Fill ComboBox ListFillRange using Option Buttons

[caper1965]

I need help with a project,
I have a Userform that I am using as an alternative to the Sort and Autosort options on a Worksheet. The reason I am using a UserForm is because the Worksheet needs to be protected from any unintentional removal of data.
The UserForm has 8 option buttons, a Label for the Combobox and the Combobox. The Label Caption is populated by whatever Option Button is selected and from that choice is where the Combobox ListFillRange is loaded.
The code I have right now is giving me a Run-time error 424: Object required when any of the Option Button's are selected.
Here is the code I have adopted

UserForm Module - Userform name is FRM_Sort

Private Sub UserForm_Initialize()
CMBO_Criteria = ""
End Sub

Private Sub CMD_Cancel_Click()
Me.Hide
End Sub
Private Sub OB_Note_Date_Click()
LBL_Label.Caption = OB_Note_Date.Caption
CMBO_Change (1)
End Sub
Private Sub OB_Res_Click()
LBL_Label.Caption = OB_Res.Caption
CMBO_Change (2)
End Sub
Private Sub OB_FPS_Click()
LBL_Label.Caption = OB_FPS.Caption
CMBO_Change (3)
End Sub
Private Sub OB_Adate_Click()
LBL_Label.Caption = OB_Adate.Caption
CMBO_Change (4)
End Sub
Private Sub OB_Stat_Click()
LBL_Label.Caption = OB_Stat.Caption
CMBO_Change (5)
End Sub
Private Sub OB_Rdate_Click()
LBL_Label.Caption = OB_Rdate.Caption
CMBO_Change (6)
End Sub
Private Sub OB_Note_Click()
LBL_Label.Caption = OB_Note.Caption
CMBO_Change (7)
End Sub
Private Sub OB_Officer_Click()
LBL_Label.Caption = OB_Officer.Caption
CMBO_Change (8)
End Sub

Standard Module to call the CMBO_Change events --- (This is where the error messages are being generated from)

Sub CMBO_Change(myOption As Long)
Dim Limit As Long
Select Case myOption
Case 1
Limit = Sheets("NOTES").Cells(Rows.Count, 2).End(xlUp).Row
CMBO_Criteria.ListFillRange = "NOTES!B3:B" & Limit

Case 2
Limit = Sheets("NOTES").Cells(Rows.Count, 3).End(xlUp).Row
CMBO_Criteria.ListFillRange = "NOTES!C3:C" & Limit

Case 3
Limit = Sheets("NOTES").Cells(Rows.Count, 4).End(xlUp).Row
CMBO_Criteria.ListFillRange = "NOTES!D3:D" & Limit

Case 4
Limit = Sheets("NOTES").Cells(Rows.Count, 5).End(xlUp).Row
CMBO_Criteria.ListFillRange = "NOTES!E3:E" & Limit

Case 5
Limit = Sheets("NOTES").Cells(Rows.Count, 6).End(xlUp).Row
CMBO_Criteria.ListFillRange = "NOTES!F3:F" & Limit

Case 6
Limit = Sheets("NOTES").Cells(Rows.Count, 7).End(xlUp).Row
CMBO_Criteria.ListFillRange = "NOTES!G3:G" & Limit

Case 7
Limit = Sheets("NOTES").Cells(Rows.Count, 8).End(xlUp).Row
CMBO_Criteria.ListFillRange = "NOTES!H3:H" & Limit

Case 8
Limit = Sheets("NOTES").Cells(Rows.Count, 9).End(xlUp).Row
CMBO_Criteria.ListFillRange = "NOTES!I3:I" & Limit

End Select
End Sub

 

[dmt32]

Hi,
couple of points

- your CMBO_Change code is in a Standard module you will therefore, need to reference the UserForm object. You can do this by passing a copy of the form as another argument to your code using me keyword as follows:

Private Sub OB_Note_Date_Click()
  LBL_Label.Caption = OB_Note_Date.Caption
 CMBO_Change Me,1
End Sub


Sub CMBO_Change(ByVal Form As Object, ByVal myOption As Long)
    Dim Limit As Long
    
    Limit = Sheets("NOTES").Cells(Rows.Count, 2).End(xlUp).Row
    Form.CMBO_Criteria.RowSource = "NOTES!B3:B" & Limit


End Sub

- you are using the ListFillRange Property which is available to ActiveX Controls in a worksheet. In a UserForm, you use RowSource property.

Dave

 

[caper1965]

Thanks Dave. Will try it this evening and let you know.

 

[caper1965]

Dave, I applied the recommendations you suggested and it errored out saying "Compile error. Argument not optional"

 

[dmt32]

Dave, I applied the recommendations you suggested and it errored out saying "Compile error. Argument not optional"

Sounds like you have omitted an argument in calling procedure.

Your OptionButtons code should look like this:

CMBO_Change Me, 1

Where Me is your Userform & 1 your Index number

Dave

 

[caper1965]

Perfect.....Thanks Dave....Solution worked. Now how do I mark this as solved?

 

[dmt32]

Perfect.....Thanks Dave....Solution worked. Now how do I mark this as solved?

Don't think there is a way to close the thread but your response will show that you have a solution.

Thanks for feedback

Dave

 

[caper1965]

Thanks again Dave....now I am seeking help for another part of this project, should I start another thread or just continue with this? Your thoughts.......

 

[dmt32]

Thanks again Dave....now I am seeking help for another part of this project, should I start another thread or just continue with this? Your thoughts.......

if its not part of this problem then start a new thread - you will attract more responses

Dave

 

[caper1965]

Good Stuff....Thanks