Unable to get the Add property of the button class

[szita2000]

Hi All.

The below code was working fine until I added in the Worksheet Protection.
What I found weird that stepping through the code the worksheet is definately unprotected.

The Thetarget value is a little "switch" that I put below the added button so if that is 1 the macro will not get triggered again thus we not addung buttons on top of each other.
When the button is pressed, the macro called will delete this "switch"

The code below throws an "Unable to get the Add property of the button class" error on the Set AddButton... line
Mypwd is declared in a module as a public const (it gets used as well, so that declaration should be O.K.)

What am I doing wrong?

Edit:Grammar

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AddButton As Object, rng As Range

Sheet23.Unprotect myPwd

    If Not Intersect(Target, Range("C3:C50")) Is Nothing Then
        If Target.Count > 1 Then
            Exit Sub
        Else
       
           
           If Target.Value <> 0 Then
                Set Thetarget = Cells(Target.Row, 10)
                    If Thetarget.Value <> 1 Then
                   
                    Thetarget.Value = 1
                   
                    Dim AddButton As Object, rng As Range

                    Set AddButton = Sheet23.Buttons.Add(Top:=Thetarget.Top, Left:=Thetarget.Left, Height:=Thetarget.Height * 2, Width:=Thetarget.Width)
                        With AddButton
                             .Caption = "test"
                             .OnAction = "ArchiveThisLine"
                         End With
                    Else
   
                        Exit Sub
   
                    End If
            End If
       
        'Adding in the formula for the grey arrows
        Cells(Thetarget.Row, 5).FormulaR1C1 = "=IF(R[1]C="""",""ð"",""ü"")"
            Cells(Thetarget.Row, 5).Select
            Selection.AutoFill Destination:=Range(Cells(Thetarget.Row, 5), Cells(Thetarget.Row, 9)), Type:=xlFillValues
           
       
        Set rng = Range(Cells(Thetarget.Row, 5), Cells(Thetarget.Row, 9))
       
            With rng.Font
                .Name = "Wingdings"
                .Size = 48
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
                .ThemeFont = xlThemeFontNone
            End With
       
            With rng
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = True
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            With rng.Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.349986266670736
            End With
       
       
        End If
    End If
    Sheet23.Protect myPwd

End Sub

 

[RoryA]

You need to disable events while your code is running because you are changing cells from a change event.

 

[szita2000]

You need to disable events while your code is running because you are changing cells from a change event.

Oooooooh.... (facepalm)
And it works! Thanks again Rory!

 

[RoryA]

Glad we could help.

I just noticed you have an Exit Sub in there - make sure you re-enable events in that situation too.

 

[szita2000]

Glad we could help.

I just noticed you have an Exit Sub in there - make sure you re-enable events in that situation too.

Thanks. In the meantime I found it as well.