Invalid qualifier

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,019
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

VBA 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

Well-known Member
Joined
Aug 14, 2018
Messages
2,019
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I think I need to add something else in my code. What does this line do that was in post 17 Dante?

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

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.
VBA Code:
      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.
VBA Code:
  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.
VBA Code:
.OLEFormat.Object.Object.Text = .Name & "    (a copy of txtMain)"

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

Well-known Member
Joined
Aug 14, 2018
Messages
2,019
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Aug 14, 2018
Messages
2,019
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for your help and ideas Dante. I worked it out with this code:

VBA 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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to hear that it works for you, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

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
Top