Run Worksheet Modules on Save

Question

I have a macro that is currently running within 4 worksheets that highlights cell rows within a certain range (the ranges differ for each worksheet) but to keep the worksheet looking clean and not to leave the highlighting it has built in to it a line that tells it to clear highlighting when I click cell A6 in each of the worksheets where the macro is contained. My issue is getting others who use the worksheet to follow this method, so I am trying to see if there is a way to use the Workbook_BeforeSave workbook function to clear all highlighting on the worksheets when the file is saved.

Is there a way to iterate the "clear formatting" sub that exists in each worksheet from the Workbook module? The clearing code in the worksheet modules is as follows (but I cannot seem to get it to function within the workbook module):

Dim bInRange As Boolean
Static rOld As Range

If Not bInRange Then
Set rOld = Nothing
Exit Sub
End If

score 2 · Accepted Answer · answered Jul 17 '15 at 15:03

Create a sub for your code. Something like this.

Private sub RunMyCode()
    Dim bInRange As Boolean
    Static rOld As Range

    If Not bInRange Then
        Set rOld = Nothing
        Exit Sub
    End If
End sub

Private Sub Workbook_BeforeClose(Cancel as Boolean)
    'Call it before your workbook is closed
    RunMyCode
End Sub

score 1 · Answer 2 · answered Jul 17 '15 at 15:03

You can run the code like such

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

call sheet1.clear_formatting
call sheet2.clear_formatting
call sheet3.clear_formatting
call sheet4.clear_formatting
end sub

sheet1 isthe code name for that sheet, you can also use sheets("sheet1").clear_formatting if that is easier

or if you have lots of sheets you could do

for each ws in activeworkbook.sheets
    call ws.clear_formatting
next

Run Worksheet Modules on Save

2 Answers2