Ctrl Z not working with VBA Column Auto Fit code

[Peter Davison]

I currently have the following code in a worksheet, however when it is on Ctrl Z will not work, but when the code is not on it will.
Any thoughts why this is happening?
And if there is a resolve?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.EntireColumn.AutoFit
End Sub


Thanks

 

[RoryA]

Code that changes the workbook will break the undo stack in Excel. There is no simple solution to that unfortunately. I'm not really sure why you would want code that resizes all the columns every time you select anything though?

 

[Beneindias]

If I'm not mistaken, you cant Undo something done with VBA

 

[Peter Davison]

Thanks both for your help.
The reason I was using resize was because it was a heading selection that may be an inch wide for some and two inches wide for others depending on the selection.

 

[Beneindias]

Thanks both for your help.
The reason I was using resize was because it was a heading selection that may be an inch wide for some and two inches wide for others depending on the selection.

Maybe run the code when opening the workbook, instead of selection change

 

[Peter Davison]

If I wanted it on a button to update the columns what would I have to change in the code?
Would it just be the top line to -
Sub ColumnFit()
and put it into a module instead of on a worksheet?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.EntireColumn.AutoFit
End Sub

 

[RoryA]

Basically yes.

 

[Peter Davison]

Thanks all for your help
it is really appreciated.

 

[Gogsan]

If I wanted it on a button to update the columns what would I have to change in the code?
Would it just be the top line to -
Sub ColumnFit()
and put it into a module instead of on a worksheet?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.EntireColumn.AutoFit
End Sub

Did you manage to solve this problem, if yes, how?

 

[Peter Davison]

I eventually used the code below
Hope this helps.

Sub ApplyAlignmentToRange()
    Dim targetSheetName As String
    targetSheetName = "Report Writer"

    ' Specify the target sheet
    Dim targetSheet As Worksheet
    On Error Resume Next
    Set targetSheet = Worksheets(targetSheetName)
    On Error GoTo 0

    ' Check if the target sheet exists
    If Not targetSheet Is Nothing Then
        ' Specify the range to check (C12:ZZ1000 in this example)
        Dim targetRange As Range
        Set targetRange = targetSheet.Range("C12:ZZ1000")

        ' Check if the target range is not empty
        If Not targetRange Is Nothing Then
            ' Disable screen updating for performance
            Application.ScreenUpdating = False

            ' Call a separate subroutine to apply the alignment logic
            ApplyAlignmentLogicToColumns targetRange

            ' Enable screen updating after processing
            Application.ScreenUpdating = True
        Else
            MsgBox "Specified range is empty.", vbExclamation
        End If
    Else
        MsgBox "Worksheet '" & targetSheetName & "' not found.", vbExclamation
    End If
End Sub

Sub ApplyAlignmentLogicToColumns(rng As Range)
    Dim col As Range
    ' Loop through each column in the specified range
    For Each col In rng.Columns
        ' Check if the column contains any text
        If Application.CountIf(col, "*") > 0 Then
            ' Align left for text columns
            col.HorizontalAlignment = xlLeft
        Else
            ' Align center for other columns
            col.HorizontalAlignment = xlCenter
        End If
    Next col
End Sub