Constants
Used for Buttons in the Msgbox Function
| Constant | Value | Description |
| vbOKOnly |
0
|
OK button only (default) |
| vbOKCancel |
1
|
OK and Cancel buttons |
| vbAbortRetryIgnore |
2
|
Abort, Retry, and Ignore buttons |
| vbYesNoCancel |
3
|
Yes, No, and Cancel buttons |
| vbYesNo |
4
|
Yes and No buttons |
| vbRetryCancel |
5
|
Retry and Cancel buttons |
| vbCritical |
16
|
Critical message |
| vbQuestion |
32
|
Warning query |
| vbExclamation |
48
|
Warning message |
| vbInformation |
64
|
Information message |
| vbDefaultButton1 |
0
|
First button is default (default) |
| vbDefaultButton2 |
256
|
Second button is default |
| vbDefaultButton3 |
512
|
Third button is default |
| vbDefaultButton4 |
768
|
Fourth button is default |
| vbApplicationModal |
0
|
Application modal message box (default) |
| vbSystemModal |
4096
|
System modal message box |
| vbMsgBoxHelpButton |
16384
|
Adds Help button to the message box |
| VbMsgBoxSetForeground |
65536
|
Specifies the message box window as the foreground window |
| vbMsgBoxRight |
524288
|
Text is right aligned |
| vbMsgBoxRtlReading |
1048576
|
Specifies text should appear as right-to-left reading on Hebrew and Arabic systems |
Constants
Used for Msgbox Return Value
| Constant | Value | Description |
| vbOK |
1
|
OK button pressed |
| vbCancel |
2
|
Cancel button pressed |
| vbAbort |
3
|
Abort button pressed |
| vbRetry |
4
|
Retry button pressed |
| vbIgnore |
5
|
Ignore button pressed |
| vbYes |
6
|
Yes button pressed |
| vbNo |
7
|
No button pressed |
The
Excel GetOpenFilename Method
Sometime
your application needs to ask the user for a filename, you can use the InputBox
function. But this approach is tedious and error-prone (with no browsing
ability). A better approach is to use the GetOpenFilename method of the
Application object, which ensures that your application gets a valid filename
(as well as its complete path).
This method
displays the normal Open dialog box, but it does not actually open the file
specified. Rather, the method returns a string that contains the path and
filename selected by the user. Then you can write code to do whatever you want
with the filename.
The syntax
for the GetOpenFilename method is as follows (all arguments are optional):
ApplicationGetOpenFilename(FileFilter,
FilterIndex, Title, ButtonText, MultiSelect)
·
FileFilter:
Optional. A string specifying file-filtering criteria.
·
FilterIndex:
Optional. The index number of the default file-filtering criteria.
·
Title:
Optional. The title of the dialog box. If omitted, the title is Open.
·
ButtonText:
For Macintosh only.
·
MultiSelect:
Optional. If True, you can select multiple filenames. The default value is
False.
The
following example prompts the user for a filename. It defines five file
filters.
Sub
GetImportFileName()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"
' Display *.* by default
FilterIndex = 5
' Set the dialog box caption
Title = "Select a File to Import"
' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)
' Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If
' Display full path and name of the file
MsgBox "You selected " & FileName
End
Sub
The following
example is similar to the previous example. The difference is that the user can
press Ctrl or Shift and select multiple files when the dialog box is displayed.
We have check for the Cancel button click by determining whether FileName is an
array. If the user doesn’t click Cancel, the result is an array that consists
of at least one element. In this example, a list of the selected files is
displayed in a message box.
Sub
GetImportMultipleFileName()
Dim
Filt As String
Dim
FilterIndex As Integer
Dim
FileName As Variant
Dim
Title As String
Dim
i As Integer
Dim
Msg As String
'
Set up list of file filters
Filt
= "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv,"
& _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"
'
Display *.* by default
FilterIndex
= 5
'
Set the dialog box caption
Title
= "Select a File to Import"
'
Get the file name
FileName
= Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title, _
MultiSelect:=True)
'
Exit if dialog box canceled
If
Not IsArray(FileName) Then
MsgBox
"No file was selected."
Exit
Sub
End
If
'
Display full path and name of the files
For
i = LBound(FileName) To UBound(FileName)
Msg = Msg & FileName(i) & vbCrLf
Next
i
MsgBox
"You selected:" & vbCrLf & Msg
End
Sub
The
Excel GetSaveAsFilename Method
The
GetSaveAsFilename method is very similar to the GetOpenFilename method. It
displays a Save As dialog box and lets the user select (or specify) a file. It
returns a filename and path but doesn’t take any action. Like the
GetOpenFilename method, all of the
GetSaveAsFilename
method’s arguments are optional.
The syntax
for this method is
Application.GetSaveAsFilename(InitialFilename,
FileFilter, FilterIndex, Title, ButtonText)
The arguments
are
·
InitialFilename:
Optional. Specifies the suggested filename.
·
FileFilter:
Optional. A string specifying file-filtering criteria.
·
FilterIndex:
Optional. The index number of the default file-filtering criteria.
·
Title:
Optional. The title of the dialog box.
·
ButtonText:
For Macintosh only.
Prompting
for a Directory
If you need
to get a filename, the simplest solution is to use the GetOpenFileName method,
as describe earlier. But if you need to get a directory name only (no file),
you can use Excel’s FileDialog object. The following procedure displays a
dialog box that allows the user to select a directory. The selected directory
name (or Canceled) is then displayed by using the MsgBox function.
Sub
GetAFolder ()
With
Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath
& “\”
.Title = “Select a location for the backup”
.Show
If .SelectedItems.Count = 0 Then
MsgBox “Canceled”
Else
MsgBox .SelectedItems(1)
End
If
End With
End
Sub
The
FileDialog object lets you specify the starting directory by specifying a value
for the InitialFileName property. In this example, the code uses Excel’s
default file path as the starting directory.
Displaying
a Data Form
Many people
use Excel to manage lists in which the information is arranged in rows. Excel
offers a simple way to work with this type of data through the use of a
built-in data entry form that Excel can create automatically. This data form
works with either a normal range of data or a range that has been designated as
a table (by using the Insert➜Tables➜Table
command).
Making
the data form accessible
For some
reason, the command to access the data form isn’t in the Excel Ribbon. To
access the data form from Excel’s user interface, you must add it to your Quick
Access toolbar or to the Ribbon. Following are instructions to add this command
to the Quick Access toolbar:
1. Right-click
the Quick Access toolbar and select Customize Quick Access Toolbar.
The Quick Access Toolbar panel of the Excel Options dialog box appears.
2.
In the Choose Commands From drop-down list, select Commands Not in the Ribbon.
3.
In the list box on the left, select Form.
4.
Click the Add button to add the selected command to your Quick Access toolbar.
5.
Click OK to close the Excel Options dialog box.
After performing these steps, a new icon will appear on your Quick Access
toolbar.
To use a
data entry form, you must arrange your data so that Excel can recognize it as a
table. Start by entering headings for the columns in the first row of your data
entry range. Select any cell in the table and click the Form button on your
Quick Access toolbar. Excel then displays a dialog box customized to your data.
You can use the Tab key to move between the text boxes and supply information.
If a cell contains a formula, the formula result appears as text (not as an
edit box). In other words, you can’t modify formulas from the data entry form.
When you complete the data form, click the New button. Excel enters the data
into a row in the worksheet and clears the dialog box for the next row of data.
Displaying
a data form by using VBA
Use the
ShowDataForm method to display Excel’s data form. The only requirement is that
the active cell must be within a range. The following code activates cell A1
(which is in a table) and then displays the data form:
Sub
DisplayDataForm()
Range(“A1”).Select
ActiveSheet.ShowDataForm
End
Sub
