Results 1 to 4 of 4

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

This is a discussion on ActiveX Button - Run-time error '424': Object required within the Excel Questions forums, part of the Question Forums category; Hi experts, I'm hoping you can share your wisdom with me. I think this a simple task, but can't seem ...

  1. #1
    Board Regular
    Join Date
    Nov 2012
    Location
    Australia
    Posts
    83

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

    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.


    Code:
    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

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,319

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

    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.


    Sub Pass_All_Button()
      Dim aFound As Range
      
      With Union(Range("Named_Range_1"), Range("Named_Range_2"))
        Set aFound = .Find(What:="a", LookIn:=xlValues, _
          LookAt:=xlWhole, MatchCase:=True, SearchFormat:=False)
        If aFound Is Nothing Then
          .Value = "a"
        Else
          .ClearContents
        End If
      End With
    End Sub
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  3. #3
    Board Regular
    Join Date
    Nov 2012
    Location
    Australia
    Posts
    83

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

    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...?

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,319

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

    Quote Originally Posted by Novelec View Post
    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.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com