How to embed commandbutton code within the button

[sassriverrat]

Hello Again,

I have a macro that creates a new sheet, formats the entire sheet, adds all of the formulas throughout the sheet, and creates a few buttons.

These Buttons (Reset Sheet, Print, Save, and Index) aren't complicated, but because I am constantly generating a new sheet, I was wondering if there was a way to embed the "call" part into the button when it's made otherwise generating a new sheet would then require someone to go into the sheet's coding and put the command in there.

 

[sassriverrat]

Won't let me delete but I solved.....

For the next guy:

ActiveSheet.Buttons.Add(x,x,x,x).Select 'Coordinates of the Button
Selection.OnAction = "YourMacroToRun"

 

[My Aswer Is This]

Why not create a template sheet.

Have all the buttons and formatting the way you want.

And then when you want a new sheet make a copy of the template


Try this:

Sub Copy_Template()
'Modified  11/5/2018  3:57:41 PM  EST
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Template" & ActiveSheet.Index
End Sub

Then modify the sheet name like you want

 

[sassriverrat]

Templates might work-

The workbook basically lays out like this- I have five sheets+

1. Notes- This has constants in it, like mileages and such, that never change. They are used throughout the other sheets.
2. Voyage Specifics- This has the constants in it particular to one voyage. This of a simple algebraic formula, =Ax^2 + bx + c. If this formula is used throughout the last three sheets (which is what the workbook is based around), the c for each of these formulas comes from this sheet.
3. The "Noon" sheet- 1 of the three important sheets in the book- this is the printable "report" for the day's stats. The entire workbook is based around printing this report. The "Noon" is the first day.
4. The "Noon#" sheet- Although not always used, this is the "Noon" Sheet plus a day (or more). If a voyage is 5 days, there is the original "noon" plus Noon 2, Noon 3, Noon 4, and Noon 5. Each day builds upon the last or subtracts from the last (i.e. total hours builds, and distance to go subtracts).
5. The "Arrival" sheet- the last and always used of the three reports- this gives the totals for the voyage. It also includes the "noon" info from the last "noon" up until now- meaning the "noon" gives voyage info every 24 hours and the arrival gives the totals but also info from the last noon until the termination (less than 24 hours) of the voyage.

The noon is used 99.9% of the time- occasionally not. The Noon# is usually used but the # of them ranges often- my old workbook, prior to VBA, used a lot of IF formulas and I had Noon-Noon 10 of dedicated sheets- usually didn't use more than 10 and would have leftovers but if I did use more than 10 it was screwed.

 

[My Aswer Is This]

Glad to see you previously said:

Won't let me delete but I solved.....

​

 

[sassriverrat]

No worries-

While I'm thinking-

Do you know the coding for a "Save As" button that will "save as" the workbook with a new name, defined in sheet1, cells A1:A5 (5 different parts of a name), delete the old workbook, and archive the file in a specified folder- if the folder won't exist then to create it.

Basically looking to have a blank version of the workbook named "Master Template" First time you use it it gets saved as "Current Voyage Report (I could make this with a specified directory), but I want it to save as in a new directory and archived name when the "arrival sheet" is finished (now you see why I have three types of sheets and buttons). The directory, specified in sheet 1, cell B22, is C:\Users\sassr\desktop\Noons\350-400 351-400 is the folder for voyages 350 through 400. there are also 7 other folders, 1-50, 51-100, etc etc. I'd like this whole piece of code to create a new folder (i.e. 401-450 when the time comes).

 

[My Aswer Is This]

You said:

No worries-​

Not sure what that means. I was just saying it sounds like you already found a way to do what you wanted.

As far as your last question. I would suggest you start a new posting and post this question.

This is beyond my knowledgebase.

 

[sassriverrat]

No worries- your help has been very much appreciated