Object Required Error

[FotS]

Hi, I'm new here and fairly new to VBA in Excel. I'm attempting to call a Sub in a worksheet's code from a userform and I'm getting an error of Object Requied(as run from the userform):

Private Sub CommandButton1_Click()
    'Error check our entries and populate the Log with Form data
    If Not IsNumeric(TextBox2.Value) Or TextBox2.Value < 1 Or TextBox2.Value > 12 Or InStr(TextBox2.Value, ".") Then
        DateErrMsg ("Month")
    ElseIf Not IsNumeric(TextBox3.Value) Or TextBox3.Value < 1 Or TextBox3.Value > 31 Or InStr(TextBox3.Value, ".") Then
        DateErrMsg ("Day")
    ElseIf Not IsNumeric(TextBox4.Value) Or Len(TextBox4.Value) <> 4 Or InStr(TextBox4.Value, ".") Then
        DateErrMsg ("Year")
    Else
        Dim TargetRow As Integer
        If InStr(Me.Caption, "New") Then
            Sheet2.Rows("2:2").Insert Shift:=xlDown
            TargetRow = 2
        ElseIf InStr(Me.Caption, "Edit") Then
            TargetRow = Selection.Row
        End If
        With Sheet2.Range("A" & TargetRow)
            .ClearFormats
            .NumberFormat = "m/d/yyyy"
            .BorderAround
            .Interior.ColorIndex = 35
            .Value = TextBox2.Value & "/" & TextBox3.Value & "/" & TextBox4.Value
        End With
        Sheet2.EntryForm_Closeout (Sheet2.Range("A" & TargetRow & ":F" & TargetRow))
        Unload Me
    End If
End Sub

It errors out with the Sheet2.EntryForm_Closeout line, which is supposed to refer to this on Sheet2's code:

Sub EntryForm_Closeout(Target As Range)
    ChangeActive = False
    Worksheet_Change (Target)
End Sub

I've even gone so far as to remove argument requirement to the EntryForm_Closeout sub and (after reflecting this change on the form's code) attempt to rerun, but it still says object required. I've tried calling the sheet as ThisWorkbook.Sheets("Backup Log"), etc, and they all still error out the same way. I've also tried using the Call statement.

Any help would be appreciated.

 

[kpark91]

Sorry. I found the line where it errors.

 

[kpark91]

Try
changing your entryform_closeout sub as

Function EntryForm_Closeout (Target As Range)
    ChangeActive = False
    Worksheet_Change (Target) 'What does this do?
End Sub

and call the function like this:

EntryForm_Closeout (Sheet2.Range("A" & TargetRow & ":F" & TargetRow))

 

[shg]

Welcome to the board, FotS.

The parens around the argument cause VBA to evaluate it, which creates a variant array, not a range, which is what the function signature requires -- so remove the enclosing parens. Also, Sheet2 must be the CodeName of the worksheet of interest (it's Name may or may not be "Sheet2")

Sheet2.EntryForm_Closeout Sheet2.Rows(TargetRow).Range("A1:F1")

 

[FotS]

Try
changing your entryform_closeout sub as

Function EntryForm_Closeout (Target As Range)
    ChangeActive = False
    Worksheet_Change (Target) 'What does this do?
End Sub

and call the function like this:

EntryForm_Closeout (Sheet2.Range("A" & TargetRow & ":F" & TargetRow))

Thanks, but still doesn't work. Leaving the Sheet2 off gives an undefined error, while leaving the Sheet2 there causes the same object required.

I'll explain more about the code below.

Welcome to the board, FotS.

The parens around the argument cause VBA to evaluate it, which creates a variant array, not a range, which is what the function signature requires -- so remove the enclosing parens. Also, Sheet2 must be the CodeName of the worksheet of interest (it's Name may or may not be "Sheet2")

Sheet2.EntryForm_Closeout Sheet2.Rows(TargetRow).Range("A1:F1")

Thanks.
Thank you, too, for the try, but it still generates the object required error.



Ok, so here's what my code does...

I have a Log sheet (CodeName Sheet2 ) which has a couple buttons on it. Either one pressed will cause the form to load, it just differs on if it's a blank form or if it populates with whatever was selected in the log. After the user enters their data, they press the Accept button (CommandButton1) and it'll populate the cells in the Log with the data they entered (I'm still in testing phase, so the code doesn't apply all the data yet... just the date).

The problem comes about that I had the Log sheet set with a Change event that when a change occurs, it sorts the rows (this was from an old rendition of the code and can probably be changed as I'll be Protecting the sheet so all data entry MUST be done through the form). So, what I needed was a way to stop the Change event from firing while the form was working (set variable ChangeActive to True just before loading the form with an If Then statement in the Change event sub), allow the form to do its stuff, then (from the form) fire off an event in the Sheet's code that resets ChangeActive to False and manually fires off the Change event sub (hence the Worksheet_Change (Target)).

Now that I've had a night to think about it and realize I don't need the sorting in the Change event anymore, I can probably remove the attempt to fire off a sub in the sheet's code from the form and just run the sort operation from the form prior to its closing, but I'd still like to see if this can be figured out/solved for future reference.

 

[Norie]

If you want to disable events when the form opens why not try this:

Application.EnableEvents = False

and turn them back on with this.

Application.EnableEvents = True

 

[RoryA]

Shg's code should work, assuming you have a sheet with codename Sheet2 and that you change the called sub to remove the parentheses there too:

Sub EntryForm_Closeout(Target As Range)
    ChangeActive = False
    Worksheet_Change Target
End Sub

 

[FotS]

Shg's code should work, assuming you have a sheet with codename Sheet2 and that you change the called sub to remove the parentheses there too:

Sub EntryForm_Closeout(Target As Range)
    ChangeActive = False
    Worksheet_Change Target
End Sub

Huh... that did it. Weird that the debug highlighted the line in the form and even after correcting it it still highlighted the same line. I would've thought the highlighting would've moved to the line in the Sheet's code....

Anyway, thanks for the help!

If you want to disable events when the form opens why not try this:

Application.EnableEvents = False

and turn them back on with this.

Application.EnableEvents = True

Nice... I didn't know about this. However, after looking into it, I see that it disables all events and can't be selective. While for the current rendition of this project that doesn't matter, I'd rather not use it in case something develops further down the road. Thanks for the input, though!