Invalid qualifier

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,341
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,853
Office Version
  1. 2007
Platform
  1. Windows
Change this
x = lRow.top

For this
x = Range("A" & lRow).Top
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,341
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for that Dante, now I just got to work out why it has moved the buttons about 31 rows too far.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,396
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Are you sure lrow needs to be based on Col "A" ?
Is lrow returning the correct / required row number ?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,853
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Thanks for that Dante, now I just got to work out why it has moved the buttons about 31 rows too far.
Check if after the "last" row with data in column A, you have any cell below with blank spaces
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,341
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Check if after the "last" row with data in column A, you have any cell below with blank spaces
There is nothing, I even deleted a heap of rows below to make sure.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,396
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Did you read post #4 ?
You haven't got any page breaks in there have you ??
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,341
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Are you sure lrow needs to be based on Col "A" ?
Is lrow returning the correct / required row number ?
lrow returns the correct row number and there is not any page breaks.

I am not sure if this is the best way to do this or not but I don't know of any other way.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,341
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What needs to happen is when the procedure is run, the shapes need to be moved to just below the last entry in column A.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,853
Office Version
  1. 2007
Platform
  1. Windows
Change to this:

Rich (BB code):
        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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,457
Messages
5,658,875
Members
418,475
Latest member
ExcelBeginner233

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