Create button code stops working

I have a template with two sheets (MASTER.. codename Sheet1, and
Summary.....codename Sheet3). There are no general modules, and the
ThisWorkbook module is empty. The only code is in Sheet1.

On the Master sheet is a CommandButton from the Controls toolbox, which
allows a new sheet to be added and formatted according to ranges in the
Master. It also creates 3 new buttons (from the Forms toolbar) on the new
sheet, and assigns separate subs in the Master sheet code module to each
button......and it's the assign statement that is failing.

Here is the relevent Sheet1 code:

Private Sub Cb_NewSheet_Click()
Dim strSheetName As String, strNoOfPages As Integer
Dim i As Integer, pasteRng As Range, ws As Worksheet
Application.ScreenUpdating = False
strSheetName = Application.InputBox _
("Please enter a name for the new sheet", Type:=2)
If strSheetName = "" Then
MsgBox ("You must enter a name for the sheet")
Exit Sub
End If
strNoOfPages = Application.InputBox _
("How many pages do you need?", Type:=1)
If strNoOfPages = False Then
MsgBox ("You must specify how many pages")
Exit Sub
End If
Worksheets.Add Before:=Sheets("Summary")
ActiveSheet.Name = strSheetName

' code then follows to format the new sheet, create the
' required number of pages, and then call the button sub:

Call Create3Buttons
'which leads to:

Private Sub Create3Buttons()
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 = ThisWorkbook.Name & _
"!Sheet1.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 = ThisWorkbook.Name & _
"!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 = ThisWorkbook.Name & _
"!Sheet1.General_Button3_click"

.Range("A1").Select

End With
End Sub

The btn.OnAction statement fails with the message
"Unable to set the OnAction property of the Button class".

The same statement has worked in other books. Why might it now have stopped,
please?

Regards.



' and the the button code:

Not tested but try this

Private Sub Cb_NewSheet_Click()
Dim strSheetName As String, strNoOfPages As Integer
Dim i As Integer, pasteRng As Range, ws As Worksheet
Application.ScreenUpdating = False
strSheetName = Application.InputBox _
("Please enter a name for the new sheet", Type:=2)
If strSheetName = "" Then
MsgBox ("You must enter a name for the sheet")
Exit Sub
End If
strNoOfPages = Application.InputBox _
("How many pages do you need?", Type:=1)
If strNoOfPages = False Then
MsgBox ("You must specify how many pages")
Exit Sub
End If
set ws = Worksheets.Add(Before:=Sheets("Summary"))
ws.Name = strSheetName

' code then follows to format the new sheet, create the
' required number of pages, and then call the button sub:

Call Create3Buttons(ws)
'which leads to:

Private Sub Create3Buttons(ws As Worksheet)
Dim btn As Button

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 = ThisWorkbook.Name & _
"!Sheet1.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 = ThisWorkbook.Name & _
"!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 = ThisWorkbook.Name & _
"!Sheet1.General_Button3_click"

.Range("A1").Select

End With
End Sub


--
HTH

Bob Phillips

"Stuart" <sgbooth@totalise.co.uk> wrote in message
news:OimnRfZTFHA.2128@TK2MSFTNGP14.phx.gbl...
> I have a template with two sheets (MASTER.. codename Sheet1, and
> Summary.....codename Sheet3). There are no general modules, and the
> ThisWorkbook module is empty. The only code is in Sheet1.
>
> On the Master sheet is a CommandButton from the Controls toolbox, which
> allows a new sheet to be added and formatted according to ranges in the
> Master. It also creates 3 new buttons (from the Forms toolbar) on the new
> sheet, and assigns separate subs in the Master sheet code module to each
> button......and it's the assign statement that is failing.
>
> Here is the relevent Sheet1 code:
>
> Private Sub Cb_NewSheet_Click()
> Dim strSheetName As String, strNoOfPages As Integer
> Dim i As Integer, pasteRng As Range, ws As Worksheet
> Application.ScreenUpdating = False
> strSheetName = Application.InputBox _
> ("Please enter a name for the new sheet", Type:=2)
> If strSheetName = "" Then
> MsgBox ("You must enter a name for the sheet")
> Exit Sub
> End If
> strNoOfPages = Application.InputBox _
> ("How many pages do you need?", Type:=1)
> If strNoOfPages = False Then
> MsgBox ("You must specify how many pages")
> Exit Sub
> End If
> Worksheets.Add Before:=Sheets("Summary")
> ActiveSheet.Name = strSheetName
>
> ' code then follows to format the new sheet, create the
> ' required number of pages, and then call the button sub:
>
> Call Create3Buttons
> 'which leads to:
>
> Private Sub Create3Buttons()
> 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 = ThisWorkbook.Name & _
> "!Sheet1.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 = ThisWorkbook.Name & _
> "!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 = ThisWorkbook.Name & _
> "!Sheet1.General_Button3_click"
>
> .Range("A1").Select
>
> End With
> End Sub
>
> The btn.OnAction statement fails with the message
> "Unable to set the OnAction property of the Button class".
>
> The same statement has worked in other books. Why might it now have
stopped,
> please?
>
> Regards.
>
>
>
> ' and the the button code:
>
>
>
>
>
>
>
>
>
>
>

Many thanks....but !

Changed the Set statement etc, and also passed ws to
Create3Buttons etc. Initially all was fine. I was about to post a 'thank
you' and a 'what was the significance of your changes' question, when it
started playing up again.

I created a couple of new sheets in a copy of the template
and then saved the template with a new name. Opened the new xlt, added new
sheets, and again saved it with a new name. I repeated this a couple of
times, and then back to the same error mesage again.

This has been bugging me all day, and it's supposed to be a "blessed"
holiday weekend!

Regards.

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:%23cepJ%23ZTFHA.544@TK2MSFTNGP15.phx.gbl...
> Not tested but try this
>
> Private Sub Cb_NewSheet_Click()
> Dim strSheetName As String, strNoOfPages As Integer
> Dim i As Integer, pasteRng As Range, ws As Worksheet
> Application.ScreenUpdating = False
> strSheetName = Application.InputBox _
> ("Please enter a name for the new sheet", Type:=2)
> If strSheetName = "" Then
> MsgBox ("You must enter a name for the sheet")
> Exit Sub
> End If
> strNoOfPages = Application.InputBox _
> ("How many pages do you need?", Type:=1)
> If strNoOfPages = False Then
> MsgBox ("You must specify how many pages")
> Exit Sub
> End If
> set ws = Worksheets.Add(Before:=Sheets("Summary"))
> ws.Name = strSheetName
>
> ' code then follows to format the new sheet, create the
> ' required number of pages, and then call the button sub:
>
> Call Create3Buttons(ws)
> 'which leads to:
>
> Private Sub Create3Buttons(ws As Worksheet)
> Dim btn As Button
>
> 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 = ThisWorkbook.Name & _
> "!Sheet1.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 = ThisWorkbook.Name & _
> "!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 = ThisWorkbook.Name & _
> "!Sheet1.General_Button3_click"
>
> .Range("A1").Select
>
> End With
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "Stuart" <sgbooth@totalise.co.uk> wrote in message
> news:OimnRfZTFHA.2128@TK2MSFTNGP14.phx.gbl...
>> I have a template with two sheets (MASTER.. codename Sheet1, and
>> Summary.....codename Sheet3). There are no general modules, and the
>> ThisWorkbook module is empty. The only code is in Sheet1.
>>
>> On the Master sheet is a CommandButton from the Controls toolbox, which
>> allows a new sheet to be added and formatted according to ranges in the
>> Master. It also creates 3 new buttons (from the Forms toolbar) on the new
>> sheet, and assigns separate subs in the Master sheet code module to each
>> button......and it's the assign statement that is failing.
>>
>> Here is the relevent Sheet1 code:
>>
>> Private Sub Cb_NewSheet_Click()
>> Dim strSheetName As String, strNoOfPages As Integer
>> Dim i As Integer, pasteRng As Range, ws As Worksheet
>> Application.ScreenUpdating = False
>> strSheetName = Application.InputBox _
>> ("Please enter a name for the new sheet", Type:=2)
>> If strSheetName = "" Then
>> MsgBox ("You must enter a name for the sheet")
>> Exit Sub
>> End If
>> strNoOfPages = Application.InputBox _
>> ("How many pages do you need?", Type:=1)
>> If strNoOfPages = False Then
>> MsgBox ("You must specify how many pages")
>> Exit Sub
>> End If
>> Worksheets.Add Before:=Sheets("Summary")
>> ActiveSheet.Name = strSheetName
>>
>> ' code then follows to format the new sheet, create the
>> ' required number of pages, and then call the button sub:
>>
>> Call Create3Buttons
>> 'which leads to:
>>
>> Private Sub Create3Buttons()
>> 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 = ThisWorkbook.Name & _
>> "!Sheet1.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 = ThisWorkbook.Name & _
>> "!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 = ThisWorkbook.Name & _
>> "!Sheet1.General_Button3_click"
>>
>> .Range("A1").Select
>>
>> End With
>> End Sub
>>
>> The btn.OnAction statement fails with the message
>> "Unable to set the OnAction property of the Button class".
>>
>> The same statement has worked in other books. Why might it now have
> stopped,
>> please?
>>
>> Regards.
>>
>>
>>
>> ' and the the button code:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>

Private Sub Cb_NewSheet_Click()
Dim strSheetName As String, strNoOfPages As Integer
Dim i As Integer, pasteRng As Range, ws As Worksheet
Application.ScreenUpdating = False
strSheetName = Application.InputBox _
("Please enter a name for the new sheet", Type:=2)
If strSheetName = "" Then
MsgBox ("You must enter a name for the sheet")
Exit Sub
End If
strNoOfPages = Application.InputBox _
("How many pages do you need?", Type:=1)
If strNoOfPages = False Then
MsgBox ("You must specify how many pages")
Exit Sub
End If
Worksheets.Add Before:=Sheets("Summary")
ActiveSheet.Name = strSheetName

' code then follows to format the new sheet, create the
' required number of pages, and then call the button sub:

Call Create3Buttons
End Sub

Private Sub Create3Buttons()
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 = _
"Sheet1.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 = _
"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 = _
"Sheet1.General_Button3_click"

.Range("A1").Select

End With
End Sub

Public Sub General_Button1_click()
MsgBox "button1"
End Sub

Public Sub General_Button2_click()
MsgBox "button2"
End Sub
Public Sub General_Button3_click()
MsgBox "button3"
End Sub


worked fine for me. Did you declare your General_Button#_Click macros as
public?

--
Regards,
Tom Ogilvy

"Stuart" <sgbooth@totalise.co.uk> wrote in message
news:%23gvIsTaTFHA.2560@TK2MSFTNGP09.phx.gbl...
> Many thanks....but !
>
> Changed the Set statement etc, and also passed ws to
> Create3Buttons etc. Initially all was fine. I was about to post a 'thank
> you' and a 'what was the significance of your changes' question, when it
> started playing up again.
>
> I created a couple of new sheets in a copy of the template
> and then saved the template with a new name. Opened the new xlt, added new
> sheets, and again saved it with a new name. I repeated this a couple of
> times, and then back to the same error mesage again.
>
> This has been bugging me all day, and it's supposed to be a "blessed"
> holiday weekend!
>
> Regards.
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:%23cepJ%23ZTFHA.544@TK2MSFTNGP15.phx.gbl...
> > Not tested but try this
> >
> > Private Sub Cb_NewSheet_Click()
> > Dim strSheetName As String, strNoOfPages As Integer
> > Dim i As Integer, pasteRng As Range, ws As Worksheet
> > Application.ScreenUpdating = False
> > strSheetName = Application.InputBox _
> > ("Please enter a name for the new sheet", Type:=2)
> > If strSheetName = "" Then
> > MsgBox ("You must enter a name for the sheet")
> > Exit Sub
> > End If
> > strNoOfPages = Application.InputBox _
> > ("How many pages do you need?", Type:=1)
> > If strNoOfPages = False Then
> > MsgBox ("You must specify how many pages")
> > Exit Sub
> > End If
> > set ws = Worksheets.Add(Before:=Sheets("Summary"))
> > ws.Name = strSheetName
> >
> > ' code then follows to format the new sheet, create the
> > ' required number of pages, and then call the button sub:
> >
> > Call Create3Buttons(ws)
> > 'which leads to:
> >
> > Private Sub Create3Buttons(ws As Worksheet)
> > Dim btn As Button
> >
> > 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 = ThisWorkbook.Name & _
> > "!Sheet1.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 = ThisWorkbook.Name & _
> > "!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 = ThisWorkbook.Name & _
> > "!Sheet1.General_Button3_click"
> >
> > .Range("A1").Select
> >
> > End With
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Stuart" <sgbooth@totalise.co.uk> wrote in message
> > news:OimnRfZTFHA.2128@TK2MSFTNGP14.phx.gbl...
> >> I have a template with two sheets (MASTER.. codename Sheet1, and
> >> Summary.....codename Sheet3). There are no general modules, and the
> >> ThisWorkbook module is empty. The only code is in Sheet1.
> >>
> >> On the Master sheet is a CommandButton from the Controls toolbox, which
> >> allows a new sheet to be added and formatted according to ranges in the
> >> Master. It also creates 3 new buttons (from the Forms toolbar) on the
new
> >> sheet, and assigns separate subs in the Master sheet code module to
each
> >> button......and it's the assign statement that is failing.
> >>
> >> Here is the relevent Sheet1 code:
> >>
> >> Private Sub Cb_NewSheet_Click()
> >> Dim strSheetName As String, strNoOfPages As Integer
> >> Dim i As Integer, pasteRng As Range, ws As Worksheet
> >> Application.ScreenUpdating = False
> >> strSheetName = Application.InputBox _
> >> ("Please enter a name for the new sheet", Type:=2)
> >> If strSheetName = "" Then
> >> MsgBox ("You must enter a name for the sheet")
> >> Exit Sub
> >> End If
> >> strNoOfPages = Application.InputBox _
> >> ("How many pages do you need?", Type:=1)
> >> If strNoOfPages = False Then
> >> MsgBox ("You must specify how many pages")
> >> Exit Sub
> >> End If
> >> Worksheets.Add Before:=Sheets("Summary")
> >> ActiveSheet.Name = strSheetName
> >>
> >> ' code then follows to format the new sheet, create the
> >> ' required number of pages, and then call the button sub:
> >>
> >> Call Create3Buttons
> >> 'which leads to:
> >>
> >> Private Sub Create3Buttons()
> >> 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 = ThisWorkbook.Name & _
> >> "!Sheet1.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 = ThisWorkbook.Name & _
> >> "!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 = ThisWorkbook.Name & _
> >> "!Sheet1.General_Button3_click"
> >>
> >> .Range("A1").Select
> >>
> >> End With
> >> End Sub
> >>
> >> The btn.OnAction statement fails with the message
> >> "Unable to set the OnAction property of the Button class".
> >>
> >> The same statement has worked in other books. Why might it now have
> > stopped,
> >> please?
> >>
> >> Regards.
> >>
> >>
> >>
> >> ' and the the button code:
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

Many thanks. I'll give it a try, and report back.

>Did you declare your General_Button#_Click macros as public?

Yes. In the Sheet1 module.

Regards.

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OHJxkVcTFHA.1384@TK2MSFTNGP09.phx.gbl...
> Private Sub Cb_NewSheet_Click()
> Dim strSheetName As String, strNoOfPages As Integer
> Dim i As Integer, pasteRng As Range, ws As Worksheet
> Application.ScreenUpdating = False
> strSheetName = Application.InputBox _
> ("Please enter a name for the new sheet", Type:=2)
> If strSheetName = "" Then
> MsgBox ("You must enter a name for the sheet")
> Exit Sub
> End If
> strNoOfPages = Application.InputBox _
> ("How many pages do you need?", Type:=1)
> If strNoOfPages = False Then
> MsgBox ("You must specify how many pages")
> Exit Sub
> End If
> Worksheets.Add Before:=Sheets("Summary")
> ActiveSheet.Name = strSheetName
>
> ' code then follows to format the new sheet, create the
> ' required number of pages, and then call the button sub:
>
> Call Create3Buttons
> End Sub
>
> Private Sub Create3Buttons()
> 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 = _
> "Sheet1.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 = _
> "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 = _
> "Sheet1.General_Button3_click"
>
> .Range("A1").Select
>
> End With
> End Sub
>
> Public Sub General_Button1_click()
> MsgBox "button1"
> End Sub
>
> Public Sub General_Button2_click()
> MsgBox "button2"
> End Sub
> Public Sub General_Button3_click()
> MsgBox "button3"
> End Sub
>
>
> worked fine for me. Did you declare your General_Button#_Click macros as
> public?
>
> --
> Regards,
> Tom Ogilvy
>
> "Stuart" <sgbooth@totalise.co.uk> wrote in message
> news:%23gvIsTaTFHA.2560@TK2MSFTNGP09.phx.gbl...
>> Many thanks....but !
>>
>> Changed the Set statement etc, and also passed ws to
>> Create3Buttons etc. Initially all was fine. I was about to post a 'thank
>> you' and a 'what was the significance of your changes' question, when it
>> started playing up again.
>>
>> I created a couple of new sheets in a copy of the template
>> and then saved the template with a new name. Opened the new xlt, added
>> new
>> sheets, and again saved it with a new name. I repeated this a couple of
>> times, and then back to the same error mesage again.
>>
>> This has been bugging me all day, and it's supposed to be a "blessed"
>> holiday weekend!
>>
>> Regards.
>>
>> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
>> news:%23cepJ%23ZTFHA.544@TK2MSFTNGP15.phx.gbl...
>> > Not tested but try this
>> >
>> > Private Sub Cb_NewSheet_Click()
>> > Dim strSheetName As String, strNoOfPages As Integer
>> > Dim i As Integer, pasteRng As Range, ws As Worksheet
>> > Application.ScreenUpdating = False
>> > strSheetName = Application.InputBox _
>> > ("Please enter a name for the new sheet", Type:=2)
>> > If strSheetName = "" Then
>> > MsgBox ("You must enter a name for the sheet")
>> > Exit Sub
>> > End If
>> > strNoOfPages = Application.InputBox _
>> > ("How many pages do you need?", Type:=1)
>> > If strNoOfPages = False Then
>> > MsgBox ("You must specify how many pages")
>> > Exit Sub
>> > End If
>> > set ws = Worksheets.Add(Before:=Sheets("Summary"))
>> > ws.Name = strSheetName
>> >
>> > ' code then follows to format the new sheet, create the
>> > ' required number of pages, and then call the button sub:
>> >
>> > Call Create3Buttons(ws)
>> > 'which leads to:
>> >
>> > Private Sub Create3Buttons(ws As Worksheet)
>> > Dim btn As Button
>> >
>> > 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 = ThisWorkbook.Name & _
>> > "!Sheet1.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 = ThisWorkbook.Name & _
>> > "!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 = ThisWorkbook.Name & _
>> > "!Sheet1.General_Button3_click"
>> >
>> > .Range("A1").Select
>> >
>> > End With
>> > End Sub
>> >
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Stuart" <sgbooth@totalise.co.uk> wrote in message
>> > news:OimnRfZTFHA.2128@TK2MSFTNGP14.phx.gbl...
>> >> I have a template with two sheets (MASTER.. codename Sheet1, and
>> >> Summary.....codename Sheet3). There are no general modules, and the
>> >> ThisWorkbook module is empty. The only code is in Sheet1.
>> >>
>> >> On the Master sheet is a CommandButton from the Controls toolbox,
>> >> which
>> >> allows a new sheet to be added and formatted according to ranges in
>> >> the
>> >> Master. It also creates 3 new buttons (from the Forms toolbar) on the
> new
>> >> sheet, and assigns separate subs in the Master sheet code module to
> each
>> >> button......and it's the assign statement that is failing.
>> >>
>> >> Here is the relevent Sheet1 code:
>> >>
>> >> Private Sub Cb_NewSheet_Click()
>> >> Dim strSheetName As String, strNoOfPages As Integer
>> >> Dim i As Integer, pasteRng As Range, ws As Worksheet
>> >> Application.ScreenUpdating = False
>> >> strSheetName = Application.InputBox _
>> >> ("Please enter a name for the new sheet", Type:=2)
>> >> If strSheetName = "" Then
>> >> MsgBox ("You must enter a name for the sheet")
>> >> Exit Sub
>> >> End If
>> >> strNoOfPages = Application.InputBox _
>> >> ("How many pages do you need?", Type:=1)
>> >> If strNoOfPages = False Then
>> >> MsgBox ("You must specify how many pages")
>> >> Exit Sub
>> >> End If
>> >> Worksheets.Add Before:=Sheets("Summary")
>> >> ActiveSheet.Name = strSheetName
>> >>
>> >> ' code then follows to format the new sheet, create the
>> >> ' required number of pages, and then call the button sub:
>> >>
>> >> Call Create3Buttons
>> >> 'which leads to:
>> >>
>> >> Private Sub Create3Buttons()
>> >> 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 = ThisWorkbook.Name & _
>> >> "!Sheet1.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 = ThisWorkbook.Name & _
>> >> "!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 = ThisWorkbook.Name & _
>> >> "!Sheet1.General_Button3_click"
>> >>
>> >> .Range("A1").Select
>> >>
>> >> End With
>> >> End Sub
>> >>
>> >> The btn.OnAction statement fails with the message
>> >> "Unable to set the OnAction property of the Button class".
>> >>
>> >> The same statement has worked in other books. Why might it now have
>> > stopped,
>> >> please?
>> >>
>> >> Regards.
>> >>
>> >>
>> >>
>> >> ' and the the button code:
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

I've given your code a reasonably robust test, and it seems to hold up, for
me. I don't however, understand why the changes made the difference.

Nevertheless, many thanks.

Regards.

"Stuart" <sgbooth@totalise.co.uk> wrote in message
news:usgxKmcTFHA.3544@TK2MSFTNGP10.phx.gbl...
> Many thanks. I'll give it a try, and report back.
>
> >Did you declare your General_Button#_Click macros as public?
>
> Yes. In the Sheet1 module.
>
> Regards.
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:OHJxkVcTFHA.1384@TK2MSFTNGP09.phx.gbl...
>> Private Sub Cb_NewSheet_Click()
>> Dim strSheetName As String, strNoOfPages As Integer
>> Dim i As Integer, pasteRng As Range, ws As Worksheet
>> Application.ScreenUpdating = False
>> strSheetName = Application.InputBox _
>> ("Please enter a name for the new sheet", Type:=2)
>> If strSheetName = "" Then
>> MsgBox ("You must enter a name for the sheet")
>> Exit Sub
>> End If
>> strNoOfPages = Application.InputBox _
>> ("How many pages do you need?", Type:=1)
>> If strNoOfPages = False Then
>> MsgBox ("You must specify how many pages")
>> Exit Sub
>> End If
>> Worksheets.Add Before:=Sheets("Summary")
>> ActiveSheet.Name = strSheetName
>>
>> ' code then follows to format the new sheet, create the
>> ' required number of pages, and then call the button sub:
>>
>> Call Create3Buttons
>> End Sub
>>
>> Private Sub Create3Buttons()
>> 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 = _
>> "Sheet1.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 = _
>> "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 = _
>> "Sheet1.General_Button3_click"
>>
>> .Range("A1").Select
>>
>> End With
>> End Sub
>>
>> Public Sub General_Button1_click()
>> MsgBox "button1"
>> End Sub
>>
>> Public Sub General_Button2_click()
>> MsgBox "button2"
>> End Sub
>> Public Sub General_Button3_click()
>> MsgBox "button3"
>> End Sub
>>
>>
>> worked fine for me. Did you declare your General_Button#_Click macros as
>> public?
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "Stuart" <sgbooth@totalise.co.uk> wrote in message
>> news:%23gvIsTaTFHA.2560@TK2MSFTNGP09.phx.gbl...
>>> Many thanks....but !
>>>
>>> Changed the Set statement etc, and also passed ws to
>>> Create3Buttons etc. Initially all was fine. I was about to post a 'thank
>>> you' and a 'what was the significance of your changes' question, when it
>>> started playing up again.
>>>
>>> I created a couple of new sheets in a copy of the template
>>> and then saved the template with a new name. Opened the new xlt, added
>>> new
>>> sheets, and again saved it with a new name. I repeated this a couple of
>>> times, and then back to the same error mesage again.
>>>
>>> This has been bugging me all day, and it's supposed to be a "blessed"
>>> holiday weekend!
>>>
>>> Regards.
>>>
>>> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
>>> news:%23cepJ%23ZTFHA.544@TK2MSFTNGP15.phx.gbl...
>>> > Not tested but try this
>>> >
>>> > Private Sub Cb_NewSheet_Click()
>>> > Dim strSheetName As String, strNoOfPages As Integer
>>> > Dim i As Integer, pasteRng As Range, ws As Worksheet
>>> > Application.ScreenUpdating = False
>>> > strSheetName = Application.InputBox _
>>> > ("Please enter a name for the new sheet", Type:=2)
>>> > If strSheetName = "" Then
>>> > MsgBox ("You must enter a name for the sheet")
>>> > Exit Sub
>>> > End If
>>> > strNoOfPages = Application.InputBox _
>>> > ("How many pages do you need?", Type:=1)
>>> > If strNoOfPages = False Then
>>> > MsgBox ("You must specify how many pages")
>>> > Exit Sub
>>> > End If
>>> > set ws = Worksheets.Add(Before:=Sheets("Summary"))
>>> > ws.Name = strSheetName
>>> >
>>> > ' code then follows to format the new sheet, create the
>>> > ' required number of pages, and then call the button sub:
>>> >
>>> > Call Create3Buttons(ws)
>>> > 'which leads to:
>>> >
>>> > Private Sub Create3Buttons(ws As Worksheet)
>>> > Dim btn As Button
>>> >
>>> > 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 = ThisWorkbook.Name & _
>>> > "!Sheet1.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 = ThisWorkbook.Name & _
>>> > "!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 = ThisWorkbook.Name & _
>>> > "!Sheet1.General_Button3_click"
>>> >
>>> > .Range("A1").Select
>>> >
>>> > End With
>>> > End Sub
>>> >
>>> >
>>> > --
>>> > HTH
>>> >
>>> > Bob Phillips
>>> >
>>> > "Stuart" <sgbooth@totalise.co.uk> wrote in message
>>> > news:OimnRfZTFHA.2128@TK2MSFTNGP14.phx.gbl...
>>> >> I have a template with two sheets (MASTER.. codename Sheet1, and
>>> >> Summary.....codename Sheet3). There are no general modules, and the
>>> >> ThisWorkbook module is empty. The only code is in Sheet1.
>>> >>
>>> >> On the Master sheet is a CommandButton from the Controls toolbox,
>>> >> which
>>> >> allows a new sheet to be added and formatted according to ranges in
>>> >> the
>>> >> Master. It also creates 3 new buttons (from the Forms toolbar) on the
>> new
>>> >> sheet, and assigns separate subs in the Master sheet code module to
>> each
>>> >> button......and it's the assign statement that is failing.
>>> >>
>>> >> Here is the relevent Sheet1 code:
>>> >>
>>> >> Private Sub Cb_NewSheet_Click()
>>> >> Dim strSheetName As String, strNoOfPages As Integer
>>> >> Dim i As Integer, pasteRng As Range, ws As Worksheet
>>> >> Application.ScreenUpdating = False
>>> >> strSheetName = Application.InputBox _
>>> >> ("Please enter a name for the new sheet", Type:=2)
>>> >> If strSheetName = "" Then
>>> >> MsgBox ("You must enter a name for the sheet")
>>> >> Exit Sub
>>> >> End If
>>> >> strNoOfPages = Application.InputBox _
>>> >> ("How many pages do you need?", Type:=1)
>>> >> If strNoOfPages = False Then
>>> >> MsgBox ("You must specify how many pages")
>>> >> Exit Sub
>>> >> End If
>>> >> Worksheets.Add Before:=Sheets("Summary")
>>> >> ActiveSheet.Name = strSheetName
>>> >>
>>> >> ' code then follows to format the new sheet, create the
>>> >> ' required number of pages, and then call the button sub:
>>> >>
>>> >> Call Create3Buttons
>>> >> 'which leads to:
>>> >>
>>> >> Private Sub Create3Buttons()
>>> >> 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 = ThisWorkbook.Name & _
>>> >> "!Sheet1.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 = ThisWorkbook.Name & _
>>> >> "!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 = ThisWorkbook.Name & _
>>> >> "!Sheet1.General_Button3_click"
>>> >>
>>> >> .Range("A1").Select
>>> >>
>>> >> End With
>>> >> End Sub
>>> >>
>>> >> The btn.OnAction statement fails with the message
>>> >> "Unable to set the OnAction property of the Button
>>> >> class".
>>> >>
>>> >> The same statement has worked in other books. Why might it now have
>>> > stopped,
>>> >> please?
>>> >>
>>> >> Regards.
>>> >>
>>> >>
>>> >>
>>> >> ' and the the button code:
>>> >>
>>> >>
>>> >>
>>> >>
>>> >>
>>> >>
>>> >>
>>> >>
>>> >>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>