Object Variable or With Block Variable not set. Quite confused here.:(

Hi to all of you,

Could anyone please direct me in the right direction and tell me what is wrong with my code. It does not work. Feel free to call me an idiot.! LOL

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyObject As Object    ' Create object variable.
Set MyObject = UserForm2
Application.EnableEvents = False[/TR]
'On Error Resume Next
If Not Application.Intersect(Range("A2"), Range("A10")) Is Nothing Then Exit Sub
'On Error Resume Next
[COLOR=#ff0000]If Target.Application.Intersect(Range("A2"), Range("A10")).Value = "" Then  [/COLOR][COLOR=#0000cd]' the procedure stops here[/COLOR][COLOR=#ff0000][/COLOR][COLOR=#ff0000][/COLOR]
UserForm1.Hide
ElseIf Target.Application.Intersect(Range("A2"), Range("A10")).Value <> "" Then
Sheet1.Shapes("Button 1").Visible = msoFalse
Sheet3.Range("C8:C9").ClearContents
UserForm1.Show
End If


Application.EnableEvents = True


End Sub

Any help would be most welcome,

Thanks.

Re: Object Variable or With Block Variable not set. Quite confused here.:(

Thanks for the quick reply, Flash. I mean, norie.

Wow, I am an idiot! You are correct!

Quote

What are you trying to do?

In Column A2:A10 if any of those cells value changes either Hide or Show Userform1. Values in that range are already in place and depending which value was selected respond accordingly.

Re: Object Variable or With Block Variable not set. Quite confused here.:(

Thanks for the quick reply, Flash. I mean, norie.

Wow, I am an idiot! You are correct!

Quote

What are you trying to do?

In Column A2:A10 if any of those cells value changes either Hide or Show Userform1. Values in that range are already in place and depending which value was selected respond accordingly.

I have changed your code in post no. 4 to,

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyObject As Object ' Create object variable.
     
    Set MyObject = UserForm1
     
    Application.EnableEvents = False
     
    If Application.Intersect(Target, Range("A2:A10")) Is Nothing Then Exit Sub
     
    If Target.Value <> "" Then
        Sheet1.Shapes("Button 1").Visible = msoFalse
        Sheet3.Range("C8:C9").ClearContents
        MyObject.Show
    End If
     
    Application.EnableEvents = True
     
End Sub

And now it seems to work as I want.

Thank you norie!

Re: Object Variable or With Block Variable not set. Quite confused here.:(

Hi norie,

Haste makes waste.

Quote

What are you trying to do?

Quote

And now it seems to work as I want.

But I was wrong. Please could you help me with the correct syntax to Exit the Sub if the cursor is not within the Range A2:A10?

Thank you norie!