LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 154
Default 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.


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Using Find/Replace in Visual basic to adapt the code Zakynthos Excel Programming 6 September 8th 05 03:41 PM
Do..Loop in multi sheets norika Excel Programming 7 May 26th 05 07:44 AM
Please help me to adapt this code. Rob Hargreaves Excel Programming 1 February 2nd 05 08:27 PM
Loop through sheets michael Excel Programming 2 September 8th 04 02:52 PM
Loop across Sheets and number of sheets Raj[_8_] Excel Programming 2 December 18th 03 09:18 AM


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

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"