Invalid qualifier

[dpaton05]

I have a procedure that moves some buttons. They need to be moved to just below the bottom of the last entry in column A.
I get an error saying invalid qualifier and the lRow on the 9th line is highlighted.

I had this code:

Sub Move_Shape()
    Dim Total As Range, ws As Worksheet, Sh As Shape, NewShape As Shape, x As Long
    Dim TTop As Long, TLeft As Long, txtMainExists As Boolean, lRow As Long
    Set ws = ThisWorkbook.Worksheets("ACA_Quoting")
    
    'Find last row in column A after rows have been copied and add 1 row
    lRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    'Set the position of  the top of 1 row below the last row in column A
    x = lRow.top

    
    For Each Sh In ws.Shapes
        Debug.Print Sh.Type
        Select Case Sh.Name
        Case "cmdAddRatio", "cmdCustomSign", "cmdGsign", "cmdAsign", "cmdNoSign", "cmdSaveToPdf"
            Sh.IncrementTop x
        Case "txtMain"                                'name your first textbox, the one you want to move,  to something unique. I used "txtMain"
            txtMainExists = True
            TTop = Sh.Top                             'record position
            TLeft = Sh.Left
            Sh.IncrementTop x
            Sh.Copy                                   'make a copy
            ws.Paste
            Set NewShape = ws.Shapes(ws.Shapes.Count)    'pasted textbox is the last shape
            With NewShape
                .Top = TTop                           'move the copy to the previous position of txtMain
                .Left = TLeft
                .OLEFormat.Object.Object.Text = .Name & "    (a copy of txtMain)"
            End With
        End Select
    Next Sh
    If Not txtMainExists Then
        MsgBox "txtMain is missing!", vbCritical
    End If
End Sub

Could someone help me please?

 

[dpaton05]

I think I need to add something else in my code. What does this line do that was in post 17 Dante?

  x = Range("A" & Rows.Count).End(3)(2).Top

 

[DanteAmor]

When i try and run the code, all of the buttons get moved except one

The buttons to be moved must have one of these names. Check the button name, maybe it has some extra blank space or other name.

      Case "cmdAddRatio", "cmdCustomSign", "cmdGsign", "cmdAsign", "cmdNoSign", "cmdSaveToPdf"
      Case "txtMain"                        'name your first textbox. I used "txtMain"

____________________________________________________________________________________________________________________________________________________________

There is nothing different about that button.

You could delete that button and re-create.
____________________________________________________________________________________________________________________________________________________________

I tried to change the code a little.

I don't understand why you want to change the code. You saw the images in post #17, do you need that?
You only have a problem with a button, I suppose you have to correct that button.
____________________________________________________________________________________________________________________________________________________________

What does this line do that was in post 17 Dante?

The following lines get the same result. These lines get the row of the last cell with data +1 from column A and then get the top of that cell.

  x = Range("A" & Rows.Count).End(3)(2).Top
  x = Range("A" & Range("A" & Rows.Count).End(3).Row + 1).Top

____________________________________________________________________________________________________________________________________________________________

Lastly, this line doesn't work for me, but if it works for you, it's the only line you should change in the macro in post # 17.

.OLEFormat.Object.Object.Text = .Name & "    (a copy of txtMain)"

Try:

Sub Move_Shape()
  Dim ws As Worksheet, sh As Shape, NewShape As Shape, x As Long, txtMainExists As Boolean
 
  Set ws = ThisWorkbook.Worksheets("ACA_Quoting")
  'x = Range("A" & Rows.Count).End(3)(2).Top
  x = Range("A" & Range("A" & Rows.Count).End(3).Row + 1).Top
 
  For Each sh In ws.Shapes
    Select Case sh.Name
      Case "cmdAddRatio", "cmdCustomSign", "cmdGsign", "cmdAsign", "cmdNoSign", "cmdSaveToPdf"
        'sh.Top = x + sh.Top
        sh.IncrementTop x
      Case "txtMain"                        'name your first textbox. I used "txtMain"
        txtMainExists = True
        sh.Copy                             'make a copy
        ws.Paste
        Set NewShape = ws.Shapes(ws.Shapes.Count)    'pasted textbox is the last shape
        With NewShape
          .Top = sh.Top                     'move the copy to the previous position of txtMain
          .Left = sh.Left
          .OLEFormat.Object.Object.Text = .Name & "    (a copy of txtMain)"
          '.OLEFormat.Object.Text = .Name & "    (a copy of txtMain)"
        End With
        'sh.Top = x + sh.Top
        sh.IncrementTop x
    End Select
  Next sh
  If Not txtMainExists Then MsgBox "txtMain is missing!", vbCritical
End Sub

 

[dpaton05]

Thanks for the ideas of what I can try. I will try them when I get back to work on Friday and let you know how it goes.

 

[dpaton05]

Thanks for your help and ideas Dante. I worked it out with this code:

Sub Move_Shape()
    Dim Total As Range, ws As Worksheet, Sh As Shape, NewShape As Shape, x As Long, t As Long
    Dim TTop As Long, TLeft As Long, txtMainExists As Boolean, lRow As Long
    Set ws = ThisWorkbook.Worksheets("ACA_Quoting")
    
    x = Cells(Rows.Count, "A").End(xlUp).Row
    t = Cells(x, "A").Top + 21
    
    For Each Sh In ws.Shapes
        Debug.Print Sh.Type
        Select Case Sh.Name
        Case "cmdSaveToPdf", "cmdAddRatio", "cmdAsign"
            Sh.Top = t
        Case "cmdCustomSign", "cmdGsign", "cmdNoSign"
            Sh.Top = t + 27
        'name your first textbox, the one you want to move,  to something unique. I used "txtMain"
        Case "txtMain"
            txtMainExists = True
            'record position
            TTop = Sh.Top
            TLeft = Sh.Left
            Sh.Top = t + 60
            'make a copy
            Sh.Copy
            ws.Paste
            'pasted textbox is the last shape
            Set NewShape = ws.Shapes(ws.Shapes.Count)
            With NewShape
                'move the copy to the previous position of txtMain
                .Top = TTop
                .Left = TLeft
                .OLEFormat.Object.Object.Text = .Name & "    (a copy of txtMain)"
            End With
        End Select
    Next Sh
    If Not txtMainExists Then
        MsgBox "txtMain is missing!", vbCritical
    End If
End Sub

 

[DanteAmor]

I'm glad to hear that it works for you, thanks for the feedback.