Run Macro with out leaving page

Question

I have a checkbox that will run this macro when checked. The check box is in sheet1 and it will hide row 11 in sheet2. How do I write this code so I do not have to bounce back and forth between sheets?

Sub Hide_HeaterTreater_Rows()

  Sheets("NSR Report").Select
  Range("11:11").Select
If Selection.EntireRow.Hidden = False Then
  Selection.EntireRow.Hidden = True
Else
  Selection.EntireRow.Hidden = False
  End If

Sheets("NSR FORM").Select

End Sub

See this :) http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179 — Siddharth Rout, Apr 10 '13 at 18:52

Tim Williams · Accepted Answer · 2013-04-10T18:49:18.423

3

Seems kind of odd to be toggling visibility with a checkbox: I would think it would be better to use the checkbox's value to set the visibility...

Sub Hide_HeaterTreater_Rows()
  With Sheets("NSR Report").Rows(11).EntireRow  
    .Hidden = Not .Hidden
  End With
End Sub

EDIT: multiple rows

Sub Hide_HeaterTreater_Rows()
  With Sheets("NSR Report").Range("11:11,24:24").EntireRow  
    .Hidden = Not .Hidden
  End With
End Sub

edited Apr 10 '13 at 18:49

answered Apr 10 '13 at 18:27

Tim Williams

154,628
8
97
125

That worked great thank you! Could you help me understand your explination above? – David Van der Vieren Apr 10 '13 at 18:35
Also, how would you write it so that you can hid multiple rows with out getting an error code. For example rows 14 and 24? – David Van der Vieren Apr 10 '13 at 18:37
It sets the Hidden property to the opposite of its current value (`Not False` = True, `Not True` = False) – Tim Williams Apr 10 '13 at 18:50

user2140261 · Answer 2 · 2013-04-10T18:34:41.217

0

Sub Hide_HeaterTreater_Rows()
    Sheets("NSR Report").Rows(11).EntireRow.Hidden = Not Sheets("NSR Report").Rows(11).EntireRow.Hidden
End Sub

But I think you may want to use

Sub Hide_HeaterTreater_Rows()
    Sheets("NSR Report").Rows(11).EntireRow.Hidden = Sheets("NSR FORM").Shapes("CheckBox1").Value
End Sub

edited Apr 10 '13 at 18:34

answered Apr 10 '13 at 18:27

user2140261

7,855
7
32
45

Run Macro with out leaving page

2 Answers2