Find Value for - ActiveSheet.Shapes("Drop Down X")

[sxhall]

Hi,

Found the code below on a thread in this forum to resize the for cell validationbut I want to add it in to an existing workbook. I have had this working in a new workbook and have changed the value for "ActiveSheet.Shapes("Drop Down 1")" to 2, 3 etc as I have added new dropdowns to the worksheet.

How do I make this apply to all dropdowns on a worksheet (they are all the same reference list) or find out the value X in"ActiveSheet.Shapes("Drop Down X")" ?

link - https://www.mrexcel.com/forum/excel-questions/84990-setting-width-list-box-data-validation-list.html

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myShp As Shape
Dim Drp As Single

On Error Resume Next

'cells holding drop downs

If Intersect(Target, [AA3:AA6]) 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 = [B:B].Width
    myShp.Left = Target.Left - myShp.Width / 2 + Drp * 2
    
End If

Set myShp = Nothing

End Sub

Thanks in advance

Steven

 

[John_w]

With multiple in-cell data validation dropdowns, it seems that the Shapes collection only has 1 item when you click on a data validation cell, therefore try:

Set myShp = ActiveSheet.Shapes(1)

 

[sxhall]

Many Thanks John.

I had a couple of shapes on my worksheet that this code interacted with rather then the drop-down when I changed it to a single 1. As I don't need them I've deleted them and it all works now. Hope this will be a permanent feature in Excel rather than having to use VBA for this, one-day!

 

[sxhall]

With multiple in-cell data validation dropdowns, it seems that the Shapes collection only has 1 item when you click on a data validation cell, therefore try:

Set myShp = ActiveSheet.Shapes(1)

Thanks John,

Worked brilliantly. I replied back but didn't quote your post so not sure if you would see it or not.