ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Add property problem (https://www.excelbanter.com/excel-programming/342166-add-property-problem.html)

Stuart[_21_]
Add property problem
 
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
Add property problem
 
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


---
Regards,
Norman



"Stuart" wrote in message
...
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.

Stuart[_21_]
Add property problem
 
That does it.
Many thanks.

Regards.

"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"Stuart" wrote in message
...
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.


All times are GMT +1. The time now is 09:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com