Module object error

Hi there,

I got 6 worksheet and I a few userform.

I tried to do this code:

Sub DefineWorkSheet()

Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim WS4 As Worksheet
Dim WS5 As Worksheet
Dim WS6 As Worksheet

Set WS1 = Worksheets("MonthSummary")
Set WS2 = Worksheets("WeekSummary")
Set WS3 = Worksheets("DatabaseForm")
Set WS4 = Worksheets("DatabasePO")
Set WS5 = Worksheets("Setup")
Set WS6 = Worksheets("Products")

End Sub

And I call it in every userform header

Call DefineWorkSheet

However I got object error issue.

Is it my method and approach wrong? I need this function to be working so every userform I only need to call the function instead of repeating the same code

On the other hand if there is changes, I only change the function name instead of changing at every form.

Any idea?

Thank you.

Without you saying which error message you get exactly, or what line of the code it errors on, it's difficult to say exactly what's wrong.

Check that the sheet names in your code are exactly what they are in the workbook. A trailing space on a sheet name would not be obvious to spot but would cause an error message if the code doesn't match.

BSB

Hi BSB

The error codei is Runtime Error 424
Object Required

The error come out when I run the userform

Could you attach the workbook here so we can have a look?

BSB

New Book.xlsm

Here you go, I already commented the error code area

It's locate in module 2

The below code is causing the problem:

For Each blah In [OutletList]
    Me.cmbOutlet.AddItem blah
Next blah

Excel doesn't know what "blah" is....

even I remove the code, the error is still at there

The problem was because you're declaring variables in a sub rather than making them Public (i.e. used in code outside of that sub).

Have a look at the attached.

yeah now it's work!

thank you so much!

+rep for your help