Combobox troubles

[g-man97]

I am trying to populate a combobox on a userform. It's my 1st atempt and needless to say it's not going so well.

I've read several of the posts and thought I could handle it.

I put the userform_initalize sub into the userform and then did
load SomeName (the userform name) followed by SomeName.Show in the worksheet module attached to a command button.

When i step through the code i can see it going through my list but nothing shows up in the combobox. Grrrrr! BTW, the list i'm using is in column F on Sheet2, starting in row 5 and then in every other row, (still in same column), e.g. f5, f7, f9, f1...and so on.


Any help or suggestions would be appreciated.

 

[Tazguy37]

The .AddItem Method will add an item to a combobox list. Load will load the userform into memory without showing it.

 

[g-man97]

TazGuy, here's my code. It is in a userform module.

Private Sub UserForm_Initialize()
Dim rng As Range
Dim LastRow As Long
Dim I As Long
Sheets("ScenariosTbl").Activate
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
For I = 5 To LastRow - 4
Set rng = ActiveSheet.Range("F" & I)
MsgBox "usrfrm initialize, listvalue is " & rng
If rng.Offset(0, 1) = "Active" Then
ScenariosMenu.ComboBox1.AddItem rng.Value
End If
Next I
End Sub

The msgbox (just for testing) shows the values but none of them show up in the combobox on the userform. Also, I need to use every other row, i.e. f5, f7, f9, etc. As it is now, this goes through every row and there is only data in every other, so I'd prefer not to have a bunch of blanks in my list.

Is this code in the right module? I am launching the userform from a worksheet module with load FormName followed by FormName.Show. Are these in the right module?

Finally, how do I capture the value of the comobox? Would OffName = Me.ComboBox1.Caption do the trick?

While I'm at it, what goes in the ComboBox1_Change sub?

Sorry to be so dense. Thanks again everyone!! You're lifesavers.

G-Man

 

[Tazguy37]

TazGuy, here's my code. It is in a userform module.

Private Sub UserForm_Initialize()
Dim rng As Range
Dim LastRow As Long
Dim I As Long
Sheets("ScenariosTbl").Activate
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
For I = 5 To LastRow - 4
Set rng = ActiveSheet.Range("F" & I)
MsgBox "usrfrm initialize, listvalue is " & rng
If rng.Offset(0, 1) = "Active" Then
ScenariosMenu.ComboBox1.AddItem rng.Value
End If
Next I
End Sub

The msgbox (just for testing) shows the values but none of them show up in the combobox on the userform. Also, I need to use every other row, i.e. f5, f7, f9, etc. As it is now, this goes through every row and there is only data in every other, so I'd prefer not to have a bunch of blanks in my list.

You could change this line:

LastRow = Cells(Rows.Count, 2).End(xlUp).Row

to this:

LastRow = Range("F65536").End(xlUp).Row

Is this code in the right module? I am launching the userform from a worksheet module with load FormName followed by FormName.Show. Are these in the right module?

Looks like everything's in the right place. You might want to just do a ScenariosMenu.Show in the Workbook_Open event, so that the form will pop up when the workbook opens. There's no need to load the form in that case, when the code gets to the .Show method, it will load it by itself.

Finally, how do I capture the value of the comobox? Would OffName = Me.ComboBox1.Caption do the trick?

While I'm at it, what goes in the ComboBox1_Change sub?

If you're in the ComboBox1_Change event, then ComboBox1.Value will reflect the currently selected value. If you're not in the userform's code, you'd have to specify that it's on a userform with ScenariosMenu.ComboBox1.Value

Sorry to be so dense. Thanks again everyone!! You're lifesavers.
G-Man

You're not dense. You have questions, you ask, someone answers, you learn, and then answer other people's questions. That's how it works.

 

[g-man97]

TazGuy37,

Thank you for your quick reply and kind words. I eventually figured out, (through NUMEROUS trials and errors) that my problem was in this code:

If rng.Offset(0, 1) = "Active" Then
ScenariosMenu.ComboBox1.AddItem rng.Value
End If

I took out the if statement and the code works beautifully!



G-Man