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?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,728
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
This was covered in a previous thread of yours, which you said was resolved then ??.....what has changed ??
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

dpaton05

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

Well-known Member
Joined
Aug 14, 2018
Messages
2,019
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I changed the code Dante and I got an error saying invalid use of property. It also highlighted the .top
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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:

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 = 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

Well-known Member
Joined
Aug 14, 2018
Messages
2,019
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 14, 2018
Messages
2,019
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I forgot to add that I also get an error saying object doesn't support this property or method.
 

DanteAmor

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

ADVERTISEMENT

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:
1590545561057.png


After:
1590545602779.png


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

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
  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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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

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

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

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")
    
    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
 

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