VBA to auto resize column width when altered

[Binbs]

Hello,

I found the following code that will resize a column to the desired width.

Sub Set_Column_Range_Width()

Dim MySheet As Worksheet

Set MySheet = ActiveSheet


With MySheet.Columns("C")
 .ColumnWidth = 52.27
End With
End Sub

Is there a way to make this VBA code run when a column is resized? Protecting the sheet is not an option.

I have the following VBA code below already in the sheet so I am hoping to incorporate the solution within that code.

Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range

    Set KeyCells = Range("H7:K7")

    If Not Application.Intersect(KeyCells, Target) Is Nothing Then

        Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh
    
      
End If
    
    
End Sub

Thank you!

 

[Logit]

If you are asking for an 'auto-fit columns' macro ....

Option Explicit

Sub autofitwidth()
Dim i As Integer
Cells.EntireColumn.AutoFit
    For i = 1 To ActiveSheet.UsedRange.Columns.Count
        Columns(i).ColumnWidth = Columns(i).ColumnWidth
    Next i

Worksheets("Sheet1").Range("A:A").EntireRow.AutoFit
End Sub

 

[Binbs]

If you are asking for an 'auto-fit columns' macro ....

Option Explicit

Sub autofitwidth()
Dim i As Integer
Cells.EntireColumn.AutoFit
    For i = 1 To ActiveSheet.UsedRange.Columns.Count
        Columns(i).ColumnWidth = Columns(i).ColumnWidth
    Next i

Worksheets("Sheet1").Range("A:A").EntireRow.AutoFit
End Sub

Hi and thanks but I am not trying to autofit but rather fit to a specific width.

I want the code to resize the column to the desired width (ex. 52.27) whenever it's changed to anything other than that width. Basically preventing anyone from changing the width for that column to anything except 52.27. Protecting the sheet isn't an option.

Please let me know if that isn't clear.

 

[Logit]

Ok ... does this work ?

Sub Worksheet_Change(ByVal Target As Range)

Dim MySheet As Worksheet
Dim KeyCells As Range

Set MySheet = ActiveSheet
Set KeyCells2 = Range("H7:K7")

        If Not Application.Intersect(KeyCells2, Target) Is Nothing Then

            Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh
      
    End If


    With MySheet.Columns("C")
         .ColumnWidth = 52.27
    End With

End Sub

 

[Binbs]

Ok ... does this work ?

Sub Worksheet_Change(ByVal Target As Range)

Dim MySheet As Worksheet
Dim KeyCells As Range

Set MySheet = ActiveSheet
Set KeyCells2 = Range("H7:K7")

        If Not Application.Intersect(KeyCells2, Target) Is Nothing Then

            Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh
     
    End If


    With MySheet.Columns("C")
         .ColumnWidth = 52.27
    End With

End Sub

No, that doesn't seem to work to resize column C when it is changed.

 

[Jimmypop]

No, that doesn't seem to work to resize column C when it is changed.

And if you change

Sub Worksheet_Change(ByVal Target As Range)

to

Sub Worksheet_SelectionChange(ByVal Target As Range)

Does it work then?

 

[Binbs]

And if you change

Sub Worksheet_Change(ByVal Target As Range)

to

Sub Worksheet_SelectionChange(ByVal Target As Range)

Does it work then?

This works to resize column C! Thanks so much to you both for the help!

But, it causes my other code to run slowly and not perform the way I need it to so I ended up doing this:

Sub Worksheet_Change(ByVal Target As Range)


Dim KeyCells As Range


Set KeyCells2 = Range("H7:K7")

        If Not Application.Intersect(KeyCells2, Target) Is Nothing Then

            Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh
      
    End If


    
End Sub


Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MySheet As Worksheet

Set MySheet = ActiveSheet


With MySheet.Columns("C")
 .ColumnWidth = 52.27
End With
End Sub

 

[Logit]

Glad you have an answer. You are welcome.

 

[Jimmypop]

No problem... Glad to help.