PDA

View Full Version : [SOLVED] Sub Not Defined error when using a renamed combobox



DeanP
12-02-2018, 07:24 AM
I renamed a combobox in my Userform, however when I'm running code I get a 'sub not defined' error. I have no idea what this means or how to fix it.

Here is my code:

Private Sub Userform_Initialise ()

ComboBox("Scheme").List = Worksheets("Validation").Range("A2:A4").Value

End Sub

djemy1975
12-02-2018, 07:44 AM
Could you please attach a sample of your workbook

Kenneth Hobs
12-02-2018, 09:21 AM
Private Sub UserForm_Initialize()

DeanP
12-04-2018, 05:04 AM
Private Sub UserForm_Initialize()

I changed that but still no luck.

DeanP
12-04-2018, 05:08 AM
As requested

Aflatoon
12-04-2018, 05:44 AM
There is no userform in that workbook.

Combobox is not a valid property of a form, so I suspect you need:


Private Sub Userform_Initialize()

Scheme.List = Worksheets("Validation").Range("A2:A4").Value


End Sub

Assuming the control is named Scheme.

rlv
12-04-2018, 06:20 AM
I don't see any code or userform in the workbook you posted.

djemy1975
12-04-2018, 06:44 AM
Perhaps he forgot to save as macro enabled workbook

DeanP
12-04-2018, 06:52 AM
Attached the wrong file, this one contains the form.

Aflatoon
12-04-2018, 07:39 AM
Not sure that helps either as it doesn't have the code you posted initially, or any controls called Scheme in it.

rlv
12-04-2018, 07:48 AM
Are you sure this new file pertains to the problem you are having? I don't see any combobox named "Scheme" or any construct like this

ComboBox("Scheme").List = Worksheets("Validation").Range("A2:A4").Value


or even the word "scheme". The error it produces when I run it, is Runtime error 70 - permission denied. The way to fix that error is to clear the rowsource property before attempting to assign values to list.

Private Sub UserForm_Initialize()
ComboBox1.RowSource = ""
ComboBox1.List = Worksheets("Validation").Range("B2:B88").Value
End Sub

DeanP
12-04-2018, 08:57 AM
I was trying to correct the error, so I temporarily changed the combo box name to ComboBox1A. When I inserted the box I renamed it "Scheme". The first combo box on the 2nd row of the form is comboBox1

Kenneth Hobs
12-04-2018, 09:18 AM
To repeat what rlv said in another way, I generally remove the value in the RowSource property for that control before using the List property. Do that or as rlv showed in code.


Private Sub UserForm_Initialize()
With Worksheets("Validation")
ComboBox1.List = .Range("B2", .Cells(Rows.Count, "B").End(xlUp)).Value
End With
End Sub

Aflatoon
12-04-2018, 09:40 AM
If the control was named Scheme, then as I said earlier, the code would be:


Scheme.List = Worksheets("Validation").Range("A2:A4").Value

DeanP
12-04-2018, 12:45 PM
When I use the clear row source property code as above I get an Object Required error.


Scheme.RowSource = ""

When I use


With Worksheets("Validation")
ComboBox1.List = .Range("B2", .Cells(Rows.Count, "B").End(xlUp)).Value
End With

Everything works ok!

Aflatoon
12-05-2018, 05:25 AM
Then you didn't name the control Scheme.