If dropdown only contains one option, automatically show that option - How????

[btatc620]

I cannot figure out how to do this. Does anyone know how?

[JKwan]

something like this


If Me.ComboBox2.ListCount = 1 Then
Me.ComboBox2.ListIndex = 0
End If

[btatc620]

Thanks, but I cannot figure out how to apply this. Can you perhaps send me a small example.

[SamT]

Sub UserForm_Initialize()

If Me.ComboBox2.ListCount = 1 Then
Me.ComboBox2.ListIndex = 0
End If
End Sub

[btatc620]

If I had a COMPLETELY FRESH AND BLANK workbook and then clicked on sheet 1 view code and copied and pasted your code in, what would I then need to input into excel for this to work. I know I would need to insert a combobox, but I no experience with them and don't know how the naming works to get it to match with "me.combobox2". I'm sorry, I am idiot with this stuff and need step-by-step instructions if you do not mind. I GREATLY appreciate it your help. Once I get the gist of it, I will be able to manipulate it how I needed it.

[JKwan]

Here is a demo file

[SamT]

If I had a COMPLETELY FRESH AND BLANK workbook and then clicked on sheet 1 view code and copied and pasted your code in, what would I then need to input into excel for this to work.

There are Worksheet Forms with Controls and VBA UserForms with Controls.

A Worksheet can have two Types of Controls; Those from the Excel Menu: View >> ToolBars >> Forms Menu, and those from the View >> ToolBars >> Control ToolBox Menu. I personally don't use the outdated Forms Menu Controls, so all the following is for the Control ToolBox Menu.

15381
Image 1

The ComboBox Icon is 3 to the right of the Button Icon. Click the ComboBox Icon, then click the location on the Sheet where you want the ComboBox. You can move it and resize it. Right Click the ComboBox on the sheet to see the Context Menu. All code for the Control ToolBox Controls goes in the Sheet code that the Control is on. Just click View Code to see it.

General Tips:
In the VBA Editor, first, open the Tools >> Options Menu and put a checkmark in all the CheckBoxes in the Code Settings Frame on the Editor Tab. This will force you to Explicitly Declare all Variables and help prevent and or find errors. (Note that pre-existing Code Pages will not have "Option Explicit" at the top, you can add that yourself,)

Press Ctrl+R and F4 to insure that the VBA Project Explorer and the VBA Properties Windows are open.


Using the VBA Editor:
15382
Image 2

The Project Explorer Window is in the Upper Left of this image and the Properties Window in the Lower Left. Sheet1 has been Selected in the Project Explorer. To show the Code Page for Sheet1, Double Click Sheet1 in the Project Explorer. Sheet1 is an Object, "Current Summary" is the Object.Name. "Accts And Notes" is the Name of the Workbook.

To add a VBA UserForms From to your Workbook, in the Project Explorer, Right Click on the ThisWorkbook Item for that particular workbook and select Insert >> UserForm.

The rest of this applies to all controls, either on a Sheet or in a UserForm. Note the A UserForm is itself a Control, so this applies to it as well.

Click any Control and the Properties Window will display the Properties of that Control. You can set those Properties in the Right Column of the Properties Window; This is during Design Time. You can set Properties in Code, this is Run Time. Select any Property in the Properties Window and press F1 to see the help for that Property.

Controls are Objects. All Objects, (Controls, WorkSheets, Workbooks, etc,) have Properties and Events. Some Objects have Methods which you can use in Code, such as Add Items to a ListBox or ComboBox, Pre-select a particular Item, or place a new Control on the UserForm.

About Events:
15383
Image 3: Inserting an Exit Sub for TextBox1

Events are things that the User can do to an Object during Run Time, such as Click a Control, Activate a WorkSheet, Open a Workbook,etc.

You have to write code for each Event that you want to Trap. Above the Code Page are two Dropdowns. In the Left DropDown, select the Object, you want and in the right Dropdown, select the Event you wish to Trap. This will insert an Event Code Stub into the Code Page so you can start writing the code to handle the Event.

For help on an Event, Type the Event Keyword, for example: "Exit," place the Cursor in or next to the Keyword and press F1.