Change Column width when Cell change

[Mikael_L]

Hi there,

Trying to do something fairly simple, but haven't been able to look up the correct answer, so hopeing for some help here ..

I've got a sheet where I load different cell values depending on a drop-down menu in C6.
The loaded cell values vary a lot in text length, so I would like some kind of VBA that autofit my columns width, D:Q, when the drop-down menu change value.


Best
Mikael

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Columns("D:Q")) Is Nothing Then Exit Sub
    
    Target.EntireColumn.AutoFit

End Sub

This should automatically Autofit the column when a value in columns D:Q is entered/changed.

 

[Mikael_L]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Columns("D:Q")) Is Nothing Then Exit Sub
   
    Target.EntireColumn.AutoFit

End Sub

This should automatically Autofit the column when a value in columns D:Q is entered/changed.

Hi Joe,

Thanks, but it seems like nothing happens.. First thougt it was due to I had to unprotect the sheet but didn't help to do that..
Here is the code I have now:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rs As Worksheet

    If Intersect(Target, Columns("D:Q")) Is Nothing Then Exit Sub
    
    ActiveSheet.Unprotect Password:="hi"
    rs.Unprotect Password:="hi"
        
    Target.EntireColumn.AutoFit
    
    rs.Protect Password:="hi"
    ActiveSheet.Protect Password:="hi"
    

End Sub

It should also be a change in C6 that got the code to run, don't now if that makes a difference?

 

[Joe4]

You declare "rs" in your code in a Dim statement, but then you never set it equal to anything.
So "rs" is empty/nothing.

It should also be a change in C6 that got the code to run, don't now if that makes a difference?

I am not sure what you mean by this. Please explain in more detail.
Are you making the change in cell C6, or in columns D:Q?
How exactly is data being entered/changed?

 

[Mikael_L]

You declare "rs" in your code in a Dim statement, but then you never set it equal to anything.
So "rs" is empty/nothing.


I am not sure what you mean by this. Please explain in more detail.
Are you making the change in cell C6, or in columns D:Q?
How exactly is data being entered/changed?

The "rs" was just a copy paste from another document to try to get the code to unprotect the sheet.

I have a dropdown menu in C6, and it is when I change the the value in this, I would like to autofit column width in D:Q..
But of course the change og the value in C6 also makes the values in D:Q change, so it shouldn't matter anyways.

 

[Joe4]

The "rs" was just a copy paste from another document to try to get the code to unprotect the sheet.

Then you missed copying the part where it sets "rs" equal to something!

Just get rid of it altogether, and change this line:

    rs.Unprotect Password:="hi"

to this:

    ActiveSheet.Unprotect Password:="hi"

and do the same for the protect line.

But of course the change og the value in C6 also makes the values in D:Q change, so it shouldn't matter anyways.

Actually, it does matter how cells are updated.
Worksheet_Change event procedure code is only triggered when data is manually updated in those cells.
Cells that change because they contain formulas or links will NOT trigger this code to run.

Can you tell me exactly what is in columns D:Q?
Are these formulas?
How does changing C6 change the values in those columns?

 

[Mikael_L]

Actually, it does matter how cells are updated.
Worksheet_Change event procedure code is only triggered when data is manually updated in those cells.
Cells that change because they contain formulas or links will NOT trigger this code to run.

Can you tell me exactly what is in columns D:Q?
Are these formulas?
How does changing C6 change the values in those columns?

Ohh okay..

D8:Q8 contains a lookup formula, that uses the sheet name in C6 to determine in which sheet it should make the lookup (it's done with the indirect function).
So no manually updates are done..
It should actually also only be when the change is happening in D8:Q8 the code is run, as there would be done some manually typing in he below cells afterwards, and there is no need for the code to be run all the time.

 

[Joe4]

Try this variation then:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C6")) Is Nothing Then Exit Sub
    
    ActiveSheet.Unprotect Password:="hi"
    Columns("D:Q").EntireColumn.AutoFit
    ActiveSheet.Protect Password:="hi"

End Sub

 

[Mikael_L]

Try this variation then:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C6")) Is Nothing Then Exit Sub
   
    ActiveSheet.Unprotect Password:="hi"
    Columns("D:Q").EntireColumn.AutoFit
    ActiveSheet.Protect Password:="hi"

End Sub

Works Perfectly, Thanks !!

 

[Joe4]

You are welcome.
Glad I was able to help.

I hope it all makes sense. If it does not, please feel free to ask any questions.