VBA Target Address Select Case Issue

[gordonc068]

Hi all, need some help as I do not know VBA. I have a form that my company uses to hire new folks. One of the cells is a Organization selection drop-down. Because of what other items are in that column I cannot re-size the whole column but want the drop-down to be wider when it is selected because you can't read your options at the normal width (ie, all you see is 8 options that read "Sale Lake Cit... Salt Lake Cit..). Each option starts with the branch location, then the line of business. So I have do some searching and came across the select case. I used it and it worked 1/2 correct. When I first used it the cell did grow when selected, but once unselected it did not return to the original width.

Any help would be greatly appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$G$30" Then
Select Case Target.Address
Case "$G$30"
Target.Columns.Columnwidth = 35
Case Else
Range("G:G").Columnwidth = 8.86
End Select
Application.ScreenUpdating = True
End If
End Sub

 

[Norie]

Do you need the If?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Select Case Target.Address
        Case "$G$30"
            Target.Columns.Columnwidth = 35
        Case Else
            Range("G:G").Columnwidth = 8.86
     End Select

End If

 

[gordonc068]

That did the trick! But now I'm running into a problem I hadn't though of. I had the cell setup to wrap the text so it could all be displayed but this seems to be forcing it to remain the original height.

 

[JoeMo]

Assuming the drop down is a validation drop down, why not use the change event rather than selection change?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("G30")) Is Nothing Then Target.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

 

[gordonc068]

JoeMo,

I gave that a try but it only expands the selection After you've picked from the dropdown, but does not expand the dropdown for you to pick from.

 

[JoeMo]

JoeMo,

I gave that a try but it only expands the selection After you've picked from the dropdown, but does not expand the dropdown for you to pick from.

Then you need to stay with the selection change event.

 

[gordonc068]

Would there be a way to write something to the effect of: If G30 = "" then keep the row height 15.75, if G30 contains text make the row height 30.75?