Macro to work before saving only when certain worksheets are active

[Pauljj]

I have created a macro which works before the document is saved, it all works fine but if I wanted to modify it, so it only happened before saving, when certain worksheets were active, how could I modify this code to do that please ?

 

[hsk]

Below code will displays all
"workbook - worksheet"

probably u can build logic to run through the list and check of your sheets ...

For Each ws In Application.Worksheets
    MsgBox ws.Parent.Name & " - " & ws.Name
Next

 

[Peter_SSs]

Do you mean something like this?

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeSave(<SPAN style="color:#00007F">ByVal</SPAN> SaveAsUI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, Cancel As <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> SheetsToActOn()<br>    <SPAN style="color:#00007F">Dim</SPAN> x<br>    <br>    SheetsToActOn = Array("Sheet1", "Sheet3") <SPAN style="color:#007F00">'<- Adjust to suit</SPAN><br>    <br>    x = Application.Match(ActiveSheet.Name, SheetsToActOn, 0)<br>    <SPAN style="color:#00007F">If</SPAN> IsNumeric(x) <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'Your action code goes here</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Pauljj]

Just so you don't think I am being lazy, I have this working....as you can see I wrote it, as its not very clever but I have a feeling its not the best way of writing the code ?

Private Sub WORKBOOK_BEFORESAVE(ByVal SAVEASUI As Boolean, Cancel As Boolean)

If ActiveSheet.Name = "Sunday" Or ActiveSheet.Name = "Monday" Or ActiveSheet.Name = "Tuesday" Or ActiveSheet.Name = "Wednesday" Or ActiveSheet.Name = "Thursday" Or ActiveSheet.Name = "Friday" Or ActiveSheet.Name = "Saturday" Then

ActiveWorkbook.ResetColors

End If

End Sub

 

[Peter_SSs]

Just so you don't think I am being lazy, I have this working....as you can see I wrote it, as its not very clever but I have a feeling its not the best way of writing the code ?

Private Sub WORKBOOK_BEFORESAVE(ByVal SAVEASUI As Boolean, Cancel As Boolean)

If ActiveSheet.Name = "Sunday" Or ActiveSheet.Name = "Monday" Or ActiveSheet.Name = "Tuesday" Or ActiveSheet.Name = "Wednesday" Or ActiveSheet.Name = "Thursday" Or ActiveSheet.Name = "Friday" Or ActiveSheet.Name = "Saturday" Then

ActiveWorkbook.ResetColors

End If

End Sub

If your code works and it isn't slow, then there's nothing really wrong with it.

However, putting those worksheet names into the array in my code should tidy the code a little.

 

[Pauljj]

Thanks Peter much appreciated

 

[Peter_SSs]

Just one further thing. If the worksheets in the workbook are fixed and the list of sheets to NOT act on is much shorter, that list could be provided instead, and the match logic reversed. Something like this:

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeSave(<SPAN style="color:#00007F">ByVal</SPAN> SaveAsUI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, Cancel As <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> DontActSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> x<br>    <br>    DontActSheets = Array("abc", "def") <SPAN style="color:#007F00">'<- Adjust to suit</SPAN><br>    <br>    x = Application.Match(ActiveSheet.Name, DontActSheets, 0)<br>    <SPAN style="color:#00007F">If</SPAN> IsError(x) <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'Your action code goes here</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Pauljj]

I understand, thanks Peter that is great