error put multiple shapes in array when move multiple buttons

[leap out]

I try moving multiple shapes to last empty column when select cell but it gives error in this line

For Each NofB In Shapes(Array("INSROW", "INSMONTH", "DELROW"))

all of the shapes are buttons

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim LastColFind As Long
Dim NofB As Variant
    For Each NofB In Shapes(Array("INSROW", "INSMONTH", "DELROW"))

    LastColFind = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    With Target
    
        Me.Shapes("BUTTON " & NofB).Top = .Top
        Me.Shapes("BUTTON " & NofB).Left = ActiveSheet.Cells(1, LastColFind + 1).Left + 2
    End With
    Next NofB
End Sub

 

[Domenic]

Try the following instead . . .

For Each NofB In Array("INSROW", "INSMONTH", "DELROW")

Hope this helps!

 

[leap out]

thanks, now move the error to this line

 Me.Shapes("BUTTON " & NofB).Top = .Top

 

[Domenic]

What type of error are you getting?

 

[leap out]

it gives

The item with the specified name wasn't found". error​

 

[Domenic]

It looks like it couldn't find a shape named either "BUTTON INSROW", "BUTTON INSMONTH", or "BUTTON DELROW" on your sheet. Do you in fact have three shapes with those names? If so, check for spelling mistakes.

 

[leap out]

Hi Domenic !
thanks for inform me about this

It looks like it couldn't find a shape named either "BUTTON INSROW", "BUTTON INSMONTH", or "BUTTON DELROW" on your sheet

I don't seem to understand the code well, yes you're right the buttons name are not matched . I thought word BUTTON is not relating for shapes names . I thought this properties should indicate button name as is known .
in reality the shepas names as is in line array , it doesn't contain word BUTTON precede for each name . I try to change this

Me.Shapes("BUTTON " & NofB).Top = .Top
        Me.Shapes("BUTTON " & NofB).Left = ActiveSheet.Cells(1, LastColFind + 1).Left + 2

to

Me.Shapes(NofB).Top = .Top
        Me.Shapes(NofB).Left = ActiveSheet.Cells(1, LastColFind + 1).Left + 2

but shows the same error in array line .
I note if I correct as you suggestion shows me another problem .

orginal data

The buttons will be placed on top of each other

what I want moving the button to last empty columns like this

thanks for your help .

 

[Domenic]

With your buttons named INSROW, INSMONTH, and DELROW, try...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    
    Dim LastColFind As Long
    LastColFind = Me.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    Dim NofB As Variant
    For Each NofB In Array("INSROW", "INSMONTH", "DELROW")
        Me.Shapes(NofB).Left = Me.Cells(1, LastColFind + 1).Left + 2
    Next NofB
End Sub

Note, however, this code will run every time you select a cell in your worksheet. Is this your intent?

 

[leap out]

Note, however, this code will run every time you select a cell in your worksheet. Is this your intent?

yes because every time I will insert new column with fill data ,then will move the buttons to empty column
your vesion works excellently
thanks very much .

 

[Domenic]

You're very welcome, glad I could help.

Cheers!