0

this super simple thing is currently driving me crazy. I have an If statement to handle the CheckBox_Click() event which is supposed to do one of two things depending on the status of the CheckBox. While it works just fine when the box becomes checked, it just won't do anything when the box is unchecked. I know the code is firing simply by putting a MsgBox to tell me, but the If statement won't initiate. See code below:

Sub Chase_Visa_Click()
    'Check new button status for action.
    If Sheets("<Sheet Name>").Shapes("<CheckBox Name>").OLEFormat.Object.Value = 1 Then
        MsgBox ("Box checked.")
    ElseIf Sheets("<Sheet Name>").Shapes("CheckBox Name>").OLEFormat.Object.Value = 0 Then
        MsgBox ("Box unchecked.")
    End If
End Sub

From what I've read, the value for unchecked is 0. I've also tried things like ".Checked = False". Thank you very much for your help.

Community
  • 1
  • 1
SunnyNonsense
  • 410
  • 3
  • 22
  • Why use `ElseIf` rather than simply `Else`? – John Coleman Jul 30 '16 at 20:36
  • I had considered that but wanted to be very specific. I guess I'm just scared because of how open ended that is... I will certainly do that if I can't get this figured out soon, but I am still very curious why "0" isn't working. Thanks for your suggestion. – SunnyNonsense Jul 30 '16 at 20:38
  • 1
    It does seem somewhat mysterious. Perhaps have the click code simple `MsgBox Sheets("").Shapes("CheckBox Name>").OLEFormat.Object.Value ` to see what this value is. – John Coleman Jul 30 '16 at 20:55
  • 1
    The [MSDN help](https://msdn.microsoft.com/en-us/library/office/gg264606(v=office.15).aspx) for MSForms suggests that the Value property will be -1 or True for checked, 0 or False for unchecked and Null for neither checked nor unchecked – barrowc Jul 30 '16 at 21:02
  • Phenomenal thinking @JohnColeman! So it turns out the unchecked value was "-4146" which is vaguely ringing a bell. And now that I know the answer, it turns out I had seen it in this question but I just couldn't google the right thing to get back to it: http://stackoverflow.com/questions/11741836/checking-if-a-worksheet-based-checkbox-is-checked. I hate that I created a duplicate question... But it's here and it would benefit from an answer so would you like to post it since it was your great thinking that got us there? – SunnyNonsense Jul 30 '16 at 21:53
  • If it is a duplicate question -- feel free to delete it. I'm glad that I was able to help. I don't think that the comment is worth posting as an answer. – John Coleman Jul 30 '16 at 21:56
  • It's true that it was simple, but the technique and the actual answer might benefit someone in the future so I hope it's alright with everyone if I post the answer (so that it may be marked as answered). I just know that sometimes a person might not be googling the right thing and this will be another chance for them to find their answer. – SunnyNonsense Jul 30 '16 at 22:12

2 Answers2

0

As John Coleman suggested above, simply displaying the value of

Sheets("<Sheet Name>").Shapes("<CheckBox Name>").OLEFormat.Object.Value

pointed out that "-4146" is the value of an unchecked CheckBox.

For any super beginners, my chosen method of getting the value was

MsgBox(Sheets("<Sheet Name>").Shapes("<CheckBox Name>").OLEFormat.Object.Value)
SunnyNonsense
  • 410
  • 3
  • 22
0

in vba access, I make a textbox named Name0 and a check box named Checkbox. now I want to write the name in the text field "Mark" and check the checkbox. I save it into the table and the form will be ready for a new entry. the text field should be filled as I filled for the previous entry "Mark". and if I uncheck the check box the text field should be empty for a new entry.

Yawar Abbas
  • 1