How do I get the dialog box to stay on top but allow the user to select an output range on a worksheet?
	When displaying the built-in Print dialog, how do I ensure that the print selection is Entire Workbook?
	What's the difference between the InputBox Function and Method?
	Is there an easy way to have a list box display its items in sorted order?
	How do I check if an item already exists in a list box, say, to prevent duplicate values?
	How do I display the built-in dialog boxes from VBA?

Using a dialog box I want to be able to have the user select an output range where the data should be placed. The problem I am having is that the dialog box will not allow any activity other than directly keying in values. I need the dialog box to stay on top but allow the user to select an output range on a worksheet.
Keywords: Dialogs, EditBoxes
Posted November 26, 1996

Put an editbox on your dialog, then select the editbox and choose Format - Object - Control from the menu. In the Edit Validaion section click the Reference option button. Once you have done this, while the dialog is showing the user will be able to click inside this editbox and drag out a selection of cells to indicate where on the worksheet they want your output to go, the address of the range they select will appear in the editbox.

When displaying the built-in Print dialog, how do I ensure that the print selection is Entire Workbook?
Keywords: Selection Print Dialog Parameter Built-in
Posted October 14, 1996

Use  

   Application.Dialogs(xlDialogPrint).Show arg12:=3
	

You can pass parameters when using the Show method to display built-in dialogs. Where are the parameters documented? In the corresponding Excel 4 macro function! For instance, to figure out the above argument, search the PRINT?() Excel 4.0 macro function in the on-line help.

What's the difference between the InputBox Function and Method?
Keywords: Input Range Input Box User Input
Posted May 16, 1996

The InputBox function is provided by VBA for getting text from a user. It always returns text. The InputBox method is provided by Excel and can return text, numbers, arrays and/or references-in other words, the Excel-specific data types. Here's an example:

Sub TestInputBoxes()
    ''' Test InputBox Function
    MsgBox InputBox(prompt:="Type a short sentence below.", _
         Title:="What you type below will display in Message Box")
    
    ''' Test InputBox Method (restricts input to a number)
    MsgBox Application.InputBox(prompt:="This will only accept a number.", _
        Title:="Testing InputBox method", Type:=1)       
End Sub
			

The InputBox Method requires the Application object (because it comes from the application Excel rather than VBA).

The Type argument can limit the input from your user to a specific type. For example, a type 8 allows you to drag a range and get a range object for your routines. See the on-line help for additional types and other optional arguments.

When items are added to my list box control, I want them to appear in sorted order. Is there an easy way to have a list box display its items in sorted order?
Keywords: Sort ListBox
Posted April 13, 1996

The easiest way to do any sorting in Excel is to get Excel to do it. So, the list box items need to be in a worksheet range where Excel can sort it. One way to do this is to set the ListFillRange property of the list box via code. You will need to sort and dynamically change the size of this range as required.

To see this technique in action, along with other reusable list box routines, we have a free workbook called SortList.xls, which you can download.

Download Baarns SortList Example Workbook Now! (32K)
Information provided in this document is provided "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The user assumes the entire risk as to the accuracy and the use of this information.

How do I check if an item already exists in a list box, say, to prevent duplicate values?
Keywords: ListBox Duplicate
Posted April 13, 1996

You will have to write a function that will loop through the list items to check for the existence of a value before adding it. The following sample should start you off: 

''' Returns the position where found (or zero if not found)
''' Can easily be changed to return True or False (As Boolean)
Function IsInListBox(sItem As String, lbItems As ListBox) As Integer
Dim iItem As Integer
    IsInListBox = 0
    For iItem = 1 To lbItems.ListCount
            ''' Ignore case
        If UCase$(sItem) = UCase$(lbItems.List(iItem)) Then
            IsInListBox = iItem
            Exit Function
        End If
    Next iItem
End Function
			

How do I display the built-in dialog boxes from VBA?
Keywords: Excel Dialog Built-in
Posted January 16, 1996

Excel has a collection of dialog objects you may call from your code. To display a specific dialog box you use one of the predefined Excel constants. For example, Excel's File Save As dialog box is called using the following syntax: 

Application.Dialogs(xlDialogSaveAs).Show
	

To find a list of all the dialog boxes that are available from Excel, use the Object Browser (View, Object Browser command when working in a module). Select Excel as the Library and Constants in the Objects/Modules list. You'll need to scroll the Methods/Properties list down the xlDialog section. Show Show Object Browser image.

Back to Top

Back to the FAQ Table of Contents

How do you know when it's time to tune your bagpipes?