Excel 2003, hiding and showing sheets

  • Thread starter Thread starter Neil Holden
  • Start date Start date
N

Neil Holden

Hi all Gurus, I have a button and when clicked I need to work out if cell c
35 is less than 10000 and if so show sheets:

- Sheets 2,4,8

and if cell C35 is greater than 10000 show:

- Sheets 3,5,6,7

Thanks
 
Dim varData as Variant
varData = ActiveSheet.Range("C35")

If varData < 10000 Then
Sheets(2).Visible = True
Sheets(4).Visible = True
Sheets(8).Visible = True
ElseIf varData > 10000 Then
Sheets(3).Visible = True
Sheets(5).Visible = True
Sheets(6).Visible = True
Sheets(7).Visible = True
End If
 
Thanks for that jacob, if the sheets are named do i just do the following:
Dim varData as Variant
varData = ActiveSheet.Range("C35")

If varData < 10000 Then
payment cert.Visible = True
Sheets(4).Visible = True
Sheets(8).Visible = True
ElseIf varData > 10000 Then
small works order.Visible = True
Sheets(5).Visible = True
Sheets(6).Visible = True
Sheets(7).Visible = True
End If
Click to expand...
 
Would it be possible to then ask the user if they would like to print the
visable sheets? If yes then print if no then exit sub?

Thanks for your help Jacob.
 
Hi Neil

Try out the below

Dim ws as Worksheet
If MsgBox("Print visible sheets", vbYesNo) = vbYes Then
For Each ws In Sheets
If ws.Visible Then ws.PrintOut Copies:=1, Collate:=True
Next
Else
Exit Sub
End If
 
I've managed it myself. May i just say Jacob you are always a great help!!!

Keep up the good work.
 
Back
Top