Excel VBA programming, CheckBox 
Author Message
 Excel VBA programming, CheckBox

Im writing a simple VBA script in Excel 97.  I have a button and
CheckBox from the Forms Tool Bar.   The following code compiles fine
under VB Editor, but I get an error when the If Sheet1.CheckBox1.Value
Then line is hit.  Runtime error 424, object required.

Sub btnDoIt()

  MsgBox ("Hello World!")

  If Sheet1.CheckBox1.Value Then
    MsgBox ("H4 is Checked")
  Else
    MsgBox ("H4 is not Checked")
  End If

End Sub

If any one can let me know why I cant access the value of CheckBox1,
Id appreciate it.

Thanks,

Tom Smith



Wed, 18 Jun 1902 08:00:00 GMT  
 Excel VBA programming, CheckBox
If you create a Checkbox using the Control Toolbox, then your code would
work. Checkboxes created from the Forms toolbar differ in the following
ways...

1. You reference checkboxes using either the Shape or DrawingObject method.
I prefer the 2nd...

    Sheet1.DrawingObjects("Check Box 1").Value

2. The name given these checkboxes is different. Note that my reference
above uses the Checkbox's name as it appears in the Name Box on the Formula
Bar in the Excel window. It separates the phrase CheckBox and also separates
it's index number. If you use Checkboxes from the Control Toolbox, then
"CheckBox1" would be the reference (and you could reference it directly
using "Sheet1.CheckBox1."

3. The checkbox's value cannot directly be interpreted as True/False.
Checkbox's created from the Control Toolbox can.

If the checkbox is checked, it returns a 1. Unchecked = -4146, Mixed = 2.
You need to check a value of 1 in your code. It's up to you if you want a
mixed value considered checked. Mixed values can only be set using code or
manually using the checkbox's Format-->Control command.

The following code will work for your checkbox...

Sub btnDoIt()

    If Sheet1.DrawingObjects("Check Box 5").Value = 1 Then
      MsgBox ("H4 is Checked")
    Else
      MsgBox ("H4 is not Checked")
    End If

End Sub

Use >0 if you also want a mixed value included in the "Checked" test.

_______________
Robert Rosenberg
RCOR Consulting
Microsoft MVP - Excel

http://ntware.com


Quote:
> I'm writing a simple VBA script in Excel 97.  I have a button and
> CheckBox from the Forms Tool Bar.   The following code compiles fine
> under VB Editor, but I get an error when the "If Sheet1.CheckBox1.Value
> Then " line is hit.  Runtime error 424, object required.

> Sub btnDoIt()

>   MsgBox ("Hello World!")

>   If Sheet1.CheckBox1.Value Then
>     MsgBox ("H4 is Checked")
>   Else
>     MsgBox ("H4 is not Checked")
>   End If

> End Sub

> If any one can let me know why I can't access the value of CheckBox1,
> I'd appreciate it.

> Thanks,

> Tom Smith




Wed, 18 Jun 1902 08:00:00 GMT  
 Excel VBA programming, CheckBox
There are solutions:

A)________________________________________
Sub Old_CBox_1()
   'Checkboxes created from the Forms toolbar
   'CheckBox Name = "Check Box 1"

    With Sheet1.[Check Box 1]
         If .Value = xlOn Then
             MsgBox "Checked"
         ElseIf .Value = xlMixed Then
             MsgBox "Mixed"
         Else
             MsgBox "not Checked"
         End If
    End With

End Sub

B)________________________________________
Sub Old_CBox_2()
    'Checkboxes created from the Forms toolbar
    'CheckBox Name = "Check Box 1"

    With Sheet1.CheckBoxes("Check Box 1")
         If .Value = xlOn Then
             MsgBox "Checked"
         ElseIf .Value = xlMixed Then
             MsgBox "Mixed"
         Else
             MsgBox "not Checked"
         End If
    End With

End Sub

C)________________________________________
Sub Old_CBox_3()
   'Checkboxes created from the Forms toolbar
   'CheckBox Name = "Check Box 1"

    With Sheet1.DrawingObjects("Check Box 1")
         If .Value = xlOn Then
             MsgBox "Checked"
         ElseIf .Value = xlMixed Then
             MsgBox "Mixed"
         Else
             MsgBox "not Checked"
         End If
    End With

End Sub

Regards,
Soocheon Jheong

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~

Quote:
> I'm writing a simple VBA script in Excel 97.  I have a button and
> CheckBox from the Forms Tool Bar.   The following code compiles fine
> under VB Editor, but I get an error when the "If Sheet1.CheckBox1.Value
> Then " line is hit.  Runtime error 424, object required.

> Sub btnDoIt()

>   MsgBox ("Hello World!")

>   If Sheet1.CheckBox1.Value Then
>     MsgBox ("H4 is Checked")
>   Else
>     MsgBox ("H4 is not Checked")
>   End If

> End Sub

> If any one can let me know why I can't access the value of CheckBox1,
> I'd appreciate it.

> Thanks,

> Tom Smith




Wed, 18 Jun 1902 08:00:00 GMT  
 Excel VBA programming, CheckBox
Tommy,

Just to add to Robert's comments, the other way you can process the check
box is to set the Cell Link under the Format Control / Control tab in the
pop-up menu.  The linked cell will then have the value True or False
depending upon the status of the check box.  The value in the linked cell
can be read ordinarily, and similarly setting the value of the linked cell
will change the status of the check box.

HTH

Peter Beach
Excel MVP


Quote:
> If you create a Checkbox using the Control Toolbox, then your code would
> work. Checkboxes created from the Forms toolbar differ in the following
> ways...

> 1. You reference checkboxes using either the Shape or DrawingObject
method.
> I prefer the 2nd...

>     Sheet1.DrawingObjects("Check Box 1").Value

> 2. The name given these checkboxes is different. Note that my reference
> above uses the Checkbox's name as it appears in the Name Box on the
Formula
> Bar in the Excel window. It separates the phrase CheckBox and also
separates
> it's index number. If you use Checkboxes from the Control Toolbox, then
> "CheckBox1" would be the reference (and you could reference it directly
> using "Sheet1.CheckBox1."

> 3. The checkbox's value cannot directly be interpreted as True/False.
> Checkbox's created from the Control Toolbox can.

> If the checkbox is checked, it returns a 1. Unchecked = -4146, Mixed = 2.
> You need to check a value of 1 in your code. It's up to you if you want a
> mixed value considered checked. Mixed values can only be set using code or
> manually using the checkbox's Format-->Control command.

> The following code will work for your checkbox...

> Sub btnDoIt()

>     If Sheet1.DrawingObjects("Check Box 5").Value = 1 Then
>       MsgBox ("H4 is Checked")
>     Else
>       MsgBox ("H4 is not Checked")
>     End If

> End Sub

> Use >0 if you also want a mixed value included in the "Checked" test.

> _______________
> Robert Rosenberg
> RCOR Consulting
> Microsoft MVP - Excel

> http://ntware.com



> > I'm writing a simple VBA script in Excel 97.  I have a button and
> > CheckBox from the Forms Tool Bar.   The following code compiles fine
> > under VB Editor, but I get an error when the "If Sheet1.CheckBox1.Value
> > Then " line is hit.  Runtime error 424, object required.

> > Sub btnDoIt()

> >   MsgBox ("Hello World!")

> >   If Sheet1.CheckBox1.Value Then
> >     MsgBox ("H4 is Checked")
> >   Else
> >     MsgBox ("H4 is not Checked")
> >   End If

> > End Sub

> > If any one can let me know why I can't access the value of CheckBox1,
> > I'd appreciate it.

> > Thanks,

> > Tom Smith




Wed, 18 Jun 1902 08:00:00 GMT  
 Excel VBA programming, CheckBox
Thanks for your help.  This helps immensely with figuring out VBA.

Tommy Smith



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Help with VBA and Excel please - Plus Comments About Excel VB Programming Books

2. Exporting from Access to Excel and automaticly executing VBA program in Excel

3. Form CheckBox Populated via if statement in VBA Excel 2000

4. Link checkbox whitin Excel to a checkbox in Word,

5. Link checkbox whitin Excel to a checkbox in Word,

6. Checkbox in Excel to control checkbox in Word ???

7. Link checkbox whitin Excel to a checkbox in Word,

8. Linear Programming Excel-VBA example (including codes)

9. Running a VBA program in Excel

10. VBA programming export data from Excel to Word and visa versa


 
Powered by phpBB® Forum Software © phpBB Group