Userform - ComboBox with DropDown List Not Showing when Macro is Run

[AB92]

Morning,

I'm brand new to this forum, and pretty new to Excel/VBA, so please be patient as I learn the lingo. My issue - I have created a userform that has a ComboBox with a pre-defined list, not to be changed. The macro runs without issue and will populate the specified row with the user inputted data, including the checkboxes. However, the ComboBox does not show the list that I created. The idea is that the userform will have a dropdown for the user to select from a pre-defined list and have that populate a specified cell. I even tried to create it as a ListBox but have the same issue. This is my first time creating a userform so obviously missing a key step here. The entire code is below. If the code is ok, is there something else that needs to happen to show the dropdown list?

Thanks!

I appreciate any and all advice you can give.

Private Sub CommandButton1_Click()

ConnectionTracker.Show

End Sub

Private Sub Userform_Initialize()

'Empty TargetCompany
TargetCompanyTextBox.Value = ""

'Empty Contact Name
NameTextBox.Value = ""

'Empty Contact's Company
CompanyTextBox.Value = ""

'Empty Contact's Title
TitleTextBox.Value = ""

'Empty Contact's Details
ContactDetailsTextBox.Value = ""

'Empty TouchPointComboBox
TouchPointComboBox.Clear

'Fill TouchPointComboBox
With TouchPointComboBox
    .AddItem "Email"
    .AddItem "Cold Call"
    .AddItem "Meeting"

End With

'Set Focus on TargetCompanyTextBox
TargetCompanyTextBox.SetFocus
   
End Sub

Private Sub OkButton_Click()

Dim emptyRow As Long

'Make Sheet1 Active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = TargetCompanyTextBox.Value
Cells(emptyRow, 2).Value = NameTextBox.Value
Cells(emptyRow, 3).Value = CompanyTextBox.Value
Cells(emptyRow, 4).Value = TitleTextBox.Value
Cells(emptyRow, 5).Value = ContactDetailsTextBox.Value
Cells(emptyRow, 6).Value = TouchPointComboBox.Value

If CheckBox1.Value = True Then Cells(emptyRow, 7).Value = CheckBox1.Caption

If CheckBox2.Value = True Then Cells(emptyRow, 7).Value = Cells(emptyRow, 7).Value & " " & CheckBox2.Caption

If CheckBox3.Value = True Then Cells(emptyRow, 7).Value = Cells(emptyRow, 7).Value & " " & CheckBox3.Caption

End Sub


Private Sub ClearButton_Click()

Unload Me

ConnectionTracker.Show

End Sub


Private Sub CancelButton_Click()

Unload Me

End Sub

 

[sykes]

Hi, and welcome to Mr. Excel!
I emulated your set-up, I created a userform, placed a combobox into it, renamed the CB to "TouchPointComboBox" then applied your "Fill" code to the UF's _initialize event, and it filled fine, so you're on the right lines...

Are you sure you've re-named the CB correctly? Just to be sure, if you look at the UF in its in-active state, and right-click the CB, and select "Properties", does it say "TouchPointComboBox" beside the (Name) field?

BTW, here's another way to populate the CB...

Private Sub UserForm_Initialize()
    With TouchPointComboBox
        .List = Array("Email", "Cold Call", "Meeting")
    End With
End Sub

Glad to see you eventually used the BB code tags, too! Makes it much easier for Folk trying to read your code.

 

[AB92]

Hi, and welcome to Mr. Excel!
I emulated your set-up, I created a userform, placed a combobox into it, renamed the CB to "TouchPointComboBox" then applied your "Fill" code to the UF's _initialize event, and it filled fine, so you're on the right lines...

Are you sure you've re-named the CB correctly? Just to be sure, if you look at the UF in its in-active state, and right-click the CB, and select "Properties", does it say "TouchPointComboBox" beside the (Name) field?

BTW, here's another way to populate the CB...

Private Sub UserForm_Initialize()
    With TouchPointComboBox
        .List = Array("Email", "Cold Call", "Meeting")
    End With
End Sub

Glad to see you eventually used the BB code tags, too! Makes it much easier for Folk trying to read your code.

Sykes, thank you so much for your reply.

I've checked and rechecked the CB name. I even copied and pasted it over just to be sure. Still didn't work.

I also just tried what you suggested, switching it to an array, and it still doesn't work. Is there an issue with the CB properties perhaps?

I ran the userform again and typed in the TouchPoint CB. It allowed me to type, which I do not want and then gave me a prompt that said Invalid Property Value. Are these clues to where my mistake lies? ) I'm not sure how to fix it if so.

Thanks!

 

[GWteB]

@AB92, In this case, multiple causes of the problem that occurs in your project are possible. Since we don't have access to the rest of your code the cause is hard to determine.

I'm brand new to this forum, and pretty new to Excel/VBA

In this regard I would like to point out a few things that can be very beneficial and help you avoid the most common problems with VBA. After all, if you are warned in advance that something is wrong while testing the program flow, or - better yet - when compiling the code, you can then immediately make the required corrections.

In the problem you raise in this thread, you were not warned in advance but immediately confronted with unexpected behavior or an unexpected outcome. For your imaging: I created a userform without any controls in a brand new workbook and pasted your code into the code-behind module of the userform. The compiler didn't give me any warning!!
If I had used Option Explicit at the top of the code module, on compiling (VBE > Menu > Debug > Compile VBAproject) I would have immediately received several warnings about the lack of various controls. It is therefore strongly recommended to always use Option Explicit in every module and to compile the entire VBA project before executing any code.

In addition, I would recommend to explicitly qualify the objects you use as much as possible. This means that you indicate in your code, of which object the intended object is a member (and then which object the "upper/parent" object is a member of, and so on...) so indicating what the origin of a particular object is. For instance, a cell on a worksheet is an object so the very first cell could be qualified (in an exaggerated manner) as: Excel.Application.Workbooks("SomeAlreadyOpenWorkbook").Worksheets("MyRenamedSheet").Range("A1").
If objects in the code are not qualified, VBA's built-in "automatic qualifier" will try to do so (to put it simply) but if it succeeds, its choice is not always the intended one.

A Userform is a special kind of object and not qualifying its members within its module will not directly lead to misbehaviour of your code but it can be helpful nevertheless.
Userforms support the Me qualifier. This is a nice short path compared to the above example, but apart from that there's another advantage. VBE's built-in intellisense might help you avoid typing errors. Typing Me followed by a dot character causes the member list to pop up, which should include the controls on that Userform. At times when the members list remains empty, this is often a sign that something is wrong with the qualification of the object in question. As you have already noticed, this does not necessarily lead to run-time errors, but it often leads to unexpected or unexplained effects (whether or not in conjunction with other code within the same VBA project).

Maybe this will help solve your problem.

 

[AB92]

@AB92, In this case, multiple causes of the problem that occurs in your project are possible. Since we don't have access to the rest of your code the cause is hard to determine.


In this regard I would like to point out a few things that can be very beneficial and help you avoid the most common problems with VBA. After all, if you are warned in advance that something is wrong while testing the program flow, or - better yet - when compiling the code, you can then immediately make the required corrections.

In the problem you raise in this thread, you were not warned in advance but immediately confronted with unexpected behavior or an unexpected outcome. For your imaging: I created a userform without any controls in a brand new workbook and pasted your code into the code-behind module of the userform. The compiler didn't give me any warning!!
If I had used Option Explicit at the top of the code module, on compiling (VBE > Menu > Debug > Compile VBAproject) I would have immediately received several warnings about the lack of various controls. It is therefore strongly recommended to always use Option Explicit in every module and to compile the entire VBA project before executing any code.

In addition, I would recommend to explicitly qualify the objects you use as much as possible. This means that you indicate in your code, of which object the intended object is a member (and then which object the "upper/parent" object is a member of, and so on...) so indicating what the origin of a particular object is. For instance, a cell on a worksheet is an object so the very first cell could be qualified (in an exaggerated manner) as: Excel.Application.Workbooks("SomeAlreadyOpenWorkbook").Worksheets("MyRenamedSheet").Range("A1").
If objects in the code are not qualified, VBA's built-in "automatic qualifier" will try to do so (to put it simply) but if it succeeds, its choice is not always the intended one.

A Userform is a special kind of object and not qualifying its members within its module will not directly lead to misbehaviour of your code but it can be helpful nevertheless.
Userforms support the Me qualifier. This is a nice short path compared to the above example, but apart from that there's another advantage. VBE's built-in intellisense might help you avoid typing errors. Typing Me followed by a dot character causes the member list to pop up, which should include the controls on that Userform. At times when the members list remains empty, this is often a sign that something is wrong with the qualification of the object in question. As you have already noticed, this does not necessarily lead to run-time errors, but it often leads to unexpected or unexplained effects (whether or not in conjunction with other code within the same VBA project).

Maybe this will help solve your problem.

Thank you for your reply and helpful feedback. I will try what you suggested.

 

[GWteB]

You are welcome and hopefully you're getting along at the end.

 

[sykes]

@AB92
Did you get sorted, with this?
I was messing about, and can't see any particular error in your code. Neither can I see why your (preferred) ListBox solution failed to populate, either.
it might be worth checking that you've not set the "Rowsource" property in either control; this will conflict with the attempt to populate the controls programmatically. If this were the case though, you'd be receiving an error message, methinks.

As mooted by @GWteB it might also be worth trying to qualify your objects using the ME keyword:

        'Fill TouchPointComboBox
    With Me.TouchPointComboBox
        .AddItem "Email"
        .AddItem "Cold Call"
        .AddItem "Meeting"
    End With

One thing which may be of use; did you know you can click into a piece of code, then use F8 to step through it line-by-line? this can be particularly helpful when fault-finding. Also, F5 will just run the whole procedure.

I tried a UF, with a listbox, and just ran this, which filled the LB no problem (AND the CB):

Private Sub UserForm_Initialize()
Dim lst_array As Variant

lst_array = Array("Email", "Cold Call", "Meeting")

    With Me
        .ListBox1.List = lst_array
        .TouchPointComboBox.List = lst_array
    End With
End Sub

You might notice that, following @GWteB 's comment about being more explicit when referring to objects, and use of the ME keywork, I've used ME inside the UF's _initialize event. In this case, "Me" refers to the UF itself, then typing the full stop (period) immediately afterwards, invokes Excel's "Intellisense" feature - listing all the objects which are available to that instance of ME. As already mooted, if the desired object's in the list, you know you're onto a winner; in my case, once Intellisense displayed the options, just typing the next letter (in this case "L") brought up the "ListBox1" option from the available objects. Similarly, typing "ME.T" (doesn't even need to be capitalised) took me straight to "TouchPointComboBox".

Might be worth inserting another, new, UF into your project, and just putting a listbox onto it, and trying from scratch. It'll only take a few seconds, and you can simply delete it, once you've finished with it. Just wondering whether it would give you some confidence that you're doing the right thing, and help in solving the issue...

HTH

 

[AB92]

@AB92
Did you get sorted, with this?
I was messing about, and can't see any particular error in your code. Neither can I see why your (preferred) ListBox solution failed to populate, either.
it might be worth checking that you've not set the "Rowsource" property in either control; this will conflict with the attempt to populate the controls programmatically. If this were the case though, you'd be receiving an error message, methinks.

As mooted by @GWteB it might also be worth trying to qualify your objects using the ME keyword:

        'Fill TouchPointComboBox
    With Me.TouchPointComboBox
        .AddItem "Email"
        .AddItem "Cold Call"
        .AddItem "Meeting"
    End With

One thing which may be of use; did you know you can click into a piece of code, then use F8 to step through it line-by-line? this can be particularly helpful when fault-finding. Also, F5 will just run the whole procedure.

I tried a UF, with a listbox, and just ran this, which filled the LB no problem (AND the CB):

Private Sub UserForm_Initialize()
Dim lst_array As Variant

lst_array = Array("Email", "Cold Call", "Meeting")

    With Me
        .ListBox1.List = lst_array
        .TouchPointComboBox.List = lst_array
    End With
End Sub

You might notice that, following @GWteB 's comment about being more explicit when referring to objects, and use of the ME keywork, I've used ME inside the UF's _initialize event. In this case, "Me" refers to the UF itself, then typing the full stop (period) immediately afterwards, invokes Excel's "Intellisense" feature - listing all the objects which are available to that instance of ME. As already mooted, if the desired object's in the list, you know you're onto a winner; in my case, once Intellisense displayed the options, just typing the next letter (in this case "L") brought up the "ListBox1" option from the available objects. Similarly, typing "ME.T" (doesn't even need to be capitalised) took me straight to "TouchPointComboBox".

Might be worth inserting another, new, UF into your project, and just putting a listbox onto it, and trying from scratch. It'll only take a few seconds, and you can simply delete it, once you've finished with it. Just wondering whether it would give you some confidence that you're doing the right thing, and help in solving the issue...

HTH

Thank you so much for your comments.

I figured out my issue. Instead of writing all the code in the userform, it was split between Sheet 1 and the Userform. Copying my above code into the userform solved my problem. Such a basic misstep. But alas I learned from everyone's comments and will incorporate this in my next project. Thanks so much!

 

[GWteB]

Glad it's sorted and thanks for letting us know.

 

[sykes]

Ah, good stuff - and thanks for the feedback.