ActiveX Button - Run-time error '424': Object required

[Novelec]

Hi experts,

I'm hoping you can share your wisdom with me. I think this a simple task, but can't seem to get it right. My aim is to make a single ActiveX button that performs a "Check All" or "Un-check All" function, from the same one button, for use with a list of marlett check boxes.

When the button is clicked, I would like the macro to populate all cells in the given named ranges with the text "a". If any of the cells already contain "a", I would like the same button to clear all cells in those named ranges.

When trying to run the macro, I get the error "Run-time error '424': Object required", with the debugger highlighting the first line of code ("If Intersect....)

I am very new, so chances are I am trying to do something Excel will not allow. I've listed code below, and have been trying different arrangements of functions for 2 hours now - With no luck...

I'm hoping one of the kind guru's on here will be able to point me in the right direction. As always, any assistance is greatly appreciated. Thanks for your time guys.

Sub Pass_All_Button()


    If Intersect(Target, Union(Range("Named_Range_1"), Range("Named_Range_2"))) <> "a" Then
        Target.Value = "a"
    Exit Sub
    End If
    
    If Intersect(Target, Union(Range("Named_Range_1"), Range("Named_Range_2"))) = "a" Then
        Target.Value = ""
    Exit Sub
    End If
    


End Sub

 

[Peter_SSs]

If you are clicking a button to instigate this code, then "Target" means nothing and that is (at least one reason) why your code is failing.

I'm not entirely clear what you want but the following code should do this:
If an "a" is found in either or both named ranges, both named ranges are cleared.
If an is not found in either named range then they are both populated with "a"s

If that is not what you want, please add more detail.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Pass_All_Button()<br>  <SPAN style="color:#00007F">Dim</SPAN> aFound <SPAN style="color:#00007F">As</SPAN> Range<br>  <br>  <SPAN style="color:#00007F">With</SPAN> Union(Range("Named_Range_1"), Range("Named_Range_2"))<br>    <SPAN style="color:#00007F">Set</SPAN> aFound = .Find(What:="a", LookIn:=xlValues, _<br>      LookAt:=xlWhole, MatchCase:=True, SearchFormat:=False)<br>    <SPAN style="color:#00007F">If</SPAN> aFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>      .Value = "a"<br>    <SPAN style="color:#00007F">Else</SPAN><br>      .ClearContents<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Novelec]

Hi Peter,

You managed to interpret my needs perfectly - Your code works a treat! Thanks so much for your help.

Is there a protocol here for "closing" a solved thread...?

 

[Peter_SSs]

Is there a protocol here for "closing" a solved thread...?

No, what you have posted is all that is required.
We leave threads open because you never know when somebody might come along with a better solution.