CanWestBoss
New Member
- Joined
- Jun 21, 2012
- Messages
- 1
I am trying to make the width of a validation list wider without widening the column width. My Validation list pull downs are in cells D7:D65536 and my list is on another sheet in a named range called ProdList
I have tried the following code i found here on these forums but with no luck. I am getting desparate as I have to have this finished by tomorrow. If anyone could assist that would be greatly appreciated
I have tried the following code i found here on these forums but with no luck. I am getting desparate as I have to have this finished by tomorrow. If anyone could assist that would be greatly appreciated
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myShp As Shape, Drp As Single
On Error Resume Next
'cells holding drop downs
If Intersect(Target, [d7:d65536]) Is Nothing Then Exit Sub
If Target.Validation.Type = xlValidateList Then
Set myShp = ActiveSheet.Shapes("Drop Down 1")
Drp = myShp.Width - Target.Width
'Column holding list, sized appropriately
myShp.Width = ["ProdList].Width
myShp.Left = Target.Left - myShp.Width / 2 + Drp * 2
End If
Set myShp = Nothing
End Sub