Results 1 to 13 of 13

Thread: *Resolved* Checkbox VBA Syntax?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    May 2004
    Location
    Right here
    Posts
    275

    Resolved *Resolved* Checkbox VBA Syntax?

    I've got a simple piece of code in Excel VBA which is:

    Code:
    Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
        Range("A1").Value = 1
    Else
        Range("A1").Value = 0
    End If
    
    End Sub
    which is supposed to make 1 or 0 appear in cell A1 depending on whether or not a checkbox is ticked. However all I get is the error message 'Runtime error 424. Object required'.

    I don't know what this means or why the code doesn't work and I can't find anything in Excel Help which explains what's wrong!

    Anyone help?

    Cheers
    -Rob
    Last edited by TheRobster; Nov 12th, 2004 at 08:56 AM.

  2. #2
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709
    The checkbox doesn't actually reside in the cell. it is contained in a
    overlay on top of the cells. Refer to the object instead of the cell.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    May 2004
    Location
    Right here
    Posts
    275
    I think you misunderstood what it was that I'm trying to do. I know the checkbox doesn't actually reside in a cell, but what I want it to do is to be linked to cell so that when it is ticked/unticked (true/false) then this places the number 1 or 2 in cell A1.

    This isn't what I want it to do in my spreadsheet exactly. What I want is for a range of cells to be coloured green or white, depending on the checkbox condition. So in English the code would read:

    If CheckBox1 is ticked then
    Cell A1 colour = green
    If CheckBox1 is not ticked then
    Cell A1 colour = white

  4. #4
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709
    VB Code:
    1. Public Sub CheckBox1_Click()
    2.     If CheckBox1.Value = True Then
    3.         With Sheet1.Range("A1:A1").Cells(1, 1).Interior
    4.             .ColorIndex = 10
    5.             .Pattern = xlSolid
    6.             .PatternColorIndex = xlAutomatic
    7.         End With
    8.     Else
    9.         With Sheet1.Range("A1:A1").Cells(1, 1).Interior
    10.             .ColorIndex = xlNone
    11.         End With
    12.     End If
    13. End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    May 2004
    Location
    Right here
    Posts
    275
    Still get the same error, even with your code.



    It seems to be the second line it's having problems with.



    Did it work on your PC? I don't understand why it doesn't do what I want it to do.
    Last edited by TheRobster; Nov 10th, 2004 at 08:38 AM.

  6. #6
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709
    I have the code behind Sheet1. That may be the issue if you have
    the code in the ThisWorkBook or a module. Reference the
    workbook/worksheet before the checkbox.

    HTH
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    May 2004
    Location
    Right here
    Posts
    275
    I still can't get it to work.

    Any chance you can provide a download link to your version of it? I must be doing something fundamentally wrong but I can't figure out what.

    Cheers
    -Rob

  8. #8
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709
    Here you go.
    Attached Files Attached Files
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    May 2004
    Location
    Right here
    Posts
    275
    Cheers, that works how I imagined it.

    Did you use an ActiveX control? I was using the Forms checkbox - this is the only difference as far as I can see between what I did and what you did, only yours works and mine doesn't.

  10. #10
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709
    No prob.

    Yes, I added the checkbox from the toolbox toolbar and not
    the checkbox from the forms collection.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  11. #11
    New Member
    Join Date
    Oct 2021
    Posts
    2

    Re: *Resolved* Checkbox VBA Syntax?

    Hello,

    how can I do it Office 2019 ?
    I cant copy ant paste Your CheckBox
    Where is toolbox toolbar in Office 2019 ?



    Quote Originally Posted by RobDog888 View Post
    No prob.

    Yes, I added the checkbox from the toolbox toolbar and not
    the checkbox from the forms collection.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,449

    Re: *Resolved* Checkbox VBA Syntax?

    this is an old thread from 2004 and the people who were posting here may well no longer visit this forum, you should have started a new thread

    i do not have office 2019, but i guess you should look in the developer tab >controls
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13
    New Member
    Join Date
    Oct 2021
    Posts
    2

    Re: *Resolved* Checkbox VBA Syntax?

    I found, all is OK.
    Thanks for the time

    Name:  vba.jpg
Views: 13
Size:  24.4 KB


    Quote Originally Posted by westconn1 View Post
    this is an old thread from 2004 and the people who were posting here may well no longer visit this forum, you should have started a new thread

    i do not have office 2019, but i guess you should look in the developer tab >controls

Posting Permissions

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



Click Here to Expand Forum to Full Width