Unable to get the Add property of the button class

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
101
Office Version
  1. 365
Platform
  1. Windows
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


VBA Code:
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
You need to disable events while your code is running because you are changing cells from a change event.
 
Upvote 0
Solution
Glad we could help.

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

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

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