Running a Macro when Excel Starts in All Tabs

[Ardiko]

Hi Everyone,

I can do this now for one tab but does anyone know the code to make it work for all tabs featured when the spreadsheet upon opening the excel file?

http://office.microsoft.com/en-ie/ex...001034628.aspx

Thank you

[HaHoBe]

Hi, Ardiko,

maybe

Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ws.Range("A1").Value = Date
  Next ws
End Sub

Ciao,
Holger

[Ardiko]

Thanks Holger,

Could you attach that in a working excel sheet so I can look at it?
Can't seem to insert what you did into excel.

[HaHoBe]

Hi, Ardiko,

the code must reside in ThosWorkbook(DieseArbeitsmappe in german), not in a nomral module or a class module because only in ThisWorkbook the event gets triggered once you open the workbook.

Ciao,
Holger

[Ardiko]

Holger,

One last question, I want to insert my own macros into that code now. I have no proper coding knowledge so I usually just copy and paste my recorded macros / tweak it till it works.
Do I put ws. in front of my own macros? e.g. ws.column

Apologies for asking more questions.

[HaHoBe]

Hi, Ardiko,

if nothing is mentioned in the code every range (Columns, Rows, Ranges, Cells) refers to the active sheet. By adding the ws. to these or referring to ws in a With-Staement makes these available on the respective worksheet. If you have problems implementing this into your code feel free to post the code for us here to help you out on that issue.

Apologies for asking more questions.

No apologies necessary - the example given is really a basic one, and if you have a more distinct code you should get it to work smoothly (and as soon as possible ).

Ciao,
Holger

[Ardiko]

Thanks Holger,

Much appreciated! I made these 5 macros and would like to know how I can automatically implement on all tabs. If I see how you tweak the code I'll be able to do it for everything. Cheers!

Automatic changes excel sheet.xlsm

[HaHoBe]

Hi, Ardiko,

code for ThisWorkbook:

Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    If ws.Name <> "Format" Then
      ColourTab_a ws.Name
      BoldText_a ws.Name
      InsertBorders_a ws.Name
      ColumnWidth_a ws.Name
      RowHeight_a ws.Name
    End If
  Next ws
End Sub

In a standard module:

Sub ColourTab_a(strsheet As String)
'
' ColourTab Macro
'

'
    With Worksheets(strsheet).Range("A1:D1, A7:D7").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub
Sub BoldText_a(strsheet As String)
'
' BoldText Macro
'

'
    Worksheets(strsheet).Range("A1:D1, A7:D7").Font.Bold = True
End Sub
Sub ColumnWidth_a(strsheet As String)
'
' Columnwidth Macro
'

'
    Worksheets(strsheet).UsedRange.Columnwidth = 10
End Sub
Sub RowHeight_a(strsheet As String)
'
' Columnheight Macro
'

'
    Worksheets(strsheet).Columns("C:C").Rowheight = 30
End Sub

Sub InsertBorders_a(strsheet As String)
'
' Insertborders Macro
'

'
    With Worksheets(strsheet).Range("A1:A7")
      .Borders(xlDiagonalDown).LineStyle = xlNone
      .Borders(xlDiagonalUp).LineStyle = xlNone
      .Borders(xlEdgeLeft).LineStyle = xlNone
      .Borders(xlEdgeTop).LineStyle = xlNone
      .Borders(xlEdgeBottom).LineStyle = xlNone
      With .Borders(xlEdgeRight)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      .Borders(xlInsideVertical).LineStyle = xlNone
      .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    With Worksheets(strsheet).Range("C1:C7")
      .Borders(xlDiagonalDown).LineStyle = xlNone
      .Borders(xlDiagonalUp).LineStyle = xlNone
      With .Borders(xlEdgeLeft)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      .Borders(xlEdgeTop).LineStyle = xlNone
      .Borders(xlEdgeBottom).LineStyle = xlNone
      .Borders(xlEdgeRight).LineStyle = xlNone
      .Borders(xlInsideVertical).LineStyle = xlNone
      .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    With Worksheets(strsheet).Range("D1")
      .Borders(xlDiagonalDown).LineStyle = xlNone
      .Borders(xlDiagonalUp).LineStyle = xlNone
      With .Borders(xlEdgeLeft)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      With .Borders(xlEdgeTop)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      With .Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      With .Borders(xlEdgeRight)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      With .Borders(xlInsideVertical)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      With .Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
    End With
End Sub

We could use BorderAround to trim the code for the last procedure.

Ciao,
Holger

[Ardiko]

Cheers Holger - This is going to help me immensely!