How to get the button name:LineRowNumber, where RowNumber varies for different buttons.

[luolovepi]

My worksheet contains a list of buttons at each row. The name of button is like: for example, row 15 has a button named Line15; row 16 has a button called Line16.

I have a sub to transfer the row information to another worksheet only if the data in that row is integrate.

And I want to hide the buttons of the rows whose data have been tranfered.

I try to get the button name as below, but it is wrong. How to get the button name in a for loop where row number is the increment variable?

Dim buttonName As String
buttonName = "Line" & Row
buttonName.Visible = False

my full sub code is as follows:

Private Sub SubmitAll_Click()
    Dim Row As Integer
    For Row = 15 To 36
        Call Functionality.set_GLB_rngToCopy(Row)
        If (Functionality.checkIntegrity) Then
            SendData (Row)
            Dim buttonName As String
            buttonName = "Line" & Row
            buttonName.Visible = False
        End If
    Next Row
End Sub

Best regards,
lolo

 

[Comfy]

You have defined buttonName As a string.

Add this to your code

Private Sub SubmitAll_Click()
    Dim Row As Integer
Dim wb As workbook, ws As worksheet
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
    For Row = 15 To 36
        Call Functionality.set_GLB_rngToCopy(Row)
        If (Functionality.checkIntegrity) Then
            SendData (Row)
            Dim buttonName As String
            buttonName = "Line" & Row
            ws.Shapes(buttonName).Visible = msoFalse
        End If
    Next Row
End Sub

 

[Andrew Poulsom]

This worked for me:

Me.OLEObjects(buttonName).Visible = False

 

[luolovepi]

Hi Andrew,
Thanks a lot!!

Best regards,
lolo^-^

This worked for me:

Me.OLEObjects(buttonName).Visible = False

 

[luolovepi]

Hi Comfy,
Thank you !!

Best regards,
lolo^^

You have defined buttonName As a string.

Add this to your code

Private Sub SubmitAll_Click()
    Dim Row As Integer
Dim wb As workbook, ws As worksheet
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
    For Row = 15 To 36
        Call Functionality.set_GLB_rngToCopy(Row)
        If (Functionality.checkIntegrity) Then
            SendData (Row)
            Dim buttonName As String
            buttonName = "Line" & Row
            ws.Shapes(buttonName).Visible = msoFalse
        End If
    Next Row
End Sub

 

[luolovepi]

Hi Andrew,
What does Me mean?
When I try to use this code in another place, it prompts out an error message on Me.
Why?

Best regards,
lolo

This worked for me:

Me.OLEObjects(buttonName).Visible = False

 

[Andrew Poulsom]

It refers to the container of a Class - in your case a Worksheet.

 

[luolovepi]

So can I replace Me with a sheet name?

It refers to the container of a Class - in your case a Worksheet.

 

[Andrew Poulsom]

Yes.