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?

 

[Michael M]

This was covered in a previous thread of yours, which you said was resolved then ??.....what has changed ??

 

[dpaton05]

I am not sure but I think I needed to add some rows in so it put all the calculations out. That is why I wanted to base it on the row item in the column, so I could add rows earlier and it would still work.

 

[dpaton05]

I changed the code Dante and I got an error saying invalid use of property. It also highlighted the .top

 

[DanteAmor]

I changed the code Dante and I got an error saying invalid use of property.

You did not write something well.
I put the complete macro:

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 = Range("A" & lRow).Top
  
  For Each sh In ws.Shapes
    'Debug.Print Sh.Type
    Select Case sh.Name
      Case "cmdAddRatio", "cmdCustomSign", "cmdGsign", "cmdAsign", "cmdNoSign", "cmdSaveToPdf"
        sh.Top = 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.Top = 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.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

 

[dpaton05]

It made all of the buttons to have the same top position so several covered the others as there are several rows of buttons.

 

[dpaton05]

I forgot to add that I also get an error saying object doesn't support this property or method.

 

[DanteAmor]

It made all of the buttons to have the same top position so several covered the others as there are several rows of buttons.

Before:

After:

If it's what you need, then (I optimized your code a little):

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
  For Each sh In ws.Shapes
    Select Case sh.Name
      Case "cmdAddRatio", "cmdCustomSign", "cmdGsign", "cmdAsign", "cmdNoSign", "cmdSaveToPdf"
        sh.Top = x + sh.Top
      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.Text = .Name & "    (a copy of txtMain)"
        End With
        sh.Top = x + sh.Top
    End Select
  Next sh
  If Not txtMainExists Then MsgBox "txtMain is missing!", vbCritical
End Sub

 

[DanteAmor]

I forgot to add that I also get an error saying object doesn't support this property or method.

Try the macro in post # 17 and if you have an error, please specify the line of the macro where you have the error.

 

[dpaton05]

When i try and run the code, all of the buttons get moved except one. There is nothing different about that button. It halts before the last button is moved and gives the error, object doesn't support this property or method. I get no option to debug it.

 

[dpaton05]

I tried to change the code a little. I tried to make the if statement near the start, a select case statement instead but when I try and run it, the shapes do not get incremented down as with the if statement. They get slightly dragged up.

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")
    
    lRow = Cells(Rows.Count, "H").End(xlUp).Row
   Select Case lRow
    Case lRow = 33
        x = 550
    Case lRow = 68
        x = 630
   End Select
   ' If lRow = 33 Then
    '    x = 551.5
     '   Else: x = 629.5
    'End If
    
    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