How to fix run time error 424 when accessing the value of a CheckBox in Excel VBA?

Question

Okay so here is the problem: In my user form, I have 2 checkboxes. I want to be able to use the value of the checkbox to perform a certain job.

Example:

Sub main()
    UserForm1.Show
    If UserForm1.CheckBox1.Value=True Then
        MsgBox("Awesome")
    End If
End Sub

Now my problem with this is that it keeps giving me run time error 424. Can anyone help me with this? Your help is greatly appreciated. Thank you.

Update:

Sub main()
    UserForm1.Show
    If UserForm1.CheckBox1.Value=True Then
        Worksheets(1).Activate
        If UserForm1.CheckBox1.Value=True Then
            MsgBox("Awesome")
        End If
    End If
End Sub

Okay now it stops after worksheets(1).Activate. Your help is greatly appreciated. Thank you.

nicolaus-hee · Accepted Answer · 2015-06-01T07:49:16.307

0

Solution: This code works for me:

Sub main()
    If UserForm1.CheckBox1.Value = True Then
        MsgBox "Checkbox is checked"
    End If
End Sub

Explanation: The error appears because you did not specify which object (in this case: which form) CheckBox1 belongs to. Hence I added the UserForm1. in the If statement. Secondly, CheckBox1.Value is a boolean property, i.e. the value will be True when checked, not 1.

enter image description here

Additional information: Please note that running the If clause just after UserForm1.Show (like you did in your example) will never work in case you intend to select the checkboxes after the .Show command. The form will be shown and the If clause be run before you even had the time to select the checkbox. So the code in my answer should go to another Sub, e.g. the one run when you click a button in your form (do you have some sort of "OK" or "Close" button on it? If yes, double click the button in the macro editor and add the code there). Let me know if you need more context.

Update (as requested in the comments): Here's what I have:

Sub a()
    ' This launches the form
    ' I added this to a normal Module in the
    ' VBA editor
    UserForm1.Show
End Sub

Private Sub CommandButton1_Click()
    ' This is what is executed when clicking
    ' the "OK" button
    ' To add this code, add a button to your
    ' form, double click it and paste this code
    If UserForm1.CheckBox1.Value = True Then
        Worksheets(1).Activate
        MsgBox "Awesome"
    End If
    ' Update 2: Close form but keep
    ' Checkbox1.Value available
    Userform1.Hide
End Sub

Running a (from the "Macros" dialogue on the "Developer" tab) gives me:

enter image description here

Selecting the checkbox and clicking OK returns this:

enter image description here

edited Jun 01 '15 at 07:49

answered May 31 '15 at 12:28

nicolaus-hee

787
1
9
25

okay your solution works. can you help me with the updated question @nhee – Nur Atiqah Hassan May 31 '15 at 12:52
Your code (from the update in your question) works fine for me. I am shown Worksheet 1 and the message box, too. Did you see the 'additional information' in my answer? – nicolaus-hee May 31 '15 at 12:55
And why do you have two `If` clauses with the same condition in the updated code? – nicolaus-hee May 31 '15 at 13:06
What do you mean by "it stops"? Do you get an error message? Can you remove the second if clause and just specify `Worksheets(1).Activate` and `MsgBox("Awesome")` in the next line? – nicolaus-hee May 31 '15 at 13:12
i mean like it doesn't do anything after the Worksheet(1).Activate i dont get the MsgBox("Awesome") i cannot remove the second If. @nhee – Nur Atiqah Hassan May 31 '15 at 13:16
Can you try removing `Worksheet(1).Activate` for testing purposes and see if the `MsgBox` comes up? As I said before, your code works on my computer so I'm not sure what else to try. – nicolaus-hee May 31 '15 at 13:20
Have you ever successfully displayed a MsgBox before? Do you happen to have a custom sub named `MsgBox` somewhere in all currently open workbooks? Can you add `Debug.Print "test"` after the `MsgBox` line to see if the macro keeps running? ([Press Ctrl+G in VBA editor to see this output](http://stackoverflow.com/questions/2916287/where-does-vba-debug-print-log-to)) – nicolaus-hee May 31 '15 at 14:04
can you just show me your code for the userform? i just like to compare it to mine. thanks @nhee – Nur Atiqah Hassan May 31 '15 at 14:39
Here you go, I've added it at the end of my previous answer. – nicolaus-hee May 31 '15 at 14:53
@NurAtiqahHassan: any update? And since I fixed your original problem, please "accept" my answer by clicking the "check" symbol next to it. Thanks. – nicolaus-hee Jun 01 '15 at 06:21
i figured it out already thanks! it didnt work because i place Unload.Me at the command button. thanks! @nhee – Nur Atiqah Hassan Jun 01 '15 at 06:21
I see. Yes, that will uncheck the checkbox again. – nicolaus-hee Jun 01 '15 at 06:22
Then how to i keep the checkbox check? @nhee – Nur Atiqah Hassan Jun 01 '15 at 07:05
Instead of `Unload.Me` use `UserForm1.Hide`. – nicolaus-hee Jun 01 '15 at 07:47
Does it work? (Explanation: `Unload.Me` removes the form from the cache including the values / states of all elements on it. `.Hide` only hides it but keeps all data in the memory) – nicolaus-hee Jun 01 '15 at 10:39
Yes it did. you help is greatly appreciated @nhee – Nur Atiqah Hassan Jun 01 '15 at 12:14

How to fix run time error 424 when accessing the value of a CheckBox in Excel VBA?

1 Answers1

Linked