Runtime error 424: object required while clearing combo boxes

[Samadhan Gaikwad]

Hi,

I have posted below code under Thisworkbook, but getting run time error 424: object required. I have added combo boxes in sheet.

Private Sub Workbook_Open()
   
    Application.EnableEvents = False
    'Clear ComboBox1 Items
    Call Clear_ComboBox
   
    'Add Items to ComboBox1 in Sheet1 while opening workbook
    With PR_data_with_search.ComboBox1
        .AddItem "Fruits"
        .AddItem "Vegetables"
        .AddItem "Soaps"
        .AddItem "Beverages"
    End With
    Application.EnableEvents = True
End Sub

Sub Clear_ComboBox()
   
    'Clear ComboBox & TextBox data
    With PR_data_with_search
        .ComboBox1.Clear
        .ComboBox2.Clear
'        .TextBox1.Text = ""
'        .TextBox2.Text = ""
    End With

End Sub

 

[Chihiro]

What does "PR_data_with_search" signify?

If it's worksheet you need to define it as such.

Ex:

With ThisWorkbook.Worksheets("PR_data_with_search")
'....
End With

 

[shrivallabha]

You can either use Chihiro's method or set the reference to the object before executing further e.g.

Call Clear_ComboBox
Dim PR_data_with_search as Worksheet
Set  PR_data_with_search = Activesheet 'Set reference here to whatever worksheet you need to.
'Add Items to ComboBox1 in Sheet1 while opening workbook  
With PR_data_with_search.ComboBox1

 

[Marc L]

Hi,

or better use directly worksheet CodeName …

Here Sheet1 is the CodeName of worksheet named abc,
so no need to create any object variable …
So Sheet1 can be directly used instead of ThisWorkbook.Worksheets("abc") !

Use only CodeName for worksheets within code workbook …
​

 

[Samadhan Gaikwad]

Tried with above suggestions but issue not resolved, some errors occur.
I am attaching file. What I want is:
1. When workbook is opened, just want 1st comboBox to display with "List1" & CBox2 and Cbox3 will be cleared.
2. 3 options will be available in CBox2.
3. According to change/selection of option in CBox2, CBox3 will be updated.

Issue now is Cbox2 and Cbox3 is not cleared, and 2nd and 3rd CBox is not updated unless I run manually vb code from Sheet1.

 

[Chihiro]

The issue here is that you can't call on Private sub from outside Worksheet module.

You can do it like below.

Private Sub Workbook_Open()
With Sheet1
    .ComboBox1.Value = ""
    .ComboBox1.AddItem "List1"
    .ComboBox2.Clear
    .ComboBox2.Value = ""
    .ComboBox3.Clear
    .ComboBox3.Value = ""
End With
End Sub

.Clear will remove items from list.
.Value = "" will reset value already assigned.

You need both to reset selection and list.

 

[Samadhan Gaikwad]

Working nice and as expected in above workbook.
But in my workbook where many macros are present, same code gives runtime error 424. Will try to investigate...

 

[Samadhan Gaikwad]

Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("PR_data_with_search")
    'On Error Resume Next
    .ComboBox1.Value = ""
    .ComboBox1.AddItem "NX"
    .ComboBox2.Clear
    .ComboBox2.Value = ""
    .ComboBox3.Clear
    .ComboBox3.Value = ""
    .ComboBox4.Clear
    .ComboBox4.Value = ""
    .ComboBox5.Clear
    .ComboBox5.Value = ""
End With
End Sub

With above code all working fine except unable to AddItem to box. It is throwing runtime error 70, permission denied.