Add property problem

[Stuart]

The following is adding buttons to a new sheet. It consistently fails to add
the second button, giving the error: Unable to get the Add property of the
Buttons class.

Private Sub CreateButtons()
Dim btn As Button, ws As Worksheet
Set ws = ActiveSheet
With ws
Set btn = ws.Buttons.Add(550, 60, 100, 15)
btn.Select
Selection.Characters.Text = "Add a Page"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = _
Sheets("MASTER").General_Button1_Click

Set btn = ws.Buttons.Add(550, 80, 100, 15)
btn.Select
Selection.Characters.Text = "Show Page Heights"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = _
Sheets(Sheet1).General_Button2_Click

Set btn = ws.Buttons.Add(550, 100, 100, 15)
btn.Select
Selection.Characters.Text = "Hide Page Heights"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = _
Sheets(Sheet1).General_Button3_Click

.Range("A1").Select
End With
End Sub

Any help much appreciated.

Regards.

 

[Norman Jones]

Hi Stuart,

Moving the three click macros to a standard module with the button code and
using strings to set the OnAction proprties, the following worked for me:

Sub CreateButtons()
Dim btn As Button, ws As Worksheet
Set ws = ActiveSheet
With ws
Set btn = ws.Buttons.Add(550, 60, 100, 15)
btn.Select
Selection.Characters.Text = "Add a Page"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = _
"General_Button1_Click"

Set btn = ws.Buttons.Add(550, 80, 100, 15)
btn.Select
Selection.Characters.Text = "Show Page Heights"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = _
"General_Button2_Click"

Set btn = ws.Buttons.Add(550, 100, 100, 15)
btn.Select
Selection.Characters.Text = "Hide Page Heights"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = _
"General_Button3_Click"

.Range("A1").Select
End With
End Sub

Sub General_Button1_Click()
MsgBox "Hi"
End Sub

Sub General_Button2_Click()
MsgBox "Ho"
End Sub

Sub General_Button3_Click()
MsgBox "He"
End Sub

 

[Stuart]

That does it.
Many thanks.

Regards.

Hi Stuart,

Moving the three click macros to a standard module with the button code
and using strings to set the OnAction proprties, the following worked for
me:

Sub CreateButtons()
Dim btn As Button, ws As Worksheet
Set ws = ActiveSheet
With ws
Set btn = ws.Buttons.Add(550, 60, 100, 15)
btn.Select
Selection.Characters.Text = "Add a Page"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = _
"General_Button1_Click"

Set btn = ws.Buttons.Add(550, 80, 100, 15)
btn.Select
Selection.Characters.Text = "Show Page Heights"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = _
"General_Button2_Click"

Set btn = ws.Buttons.Add(550, 100, 100, 15)
btn.Select
Selection.Characters.Text = "Hide Page Heights"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = _
"General_Button3_Click"

.Range("A1").Select
End With
End Sub

Sub General_Button1_Click()
MsgBox "Hi"
End Sub

Sub General_Button2_Click()
MsgBox "Ho"
End Sub

Sub General_Button3_Click()
MsgBox "He"
End Sub