Adapt code to loop through sheets

Hi, I'm getting too old for this .... keep forgetting even the simplest of
things (g).

I'm trying to adapt the following simple routine, which adds buttons to a
sheet.

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 = _
"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

Originally, the user was given a workbook containing a single preformatted
Master sheet. This code sequence was in the sheet module. There were buttons
on the Master sheet which allowed the user to create a new sheet, then the
above code ran, and created the above buttons on the new sheet.

My users love it, and have sent me loads of old workbooks. They want buttons
on them too !!!

I just can't find a way to adapt this to run on those old workbooks. I can
easily copy the Master sheet across.

Can someone help, please?

Regards.

It would be interesting if you had explained what is not working. Anyway,
here is a guess:

Private Sub CreateButtons()
Dim s as String
Dim btn As Button, ws As Worksheet
Set ws = ActiveSheet
s = ws.codeName
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 = _
s & ".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 = _
s & ".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 = _
s & ".General_Button3_click"

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

But that doesn't get those onaction routines into the sheet module.

--
Regards,
Tom Ogilvy


"Stuart" <sgbooth@totalise.co.uk> wrote in message
news:%23nDkkWqyFHA.2552@TK2MSFTNGP10.phx.gbl...
> Hi, I'm getting too old for this .... keep forgetting even the simplest of
> things (g).
>
> I'm trying to adapt the following simple routine, which adds buttons to a
> sheet.
>
> 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 = _
> "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
>
> Originally, the user was given a workbook containing a single preformatted
> Master sheet. This code sequence was in the sheet module. There were
buttons
> on the Master sheet which allowed the user to create a new sheet, then the
> above code ran, and created the above buttons on the new sheet.
>
> My users love it, and have sent me loads of old workbooks. They want
buttons
> on them too !!!
>
> I just can't find a way to adapt this to run on those old workbooks. I can
> easily copy the Master sheet across.
>
> Can someone help, please?
>
> Regards.
>
>

Apologies, guess I've forgotten how to post, as well.

I was trying to run the code on the one of these old workbooks. So I tried:

Private Sub CreateButtonsInEverySheet()
Dim btn As Button, ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets()
With ws
If Not (.Name = "MASTER" Or .Name = "Summary" _
Or .Name = "SUMMARY") Then
Set ws = ActiveSheet
With ws
Set btn = ws.Buttons.Add(550, 60, 100, 15)
btn.Select
etc.

Sometimes a 1004 error, other times no error at all. In all
cases, at best just one sheet had the buttons added.

Re OnAction: had planned to copy the Master sheet and
code behind to every workbook. That way each existing
sheet will have the buttons, and new sheets with buttons
can also be generated....all code being in the Master
sheet module. Ok?

Regards and thanks.

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uopn6bqyFHA.4032@TK2MSFTNGP15.phx.gbl...
> It would be interesting if you had explained what is not working. Anyway,
> here is a guess:
>
> Private Sub CreateButtons()
> Dim s as String
> Dim btn As Button, ws As Worksheet
> Set ws = ActiveSheet
> s = ws.codeName
> 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 = _
> s & ".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 = _
> s & ".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 = _
> s & ".General_Button3_click"
>
> .Range("A1").Select
> End With
> End Sub
>
> But that doesn't get those onaction routines into the sheet module.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Stuart" <sgbooth@totalise.co.uk> wrote in message
> news:%23nDkkWqyFHA.2552@TK2MSFTNGP10.phx.gbl...
>> Hi, I'm getting too old for this .... keep forgetting even the simplest
>> of
>> things (g).
>>
>> I'm trying to adapt the following simple routine, which adds buttons to a
>> sheet.
>>
>> 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 = _
>> "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
>>
>> Originally, the user was given a workbook containing a single
>> preformatted
>> Master sheet. This code sequence was in the sheet module. There were
> buttons
>> on the Master sheet which allowed the user to create a new sheet, then
>> the
>> above code ran, and created the above buttons on the new sheet.
>>
>> My users love it, and have sent me loads of old workbooks. They want
> buttons
>> on them too !!!
>>
>> I just can't find a way to adapt this to run on those old workbooks. I
>> can
>> easily copy the Master sheet across.
>>
>> Can someone help, please?
>>
>> Regards.
>>
>>
>
>

If the worksheets aren't protected, then I think your code should work.

--
Regards,
Tom Ogilvy

"Stuart" <sgbooth@totalise.co.uk> wrote in message
news:OyWY2lqyFHA.2848@TK2MSFTNGP15.phx.gbl...
> Apologies, guess I've forgotten how to post, as well.
>
> I was trying to run the code on the one of these old workbooks. So I
tried:
>
> Private Sub CreateButtonsInEverySheet()
> Dim btn As Button, ws As Worksheet
>
> For Each ws In ActiveWorkbook.Worksheets()
> With ws
> If Not (.Name = "MASTER" Or .Name = "Summary" _
> Or .Name = "SUMMARY") Then
> Set ws = ActiveSheet
> With ws
> Set btn = ws.Buttons.Add(550, 60, 100, 15)
> btn.Select
> etc.
>
> Sometimes a 1004 error, other times no error at all. In all
> cases, at best just one sheet had the buttons added.
>
> Re OnAction: had planned to copy the Master sheet and
> code behind to every workbook. That way each existing
> sheet will have the buttons, and new sheets with buttons
> can also be generated....all code being in the Master
> sheet module. Ok?
>
> Regards and thanks.
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:uopn6bqyFHA.4032@TK2MSFTNGP15.phx.gbl...
> > It would be interesting if you had explained what is not working.
Anyway,
> > here is a guess:
> >
> > Private Sub CreateButtons()
> > Dim s as String
> > Dim btn As Button, ws As Worksheet
> > Set ws = ActiveSheet
> > s = ws.codeName
> > 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 = _
> > s & ".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 = _
> > s & ".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 = _
> > s & ".General_Button3_click"
> >
> > .Range("A1").Select
> > End With
> > End Sub
> >
> > But that doesn't get those onaction routines into the sheet module.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Stuart" <sgbooth@totalise.co.uk> wrote in message
> > news:%23nDkkWqyFHA.2552@TK2MSFTNGP10.phx.gbl...
> >> Hi, I'm getting too old for this .... keep forgetting even the simplest
> >> of
> >> things (g).
> >>
> >> I'm trying to adapt the following simple routine, which adds buttons to
a
> >> sheet.
> >>
> >> 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 = _
> >> "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
> >>
> >> Originally, the user was given a workbook containing a single
> >> preformatted
> >> Master sheet. This code sequence was in the sheet module. There were
> > buttons
> >> on the Master sheet which allowed the user to create a new sheet, then
> >> the
> >> above code ran, and created the above buttons on the new sheet.
> >>
> >> My users love it, and have sent me loads of old workbooks. They want
> > buttons
> >> on them too !!!
> >>
> >> I just can't find a way to adapt this to run on those old workbooks. I
> >> can
> >> easily copy the Master sheet across.
> >>
> >> Can someone help, please?
> >>
> >> Regards.
> >>
> >>
> >
> >
>
>