OLEObjects runtime error

[luolovepi]

If (Pr.OLEObjects("Line" & i).Visible = False) Then
            Pr.OLEObjects("Line" & i).Visible = True
End If

Pr is a worksheet. Line & i is the button name. Is there anything wrong with these lines of code? I got a runtime error on OLEObjects.

Thanks!

Best regards,
lolo

 

[GTO]

Pr is a worksheet. Line & i is the button name. Is there anything wrong with these lines of code? I got a runtime error on OLEObjects.

Hi there,

e.g. 'Line6' is the name of a button? The below works for a line from Draw toolbar. If a button, is it from the control toolbox or the Forms toolbar?

Sub exa()
Dim Pr As Worksheet
Dim i As Long
 
i = 1
Set Pr = ActiveSheet
 
Pr.Shapes("Line " & i).OLEFormat.Object.Visible = Not Pr.Shapes("Line " & i).OLEFormat.Object.Visible
 
End Sub

 

[luolovepi]

Hi GTO, thanks for your reply!
Last time I created the button like this:

Sub initButton()
    Dim ButtonCell As Range
    Set ButtonCell = Pr.Range("N15")
    ButtonCell.Activate
    For i = 15 To (tableContentRows + 14)
        Call AddButton
        Pr.OLEObjects("Line" & i).Visible = True
        ActiveCell.Offset(1, 0).Activate
    Next i
End Sub
 
Sub AddButton()
    Dim buttonForMasterSparePart As OLEObject
    With ActiveCell
        Set buttonForMasterSparePart = ActiveSheet.OLEObjects.Add _
            (ClassType:="Forms.CommandButton.1", _
            Link:=False, DisplayAsIcon:=False, _
            Left:=.Left, _
            Top:=.Top, _
            Height:=.Height, _
            Width:=.Width)
        buttonForMasterSparePart.Object.Caption = .row
        buttonForMasterSparePart.Object.Font.Size = 6
        buttonForMasterSparePart.Object.Font.Bold = True
        buttonForMasterSparePart.Object.WordWrap = True
        buttonForMasterSparePart.Name = "Line" & .row
        buttonForMasterSparePart.Object.Font.Name = "MS Gothic"
    End With
     ' Inserts code for the button
    With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        Line = .CountOfLines
        .InsertLines Line + 1, "Sub " & buttonForMasterSparePart.Name & "_Click()"
        .InsertLines Line + 2, "sendData(" & ActiveCell.row & ")"
        .InsertLines Line + 3, "End Sub"
    End With
End Sub

I'm not so sure what kind of button it belongs to. Shouldn't it be correct?

Pr is a worksheet. Line & i is the button name. Is there anything wrong with these lines of code? I got a runtime error on OLEObjects.

Hi there,

e.g. 'Line6' is the name of a button? The below works for a line from Draw toolbar. If a button, is it from the control toolbox or the Forms toolbar?

Sub exa()
Dim Pr As Worksheet
Dim i As Long
 
i = 1
Set Pr = ActiveSheet
 
Pr.Shapes("Line " & i).OLEFormat.Object.Visible = Not Pr.Shapes("Line " & i).OLEFormat.Object.Visible
 
End Sub

 

[GTO]

Hi there,

Apologies, but it is too late and I have to logout. I hope to stop by tomorrow, but hope you get better help in the meantime. Your control is an activex button. I don't remember right off as to renaming these and referring to them while on a sheet. I was not getting the same error.

Well, again, hope you get it figured in the meantime, but I'll try and stop in tomorrow night.

Mark

 

[RoryA]

What is the value of i and is PR definitely the right worksheet?

 

[luolovepi]

I just made some changes to my other code. I don't understand what happened, the error disappeared...

Thank you Mark and Rory!