Macro to loop thru all open workbooks & unhidden worksheets

[Bob_D]

I have XL 2003 and I want to loop thru all open workbooks & unhidden worksheets to change the same cell in each sheet. I have worksheets that continuously calculate which makes it difficult to manually change cell values across multiple sheets since I can only activate them between calculations. I have a macro that will turn on & off the calculations when I need to but after each on/off cycle I want to loop thru each sheet in each workbook to change cell A1 to read either Calc On or Calc Off and change the text color to white, background to red for Calc Off or green for Calc On and center & bold the text.

I found several examples to loop thru workbooks and worksheets in one book but none together. Combining them using the suggestions makes the changes to the active sheet but doesn't loop thru worksheets or workbooks.

Any help in creating macros to do this, 1 for 'on' and 1 for 'off', would be greatly appreciated.

 

[JLGWhiz]

All open workbooks will have the same setting for Calculation, either Manual or Automatic, so putting the status in each sheet of each workbook seems like an overkill. You only need to know what the first one you look at is set to and all the others will be the same. Calculation is an Application level setting, not a workbook or worksheet setting.

 

[John_w]

Welcome to MrExcel forums.

Try these macros:

Public Sub Calc_On()

    Dim wbWindow As Window
    Dim wb As Workbook
    Dim ws As Worksheet
    
    For Each wbWindow In Windows
        Set wb = wbWindow.Parent
        For Each ws In wb.Worksheets
            If ws.Visible = xlSheetVisible Then
                With ws.Range("A1")
                    .Value = "Calc On"
                    .Font.Bold = True
                    .Font.Color = vbWhite
                    .Interior.Color = vbGreen
                    .HorizontalAlignment = xlCenter
                End With
            End If
        Next
    Next

End Sub


Public Sub Calc_Off()

    Dim wbWindow As Window
    Dim wb As Workbook
    Dim ws As Worksheet
    
    For Each wbWindow In Windows
        Set wb = wbWindow.Parent
        For Each ws In wb.Worksheets
            If ws.Visible = xlSheetVisible Then
                With ws.Range("A1")
                    .Value = "Calc Off"
                    .Font.Bold = True
                    .Font.Color = vbWhite
                    .Interior.Color = vbRed
                    .HorizontalAlignment = xlCenter
                End With
            End If
        Next
    Next

 

[Bob_D]

Thanks a lot. It worked. The only other thing I'd like is to not have it happen to the PERSONAL.XLS workbook as I have my macros stored in there. But it's not a big deal.

 

[JLGWhiz]

Read post #2 carefully.

 

[Bob_D]

All open workbooks will have the same setting for Calculation, either Manual or Automatic, so putting the status in each sheet of each workbook seems like an overkill. You only need to know what the first one you look at is set to and all the others will be the same. Calculation is an Application level setting, not a workbook or worksheet setting.

Read post #2 carefully.

Yes I know that & I originally set it up that way. My original post was asking about changing cell a1 in each sheet in each workbook after the on or off calc done once.

 

[JLGWhiz]

OK, just wanted to be sure you understood that if the workbook (including personal) is open when the a change is made to the Caluculation setting, it affects all. As far as the pasting of the status in cell A1, you can exclude the Personal.xlb by an If statement. eg. 'If wb.Name <> "Personal.xlb" Then" before the part that puts the status into cell A1.