If statement is not working in Excel VBA

Question

Iam trying to create a simple if statement in Excel with VBA.

I'm creating a new checkbox

Adds the following code to the box.

Sub CheckBox1_Click()
   HideRows "2:5"
End Sub

Sub HideRows(rowRange)
If CheckBox1 = False Then
   Rows(rowRange).EntireRow.Hidden = True
   Else: Rows(rowRange).EntireRow.Hidden = False
   End If
End Sub

Result: The rows are hidden both if the checkbox is checked or unchecked.

(checkbox is checked)

All rows are visible

Uncheck the checkbox

Result: All rows are hidden

(checkbox is unchecked)

All rows are visible

Uncheck the checkbox

Result: All rows are hidden

Scott Craner · Accepted Answer · 2017-10-13 14:44:35Z

4

You want it in a Change Event.

You do not need the If Then. CheckBox1 rturns a TRUE/FALSE, just use that.

And the EntireRow is also not needed when refering to Rows(). You are already refering to the whole row.

Also, it is good practice to always declare the parent to any Range Object, which Rows() is. If the the code is in the Worksheet code then use Me as it will refer to itself. If the code is in a module then use ActiveSheet or more preferably the specific sheet, Worksheets("Sheet1") :

Private Sub CheckBox1_Change()
    HideRows "2:5"
End Sub



Sub HideRows(rowRange)
   'if this code is not in the worksheet code then change `Me` to `ActiveSheet`
   Me.Rows(rowRange).Hidden = Not CheckBox1

End Sub

edited Oct 13, 2017 at 14:44

answered Oct 13, 2017 at 14:27

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mathieu Guindon Over a year ago

Upvoted for pointing out the redundancy of the If...Then block and Boolean assignments... I'd qualify Rows with ActiveSheet and explicitly refer to CheckBox1.Value though ;-)

Tom Over a year ago

What he said ^^^^

Scott Craner Over a year ago

@Mat'sMug though, if this is on the sheet code is it really needed or would Me be better?

Mathieu Guindon Over a year ago

Ah, yes, absolutely - I keep assuming comboboxes are on userforms :)

Scott Craner Over a year ago

@Mat'sMug I'm just glad I was able to get the +1 from you. I've been trying to step up my vba answers.

Subodh Tiwari sktneer · Accepted Answer · 2017-10-14 01:57:53Z

2

Assuming its an ActiveX CheckBox, place this code in Sheet Module...

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
    Rows("2:5").Hidden = True
Else
    Rows("2:5").Hidden = False
End If
End Sub

Edit:

Or just use this...

Private Sub CheckBox1_Click()
    Rows("2:5").Hidden = CheckBox1
End Sub

edited Oct 14, 2017 at 1:57

answered Oct 13, 2017 at 14:26

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

3 Comments

Mathieu Guindon Over a year ago

Why not just ActiveSheet.Rows("2:5").Hidden = CheckBox1.Value?

Subodh Tiwari sktneer Over a year ago

@Mat'sMug Yes, that's true. But it was just to demonstrate how it would work rather than finding the shortest method to achieve the end result. What you suggested actually doesn't even require to qualify it with sheet reference as the code will be sitting in the sheet module itself. :)

Mathieu Guindon Over a year ago

That's correct (+1) - the applicable explicit qualifier would be Me if that code is in a worksheet's code-behind; arguably the active sheet would/should be that sheet though, but yeah Me is semantically more appropriate than ActiveSheet... and Me is always a redundant qualifier.

krib · Accepted Answer · 2017-10-13 14:24:30Z

1

You can put this in one sub, assuming its an activeX checkbox

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
    [2:5].EntireRow.Hidden = False
    Else: [2:5].EntireRow.Hidden = True
End If
End Sub

answered Oct 13, 2017 at 14:24

krib

5694 silver badges14 bronze badges

Collectives™ on Stack Overflow

If statement is not working in Excel VBA

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related