Excel 2003, hiding and showing sheets

[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

 

[Jacob Skaria]

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

 

[Neil Holden]

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

 

[Jacob Skaria]

as below

Sheets("payment cert").Visible = True

 

[Neil Holden]

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.

 

[Jacob Skaria]

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

 

[Neil Holden]

Brilliant Jacob, Can i have it so that it hides set up sheet before print?

 

[Neil Holden]

I've managed it myself. May i just say Jacob you are always a great help!!!

Keep up the good work.