Error Initializing UserForm ComboBox's Data List

[bdmangum]

Hi,

I can't seem to get my userform's combobox to correctly initialize the data range I'm inputting. I've inserted my code below. When I run the program I don't get any errors. However, the combobox only contains the first value and nothing else. Somehow the other values in my array are not being put into the data list for the box. Any ideas on what I should do?




Private Sub UserForm_Initialize()

'Set Range for RVArray
Dim RVvar As Integer
Sheets(1).Range("B5").Select
Selection.End(xlDown).Select
RVvar = ActiveCell.Row


'Get info for RVArray
Dim RowCounter As Integer
Dim RVArray() As String
ReDim RVArray(1 To RVvar)

RowCounter = 4
Counter = 1

For RowCounter = 5 To RVvar
RVArray(Counter) = Worksheets(1).Cells(RowCounter, 2).Value
Counter = Counter + 1
Next

RV_Sub_System_ComboBox.Column() = RVArray


'Combobox startup properties
RV_Sub_System_ComboBox.Style = fmStyleDropDownList
RV_Sub_System_ComboBox.BoundColumn = 0
RV_Sub_System_ComboBox.ListIndex = 0

End Sub

 

[PHV]

Another way:
Private Sub UserForm_Initialize()
Dim RowCounter As Long
RowCounter = 5
With Worksheets(1)
While .Cells(RowCounter, 2).Value <> ""
RV_Sub_System_ComboBox.AddItem .Cells(RowCounter, 2).Value
RowCounter = RowCounter + 1
WEnd
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bdmangum]

Thanks! That worked great. Perhaps you could help me with my next problem with this form. The reason I needed to use a data range to populate the form is because the data needed will change over time. However this creates a problem because based upon which entry they choose another form needs to appear. I've tried a Select_Case code but that only works if the options are they same every time, it won't help when the options change. Any ideas on how to code this?

Below is what I currently have, but again this will only work initially, once the data changes this code is useless. I need to code this so it auto-adjusts. Meaning There is a another data list which contains the sub-lists of each initial choice. I probably didn't explain this very weel, so feel free to ask me for clarification.

Also note that I need to find another way to present the user with the new data selections. I can't create a form (as I did below in case 0 with RV1_Form) for each first option and then one for each sub_option. There has to be a way to use the same form and combobox to get each input, store the data, change the list options as many times as needed. I figured that much out, but I haven't figured out how to do it since the first data options will be changing over time. Ideas?



Private Sub RV_Sub_System_CommandButton_Click()

Select Case RV_Sub_System_ComboBox.Value
Case 0
Sheets(2).Range("C7").Value = "RV1"
Unload RV_Form
RV1_Form.Show
Case 1
Sheets(2).Range("C7").Value = "RV2"
Unload RV_Form
Case 2
Sheets(2).Range("C7").Value = "RV3"
Unload RV_Form

End Select