If/Then Macro For Each Visible Worksheet

[Mike G - D.C.]

I have a workbook that will always contain at least one visible worksheet.
The number of visible worksheets may vary, but the workbook will always
contain at least one visible and one hidden worksheet.

I would like to add the following logic to run with an on open workbook
event but am having a hard time piecing together the proper VB code.

On Workbook Open,
For each visible worksheet within the workbook,
IF cell A1="Input",
THEN run formatting macro,
ELSE do nothing.

Any help is much appreciated.

 

[Charles Chickering]

Mike, test each worksheet to see if it is visible:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
If ws.Range("A1") = "Input" Then
formatting_macro
End If
End If
Next
End Sub

replace "formatting_macro" with the name of your macro

 

[Jim Thomlinson]

give this a try... It must go into the thisworkbook module. Right click the
XL icon next to the word file in the XL menu and select View Code. Paste the
following

Private Sub Workbook_Open()
Dim wks As Worksheet

For Each wks In Worksheets
If wks.Visible = xlSheetVisible Then
MsgBox "Format sheet " & wks.Name
End If
Next wks
End Sub
Not that this code is not activating the sheet...

 

[Jim Thomlinson]

Sorry I missed the whole A1 = Input thing ... Try this...

Private Sub Workbook_Open()
Dim wks As Worksheet

For Each wks In Worksheets
If wks.Visible = xlSheetVisible And _
UCase(wks.Range("A1").Value) = "INPUT" Then
MsgBox "Format sheet " & wks.Name
'wks.select 'Select the sheet if necessary
End If
Next wks
End Sub