If-Then-Else Statement not running Else Statement

Question

I need a second pair of eyes, I cant get this code to run correctly. What I am trying to ge the code to do is if "checkbox 47" is pushed then to hide the different rows based on the value of cell E43, but if the checkbox is not pressed then to hide rows 45:55 no matter what the value of cell E43 is. I got the first section of the code to run perfectly but it will not hide the rows when the check box is unchecked. Here is the code

 Sub Toggle_Rows_Deisel()
 Dim Sheet As Worksheet: Set Sheet = ThisWorkbook.Worksheets("NSR FORM")

   If Sheet.Shapes("Check Box 47").OLEFormat.Object.Value = 1 Then
     Select Case CStr(Sheet.Range("E43").Value2)
     Case "1"
         Sheet.Rows("45:55").Hidden = True
         Sheet.Rows("43:44").Hidden = False
     Case "2"
         Sheet.Rows("47:55").Hidden = True
         Sheet.Rows("43:46").Hidden = False
     Case "3"
         Sheet.Rows("49:55").Hidden = True
         Sheet.Rows("43:48").Hidden = False
     Case "4"
         Sheet.Rows("51:55").Hidden = True
         Sheet.Rows("43:50").Hidden = False
     Case "5"
         Sheet.Rows("53:55").Hidden = True
         Sheet.Rows("43:52").Hidden = False
     Case "6"
         Sheet.Rows("55:55").Hidden = True
         Sheet.Rows("43:54").Hidden = False
     Case "7"
         Sheet.Rows("43:55").Hidden = False

     Case Else

     End Select
     Else
         Sheet.Rows("45:55").Hidden = True
         Sheet.Rows("43:44").Hidden = False
    End If

 End Sub

Are you calling this from a button? How are you triggering this Sub. — Menelaos, Apr 09 '13 at 22:10
Yes looks fine. I'll give it a spin within excel and see if I can figure it out. — Menelaos, Apr 09 '13 at 22:14
The code works for me. Tested on Excel 2010 with Forms Checkbox — Siddharth Rout, Apr 10 '13 at 07:05

Kazimierz Jawor · Answer 1 · 2013-04-10T05:55:21.553

Change:

If Sheet.Shapes("Check Box 47").OLEFormat.Object.Value = 1 Then

into

If Sheet.Shapes("Check Box 47").OLEFormat.Object.Value = True Then

But, please don't ask why, I just stick to that and it works.

Edit I was very, very surprised after I read some comments and after I analysed link by @Menelaos (where someone accepted answer which never meet conditions on my computer). It seems that is very confusing thing. Therefore I made some test: 1 simple OLEObject ComboBox in new sheet. I checked the values in both 2007 & 2010 (which results found to be the same). Here is code and results in comments. I hope it will be helpful.

Private Sub CheckBox1_Click()
With Sheet1.OLEObjects(1).Object

'1st type
If .Value = 1 Then
    MsgBox "1st Checked: " & .Value
Else
    '2007>Checked   >.Value =True
    '2007>UnChecked >.Value =False
    '2010>Checked   >.Value =True
    '2010>UnChecked >.Value =False
    MsgBox "1st UnChecked: " & .Value
End If

'2nd type
If .Value = True Then
    '2007>Checked   >.Value =True
    '2010>Checked   >.Value =False
    MsgBox "2nd Checked: " & .Value
Else
    '2007>UnChecked >.Value =False
    '2010>UnChecked >.Value =False
    MsgBox "2nd UnChecked: " & .Value
End If

'3rd
If .Value = -1 Then
    '2007>Checked   >.Value =True
    '2010>Checked   >.Value =True
    MsgBox "3rd -1: " & .Value
End If
If .Value = 0 Then
    '2007>UnChecked >.Value =False
    '2010>UnChecked >.Value =False
    MsgBox "3rd 0: " & .Value
End If
If .Value = -4146 Then
    MsgBox "3rd -4146: " & .Value
End If

End With
End Sub

Curious. Explaining *why* makes one of the core values of this website. — Tomalak, Apr 09 '13 at 22:30
To be honest I don't know, possibly a kind of bug which I never explored. Hoping someone could give some more details... — Kazimierz Jawor, Apr 09 '13 at 22:41
For some reason it is the reverse here: http://stackoverflow.com/questions/11741836/checking-if-a-worksheet-based-checkbox-is-checked — Menelaos, Apr 09 '13 at 22:44

If-Then-Else Statement not running Else Statement

1 Answers1