Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Add property problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 154
Default 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.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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.



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 154
Default 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.





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem using filter property ub Excel Worksheet Functions 8 October 17th 07 09:32 PM
Object property/method problem Ayo Excel Discussion (Misc queries) 6 August 30th 07 10:18 PM
Problem with using the ActiveWindow property - sometimes Ragnar Midtskogen Excel Programming 1 November 24th 04 02:37 PM
problem with resize property Michelle Excel Programming 4 February 23rd 04 02:11 PM
Problem with PrintArea property Ugleeduck Excel Programming 1 December 5th 03 05:26 PM


All times are GMT +1. The time now is 05:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement