Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adapt code to loop through sheets
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adapt code to loop through sheets
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" wrote in message ... 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adapt code to loop through sheets
If the worksheets aren't protected, then I think your code should work.
-- Regards, Tom Ogilvy "Stuart" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Find/Replace in Visual basic to adapt the code | Excel Programming | |||
Do..Loop in multi sheets | Excel Programming | |||
Please help me to adapt this code. | Excel Programming | |||
Loop through sheets | Excel Programming | |||
Loop across Sheets and number of sheets | Excel Programming |