Unhide sheets based on a cell value

T

Tim

Hi, this problem is driving me nuts.

I have a macro warning page (shows only the warning unless you enable
macros) and can close my workbook, hiding all pages except the macro
warning page in the process.

I cannot for the life of me get any code to work when i open my
workbook.
What I have is:

1/ 21 worksheets including the macro warning sheet
2/ upon opening the workbook I want to 'very hide' 3 (or sometimes
more) sheets based upon values I have in cells.
3/ I place the values in the sheet named "Data" in a range D3:D24
4/ The cells in the range return the value TRUE or FALSE based on
the visible properties I want the sheet to have. (I set these values
by checkboxs linked to the range above).
5/ I an currently using the code (applies to two of the 21 sheetsfor
this example)

Sheets("Cover").Visible = Sheets("Data").Range("D4").Value
Sheets("KA").Visible = Sheets("Data").Range("D5").Value

and so on for the 21 sheets....
This code is used in the workbook open section.

I get the error upon opening the workbook
Error 9 script out of range

This must be either very complicated or I am very dense...could go
either way.
Any help would be appreciated.
Thanks
Tim
 
M

Mike Fogleman

I devised a simple way to let them know the macro was working. Assuming the
macro is going to change some value or values in a cell on the spreadsheet,
I would make that cell visible on the sheet (if it wasn't already), put the
word PROCESSING in the cell, let the macro do its thing and then overwrite
PROCESSING with the results of the macro.

Sub
Sheets("Analysis").Activate
Application.ScreenUpdating = True
Range("L42").Select
Range("L42").Value = "Calculating Solution Codes"
Application.ScreenUpdating = False

'Your macro runs here

Range("L42").Value = Your macro results
Application.ScreenUpdating = True
Application.ScreenUpdating = False
End Sub

Mike F
 
M

Mike Fogleman

Sorry, I sent this answer to the wrong question.
Mike Fogleman said:
I devised a simple way to let them know the macro was working. Assuming the
macro is going to change some value or values in a cell on the spreadsheet,
I would make that cell visible on the sheet (if it wasn't already), put the
word PROCESSING in the cell, let the macro do its thing and then overwrite
PROCESSING with the results of the macro.

Sub
Sheets("Analysis").Activate
Application.ScreenUpdating = True
Range("L42").Select
Range("L42").Value = "Calculating Solution Codes"
Application.ScreenUpdating = False

'Your macro runs here

Range("L42").Value = Your macro results
Application.ScreenUpdating = True
Application.ScreenUpdating = False
End Sub

Mike F
Click to expand...
 
T

Tim

Sorry Mike,
I didn't understand your answer.
Does anyone have an idea how to unhide mulitple sheets based on
multiple cell values (i.e. 1 cell value per sheet)?
Any help would be appreciated.
Regards
Tim
 
J

JE McGimpsey

One way:

Public Sub ConditionalUnhide()
Const sADDR As String = "A1"
Const sCRIT = 1
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
With wsSheet
If .Visible = False Then
If .Range(sADDR).Value = sCRIT Then _
.Visible = True
End If
End With
Next wsSheet
End Sub
 
T

Tim

Sorry but I don;t inderstand.
What do SADDR & sCRIR mean? Do they somehow relate to my range on my data page?
Regards
Tim,
 
D

Dave Peterson

J.E. was trying to make it simple for you.

sADDR is a constant that holds the address of the cell that you need to check.
(sADDR uses J.E.'s naming convention--lower case s means string and remainder of
upper mean it's a constant--and ADDR is close enough to mean Address to me).

And sCRIT is the String(?) criteria that you want to compare against.

You could have embedded the values directly into this line:
If .Range(sADDR).Value = sCRIT Then _
If .Range("a1").Value = 1 Then _

But by making them constants and putting them at the top of the procedure, it
should make tweaking the code even easier. Just change those two constants and
you're done.
 
Top