Curious about macro possibility
I was wondering, and probably reaching for this one, if a macro can write
another macro and place it in the appropriate "Microsoft Excel Object" sheet? The project I am working on is a annual scheduling workbook that will create 26 sheets (payperiods) when a commandbutton is clicked and name each sheet accordingly. During this execution, I was wondering if I could also: 1) create 26 commandbuttons with same caption as its respective sheet, size and align each button on a sheet, 2) write the three line code for each commandbutton, using the caption (also name of sheet) as the reference to GOTO that sheet? Just curious about number 2. Thanks, Les |
Curious about macro possibility
Why not use a template with the controls and code in place? You can read
sheet names, position and change captions and refer to GOTO locations by reference. I do not see a need to write module code. -- Regards, Nigel "WLMPilot" wrote in message ... I was wondering, and probably reaching for this one, if a macro can write another macro and place it in the appropriate "Microsoft Excel Object" sheet? The project I am working on is a annual scheduling workbook that will create 26 sheets (payperiods) when a commandbutton is clicked and name each sheet accordingly. During this execution, I was wondering if I could also: 1) create 26 commandbuttons with same caption as its respective sheet, size and align each button on a sheet, 2) write the three line code for each commandbutton, using the caption (also name of sheet) as the reference to GOTO that sheet? Just curious about number 2. Thanks, Les |
Curious about macro possibility
I think this will accomplish what you're trying to do. But it uses
hyperlinks instead of buttons.. Sub CreateIndexSheet() Dim ws As Worksheet Dim wsIndex As Worksheet Dim lRow As Long With ActiveWorkbook Set wsIndex = .Worksheets.Add(Befo=.Worksheets(1)) wsIndex.Name = "Index" End With With wsIndex.Range("A1") .Value = "Index" .Font.Bold = True End With lRow = 2 For Each ws In ActiveWorkbook.Worksheets If ws.Name < "Index" Then With wsIndex .Range("A" & lRow).Value = ws.Name .Hyperlinks.Add Anchor:=.Range("A" & lRow), _ Address:="", _ SubAddress:=ws.Name & "!A1" lRow = lRow + 1 End With End If Next ws wsIndex.Columns("A").AutoFit End Sub -- Hope that helps. Vergel Adriano "WLMPilot" wrote: I was wondering, and probably reaching for this one, if a macro can write another macro and place it in the appropriate "Microsoft Excel Object" sheet? The project I am working on is a annual scheduling workbook that will create 26 sheets (payperiods) when a commandbutton is clicked and name each sheet accordingly. During this execution, I was wondering if I could also: 1) create 26 commandbuttons with same caption as its respective sheet, size and align each button on a sheet, 2) write the three line code for each commandbutton, using the caption (also name of sheet) as the reference to GOTO that sheet? Just curious about number 2. Thanks, Les |
All times are GMT +1. The time now is 08:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com