CODEWITHSUNDEEP
excel
macaugmxus
macaugmxus

Reputation: 61

Autofit doesn't work in worksheet_active() in Excel

I have these codes to have both workbook opening and switching sheet would have all being wraptext and autofit to make all long data to be muti links:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Me.Sheets
ws.Cells.WrapText = True
ws.Cells.EntireRow.AutoFit
Next ws
End Sub



Private Sub Worksheet_Activate()
Dim ws As Worksheet
For Each ws In Me.Sheets
ws.Cells.WrapText = True
ws.Cells.EntireRow.AutoFit
Next ws
End Sub

The workbook open part works, but the worksheet_activate part doesn't. I have few sheets in the workbook, and few field of a sheet is depend on another sheet. After I make change to make a field becomes long, the another sheet result cannot catch up the length and hidden below in the box. I have to retarts the excel in order to let the workbook open part work and all hidden words would be seen again.

Upvotes: 0

Views: 154

Answers (1)

Monty Wild
Monty Wild

Reputation: 4001

You need to have the Private Sub Worksheet_Activate() in each sheet's code module. Also, the code in each worksheet needs to be:

Private Sub Worksheet_Activate()
    Me.Cells.WrapText = True
    Me.Cells.EntireRow.AutoFit
End Sub

as in that context, Me refers to a Worksheet object, not a Workbook object.

Upvotes: 1

Related Questions