CODEWITHSUNDEEP
vbaexcel
SVTFX4
SVTFX4

Reputation: 49

Excel Adjust Column Width based on Cell Value

So I have a schedule workbook that I have setup to flip between English and Spanish based on a drop down menu located on the Input Tab.

Drop Down menu choice will result in cell I12 equal 1 or 2.
I'd like for this to determine the Column D Width of Worksheets First Week and Second Week.
If Cell I12 is 1 Column width would be 3.33(37) , or if I12 is 2 Column width would be 5.22(54).
Autofit doesn't work as it doesnt also decrease if it goes back to english.

English

Spanish

Thanks for Tim's help I got it working. Ran into a little glitch as I forgot to mention the sheets were protected.

I used the following and works fine

Private Sub Worksheet_Activate()
 ActiveSheet.Unprotect Password:="passwordhere"
  Select Case ThisWorkbook.Sheets("Input").Range("I12").Value
    Case 1: Me.Columns(4).ColumnWidth = 2.5
    Case 2: Me.Columns(4).ColumnWidth = 5
  End Select
 ActiveSheet.Protect Password:="passwordhere"

End Sub

Upvotes: 0

Views: 4805

Answers (1)

Tim Williams
Tim Williams

Reputation: 166241

Simplest approach is to put something like this in the worksheet code module for each sheet where you need to adjust the column width:

Private Sub Worksheet_Activate()
    Select Case ThisWorkbook.Sheets("Input").Range("I12").Value
        Case 1: Me.Columns(4).ColumnWidth = 10
        Case 2: Me.Columns(4).ColumnWidth = 20
    End Select
End Sub

Upvotes: 2

Related Questions