Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
I have a checkbox named CheckBox1 in a worksheet named "DataEntry," and I
would like to be able to test the state of this checkbox in VBA. That is, I would like to test to see whether it's checked or not. Can anyone tell me how to do this? Thanks in advance. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
Right Click on the checkbox and
select format control then select properties assign a cell link, this cell link will be changed each time the checkbox is selected and de-selected. You can refer to it as need be with formulas or VB. -- Regards, Rocky McKinley "Paul James" wrote in message ... I have a checkbox named CheckBox1 in a worksheet named "DataEntry," and I would like to be able to test the state of this checkbox in VBA. That is, I would like to test to see whether it's checked or not. Can anyone tell me how to do this? Thanks in advance. Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
Paul,
if the Checkbox is a Form Control not a VB Control the following Code should work. This code will actually go for the Checkbox Text Label (as it appears to the user) not for the Name in VBA. Felix Sub Read_FormControls() Dim MyShape, CheckBox1Value For Each MyShape In ActiveSheet.Shapes If MyShape.Type = msoFormControl Then If MyShape.FormControlType = xlCheckBox Then If MyShape.AlternativeText="CheckBox1" then If MyShape.ControlFormat.Value = 1 Then CheckBox1Value=True Else CheckBox1Value=False End if End If End If End if Next For i = 1 To 8 If DlgEntries.Controls.item("chbx_FrmBox" + CStr (i)).Caption = "N/A" Then DlgEntries.Controls.item("chbx_FrmBox" + CStr (i)).Visible = False ii = ii + 1 End If Next End Sub -----Original Message----- I have a checkbox named CheckBox1 in a worksheet named "DataEntry," and I would like to be able to test the state of this checkbox in VBA. That is, I would like to test to see whether it's checked or not. Can anyone tell me how to do this? Thanks in advance. Paul . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
Thanks for your reply, Rocky.
I do know how to establish a cell link with the check box. However, the problem I'm having is that I can't get my VBA test statements, that is, the If statements, to work by referring to the values TRUE and FALSE which appear in the linked cell. For example, I've tried the following If statement: If Worksheets("dataentry").Range("G3") = True Then MsgBox "true" Else MsgBox "false" End If When the box is checked, the linked cell displays the value TRUE; when unchecked, it displays the value FALSE. But regardless of the status of the check box, (and so no matter what the value of the linked cell), the MsgBox always displays "false" when I run this sub. So my problem isn't how to link a checkbox to a linked cell, it's how to interpret those TRUE and FALSE values in the linked cell. Any idea on how to modify the If conitions in the VBA code above to get it to correctly respond to the values in the linked cell? Thanks again in advance. Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
Thanks for the reply, Felix.
I tried your code and couldn't get it to work for me. I was also puzzled by the sentence in your message that said: "This code will actually go for the Checkbox Text Label (as it appears to the user) not for the Name in VBA. " I was puzzled by this because I'm trying to test to see whether the checkbox is checked. It doesn't help my purpose to be considering the Text Label. Again, I'm looking for the VBA code I would use in a VBA "If" statement to tell me whether a check box is checked. Can anyone help me with this? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
But regardless of the status of the
check box, (and so no matter what the value of the linked cell), the MsgBox always displays "false" when I run this sub. That can't be right. Perhaps you are testing the wrong cell. -- Vasant "Paul James" wrote in message ... Thanks for your reply, Rocky. I do know how to establish a cell link with the check box. However, the problem I'm having is that I can't get my VBA test statements, that is, the If statements, to work by referring to the values TRUE and FALSE which appear in the linked cell. For example, I've tried the following If statement: If Worksheets("dataentry").Range("G3") = True Then MsgBox "true" Else MsgBox "false" End If When the box is checked, the linked cell displays the value TRUE; when unchecked, it displays the value FALSE. But regardless of the status of the check box, (and so no matter what the value of the linked cell), the MsgBox always displays "false" when I run this sub. So my problem isn't how to link a checkbox to a linked cell, it's how to interpret those TRUE and FALSE values in the linked cell. Any idea on how to modify the If conitions in the VBA code above to get it to correctly respond to the values in the linked cell? Thanks again in advance. Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
Vasant, you're right!
It was a typo, and when I checked again, I realized that, like you said, I was testing the wrong cell. Thanks to Rock, Vasant and Felix for their suggestions and replies to this message. Paul |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
Rocky McKinley wrote: *Right Click on the checkbox and select format control then select properties assign a cell link, this cell link will be changed each time th checkbox is selected and de-selected. You can refer to it as need be with formulas or VB. * Is there a way to change which cell you're linked to in VB code ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
Rocky McKinley wrote: *Right Click on the checkbox and select format control then select properties assign a cell link, this cell link will be changed each time th checkbox is selected and de-selected. You can refer to it as need be with formulas or VB. * Is there a way to change which cell you're linked to in VB code ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
Is there a way to change which cell the box is linked to within VB code ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing the state of a checkbox in vba code
Doing it the "old-fashioned" way of turning the macro recorder on and
recording the operation gave ActiveSheet.Shapes("Check Box 3").Select With Selection .LinkedCell = "$F$13" End With Good luck ! James "lcoreyl" wrote in message ... Is there a way to change which cell the box is linked to within VB code? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Web lookup query (have: city+state | want: zip code) | Excel Discussion (Misc queries) | |||
How to change address, city, state zip code into separate columns | Excel Discussion (Misc queries) | |||
How do you assess the state of a form-checkbox in a logic statemen | Excel Worksheet Functions | |||
replace state names with state code abbreviations | Excel Worksheet Functions | |||
zip code to city, state function | Excel Worksheet Functions |