Does macro change font automatically when clsoing workbook and how do I get to do on mutiple sheets

[ghrek]

Hi

I have the following macro and trying to find out if it will automatically change the font and save it when the workbook closes and how do I do it for mutiple sheets in same workbook.


Private Sub Workbook_Close()
Cells.Select
With Selection.Font
.Name = "Daytona Condensed"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A5308").Select
Selection.End(xlUp).Select
ActiveWindow.SmallScroll Down:=-6
End Sub

 

[DanteAmor]

Hi @ghrek , thanks for posting on the forum.

You didn't mention if it's all the sheets of the book or just some.

for mutiple sheets in same workbook.

I present to you the 2 alternatives.

For all sheets:

Private Sub Workbook_Close()
  Dim sh As Worksheet
  Application.ScreenUpdating = False
  For Each sh In Sheets
        sh.Select
        With sh.Cells.Font
          .Name = "Daytona Condensed"
          .FontStyle = "Regular"
          .Size = 11
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .Underline = xlUnderlineStyleNone
          .TintAndShade = 0
          .ThemeFont = xlThemeFontNone
        End With
        sh.Range("A5308").End(xlUp).Select
  Next
  Application.ScreenUpdating = True
End Sub

For some sheets, but you must indicate the name in this line of the macro:

Case "Sheet1", "Sheet2", "report", "etc"

Then:

Private Sub Workbook_Close()
  Dim sh As Worksheet
  Application.ScreenUpdating = False
  For Each sh In Sheets
    Select Case sh.Name
      Case "Sheet1", "Sheet2", "report", "etc"
        sh.Select
        With sh.Cells.Font
          .Name = "Daytona Condensed"
          .FontStyle = "Regular"
          .Size = 11
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .Underline = xlUnderlineStyleNone
          .TintAndShade = 0
          .ThemeFont = xlThemeFontNone
        End With
        sh.Range("A5308").End(xlUp).Select
    End Select
  Next
  Application.ScreenUpdating = True
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[ghrek]

Many thanks

Its the all sheets one that im going to use. One thing I just noticed and didnt think about was if the workbook is "read only" dont run the macro when closing. Can that be added to it?

 

[DanteAmor]

is "read only" dont run the macro

Try this:

Private Sub Workbook_Close()
  Dim sh As Worksheet
  
  If ThisWorkbook.ReadOnly = True Then Exit Sub
  
  Application.ScreenUpdating = False
  For Each sh In Sheets
        sh.Select
        With sh.Cells.Font
          .Name = "Daytona Condensed"
          .FontStyle = "Regular"
          .Size = 11
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .Underline = xlUnderlineStyleNone
          .TintAndShade = 0
          .ThemeFont = xlThemeFontNone
        End With
        sh.Range("A5308").End(xlUp).Select
  Next
  ThisWorkbook.Save
  Application.ScreenUpdating = True
End Sub