#Run time error 424, object required

[Vivek Roshan]

Hi,

I have created a user form in sheet1(Data_Entry_Form) and my data in sheet2(ICC_Data). When I run the user form through command button in sheet1. I am getting a message "run time error #424 , object required" and when I debug, it highlighted at userform1.show. When I pressed f8, it is showing the problem in userform1 initializer. The code is given below.


Private Sub UserForm_Initialize()
Dim lrow As Long
lrow = Application.WorksheetFunction.CountA(ICC_Data.Range("A:A")) + 1
Me.ComboBox1.RowSource = "ICC_Data:A2:A" & lrow
End Sub


Any help on the above shall be highly appreciated.
Thanks,
Vivek Roshan

 

[Fluff]

What is ICC_Data? You haven't declared, or set it.

 

[Vivek Roshan]

It's a name given to sheet2. ICC_Data (Incoming Correspondence) where data will be stored.

 

[Fluff]

Try

   Dim rng As Range
   Set rng = ICC_Data.Range("A2", ICC_Data.Range("A" & Rows.Count).End(xlUp))
   Me.ComboBox1.RowSource = rng.AddressLocal

 

[Vivek Roshan]

Hi Fluff, Thanks for your reply. I have tried your suggested code. I got an error mesg as given below:

# Compile error, sub or function not defined. The underlined area is highlighted.

Can you please look into this?


Set rng = ICC_Data.Range("A2", ICC_Data.Range("A" & Rows.Count).End(xlUp))

 

[Fluff]

In your op you said

sheet2(ICC_Data)

which suggests that the sheet name is ICC_Data & the sheet codename is Sheet2.

Then in post#3 you said

ICC_Data (Incoming Correspondence)

which suggests that ICC_Data is the code name & the actual sheet name is Incoming Correspondence.

When you look at the project window in the VBE what does it say for that sheet?

 

[Vivek Roshan]

Sheet2(ICC_Data)

 

[Fluff]

In that case try

Dim rng As Range
   Set rng = Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp))
   Me.ComboBox1.RowSource = rng.AddressLocal

 

[Vivek Roshan]

Thanks a lot Mr.Fluff. Now form opens without any error. May God bless you. I will seek your help once again in case of any problem.

 

[Fluff]

Glad to help & thanks for the feedback