is there any way to simplify this command ActiveSheet.Shapes("Drop Down 1154")

[Miguelhn94]

ActiveSheet.Shapes("Drop Down 1154").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 2053").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 2054").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 2055").ControlFormat.Value = 1

i just want to have a global configuration that does this command instead of every single one, there's like 30 so it slows down quite alot the opening of the excel sheet



thanks in advance to everybody!

[mjr veverka]

For example:

Please Login or Register  to view this content.

[Roel Jongman]

If the command can be simplified depends on how much logic the names have or if the only shapes on the sheet are the 30 dropdowns.
if the dropdowns are the only ones on the sheet (untested)

Please Login or Register  to view this content.

[Miguelhn94]

imma try both solutions one sec

[Miguelhn94]

Option Explicit

Sub drop_down_number()
Dim drpdwn, i As Long, indx As Long

drpdwn = Array(1154, 2053, 2054, 2055)
indx = UBound(drpdwn)

With ActiveSheet
'... commands
For i = 0 To indx
.Shapes("Drop Down " & drpdwn(i)).ControlFormat.Value = 1
Next
'... further commands
End With
End Sub



this worked out perfectly thank you!!!