VBA Target Address Select Case Issue

gordonc068

New Member
Joined
Feb 8, 2016
Messages
12
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Do you need the If?
Code:
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

New Member
Joined
Feb 8, 2016
Messages
12
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,461
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming the drop down is a validation drop down, why not use the change event rather than selection change?

Code:
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

New Member
Joined
Feb 8, 2016
Messages
12

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,461
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 8, 2016
Messages
12
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?
 

Forum statistics

Threads
1,136,969
Messages
5,678,893
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top