Excel 2016 VBA UserForm shows nothing - no data

[DRILL]

I would be grateful if there are any suggestions - I have been wrestling with this for several days.

I am try to use a UserForm called from VBA to offer a drop down list for user selection.

When I create this Userform with a Combobox the box appears but there is nothing in it?


So I went for the simplest code I could think of where I put some data in the Combo box from VBA but it is exactly the same. The UserForm opens with the Combobox but it is empty - no banana visible anywhere!

I am doing something wrong here! I am in need of some guidance!

Many thanks,

DRILL


Firstly I created a UserForm with a single Combo Box in it.
Then I have the code below.

Private Sub UserForm3_Activate()
  ComboBoxT.AddItem "banana"
  End Sub

 

[dmt32]

Hi,
you have changed the name of the event

Private Sub UserForm3_Activate()


End Sub

this and all other events must not be renamed

Regardless of your userforms name, it should be:

Private Sub UserForm_Activate()


End Sub

Dave

 

[DRILL]

Dave,

You are scholar and a gentleman!
Thanks for the quick response.

That was the problem which I had spent days trying to find and trawling the internet for a solution - without success.
Also, I did not realise that the text immediately before the xxxxxx.AddItem "banana" referred to a specific name for the ComboBox with this name being automatically created.

Could I ask you one more question.

In VBA code how do you call different Userforms if they are all referred to as with the same name UserForm every time?

Many thanks,

DRILL

 

[RoryA]

You refer to them by their actual name, but the events within each form always use just Userform_ as the prefix.

 

[DRILL]

[Dave,

I understand now about the naming convention.

I would be grateful if I could ask one more thing.

Now that I have corrected the use of only UserForm at the beginning of the code I have been able to get my other piece of slightly more complicated code to work.

This code reads a dynamic list below cell A1 on a Sheet called Strings



Private Sub UserForm_Activate()

string_list.RowSource = "Strings!A2:A" & Range("A" & Rows.Count).End(xlUp).Row


End Sub

This now works but when the UserForm appears the ComboBox is initially blank - but when you click on the down arrow of the ComboBox you can see and select an item.
This will work for me as I can put a comment to click on the arrow. However I was not sure whether the list should be immediately visible in the ComboBox?

Many thanks,

DRILL


QUOTE=RoryA;5320249]You refer to them by their actual name, but the events within each form always use just Userform_ as the prefix.[/QUOTE]

 

[dmt32]

[Dave,

This now works but when the UserForm appears the ComboBox is initially blank - but when you click on the down arrow of the ComboBox you can see and select an item.
This will work for me as I can put a comment to click on the arrow. However I was not sure whether the list should be immediately visible in the ComboBox?

Use the ListIndex property to select first item in your list

With string_list
    .RowSource = "Strings!A2:A" & Range("A" & Rows.Count).End(xlUp).Row
    .ListIndex = 0
 End With

Dave

 

[DRILL]

[Dave,

That works perfectly for me now!

Many thanks for you help it is much appreciated.

DRILL
QUOTE=dmt32;5320372]Use the ListIndex property to select first item in your list

With string_list
    .RowSource = "Strings!A2:A" & Range("A" & Rows.Count).End(xlUp).Row
    .ListIndex = 0
 End With

Dave[/QUOTE]

 

[DRILL]

I am afraid I am still struggling to complete a UserForm VBA.

What I am trying to do is to get a form which shows a dynamic single column list (which already exists on a sheet) and then to be able to select and item (string) on that list and then take that string back into my code to us as a variable.

With Dave's assistance I have been able to get as far a bringing up the dynamic list in a Combbox but cannot work out where to go from there.

1. Ideally I would like to be able to scroll down the list and then when I click on a string item then this is recorded as the variable and the UserForm then closes.

2. Or does it have to be that I click on the string item and then have another button to click to accept this item and then another button to close the Userform?

And then when this is done how do I take that variable back into my main code.

Any suggestions would be gratefully received.

DRILL

The code I have so far is:-

Private Sub UserForm_Activate()


With string_list
    .RowSource = "Strings!A2:A" & Range("A" & Rows.Count).End(xlUp).Row
    .ListIndex = 0
 End With
End Sub