How do i enable this macro to run everytime i open or edit anything on my excel?

Question

Sorry, I'm new to excel-vba and need some guidance. I want to run auto-adjust the height of merged cells upon opening my excel file.

I found the code to auto-adjust the merged cell height, which I've tried and it works. I also found the code that supposes to enable macros to run everytime when the Excel file is opened. However, I have trouble integrating both.

Code to auto-adjust height of cell

Option Explicit

Public Sub AutoFitAll()
    Call AutoFitMergedCells(Range("I13:L13"))
End Sub


Public Sub AutoFitMergedCells(oRange As Range)
  Dim tHeight As Integer
  Dim iPtr As Integer
  Dim oldWidth As Single
  Dim oldZZWidth As Single
  Dim newWidth As Single
  Dim newHeight As Single
  With Sheets("Acceptance")
    oldWidth = 0
    For iPtr = 1 To oRange.Columns.Count
      oldWidth = oldWidth + .Cells(1, oRange.Column + iPtr - 1).ColumnWidth
    Next iPtr
    oldWidth = .Cells(1, oRange.Column).ColumnWidth + .Cells(1, oRange.Column + 1).ColumnWidth
    oRange.MergeCells = False
    newWidth = Len(.Cells(oRange.Row, oRange.Column).Value)
    oldZZWidth = .Range("ZZ1").ColumnWidth
    .Range("ZZ1") = Left(.Cells(oRange.Row, oRange.Column).Value, newWidth)
    .Range("ZZ1").WrapText = True
    .Columns("ZZ").ColumnWidth = oldWidth
    .Rows("1").EntireRow.AutoFit
    newHeight = .Rows("1").RowHeight / oRange.Rows.Count
    .Rows(CStr(oRange.Row) & ":" & CStr(oRange.Row + oRange.Rows.Count - 1)).RowHeight = newHeight
    oRange.MergeCells = True
    oRange.WrapText = True
    .Range("ZZ1").ClearContents
    .Range("ZZ1").ColumnWidth = oldZZWidth
  End With
End Sub

Code that enable the macro to run upon opening excel

Private Sub Worksheet_Activate()
    'your code here
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    'your code here
End Sub

So what I'm hoping to achieve is that when I open the excel file, the merged cells that has excessive content that is unable to be shown within the default height of the cells would automatically be adjusted so all the contents can be seen.

I'm confused! You say you want the code to run when the workbook opens but your code refers to worksheet events. — Mark Fitzgerald

Unknown Unknown · Accepted Answer · 2019-05-22T08:30:56

Worksheet_Activate() and Worksheet_Change do not run when you open the workbook. They run when the sheet is activated or changed.

You need Private Sub Workbook_Open() which is in the ThisWorkbook class.

To run your macro when your workbook opens you would add the call to your Sub in there

Private Sub Workbook_Open()
    AutoFitAll
End Sub

How do i enable this macro to run everytime i open or edit anything on my excel?

1 Answers