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

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
203
Office Version
  1. 365
Platform
  1. Windows
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

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

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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:
Code:
Set myShp = ActiveSheet.Shapes(1)
 
Upvote 0
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!
 
Upvote 0
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:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,218,625
Messages
6,143,580
Members
450,493
Latest member
Woejeber

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
Back
Top