Create Button from VBA

[pingme89]

I have a spreadsheet (A) that creates a new spreadsheet (B). I would like to incorporate some VBA code from Spreadsheet (A) that when Spreadsheet (B) is created, a button labeled "Show Data" would be created on Spreadsheet (B).

Specifically speaking, I will have data in Column "E" that is hidden. I would like the "Show Data" Button to unhide Column "E" on Spreadsheet (B). Furthermore, if Spreadsheet (B) is printed, the "Show Data" button will be be printed.

Is this possible? And if so, how? Any help would be much appreciated.

 

[mikerickson]

If by "spreadsheet", you mean a worksheet rather than a workbook. If you have a blank template worksheet with a button that does that and you copy that worksheet, the button will be copied along with the sheet.

 

[pingme89]

Actually I meant workbook (A). Workbook (A) creates a new Workbook (B). That part is done. Now that I have data populated on Workbook (B) that has data hidden in Column "E", I want to create a button in Workbook (B) upon creation from the VBA code executed from Workbook (A).

 

[mikerickson]

And you'd also have to write the code that hid the column.

Again, copying an existing workbook, with the necessary controls and code, would be the easiest, surest way to go.

(Or copying a sheet from the master workbook to the newly created workbook.)

With Workbooks.Add
    ThisWorkbook.Sheets("Template").Copy after:=.Sheets(1)
End With

 

[pingme89]

And you'd also have to write the code that hid the column.

Again, copying an existing workbook, with the necessary controls and code, would be the easiest, surest way to go.

(Or copying a sheet from the master workbook to the newly created workbook.)

With Workbooks.Add
    ThisWorkbook.Sheets("Template").Copy after:=.Sheets(1)
End With

Unfortunately that isn't an option. Specifically speaking, Workbook (A) is a spreadsheet that creates estimates. The created Estimate would be Spreadsheet (B). As I mentioned, that part is complete. Every estimate is different and has different number of pages as well. As it stands now, Spreadsheet (B) is created, Column "E" is already hidden and password protected. Workbook (A) even creates a PDF copy of Spreadsheet (B). This is the reason why I don't want the button to show upon printing. Essentially, I will have 2 copies of the Estimate. One will be in Excel and one will be in PDF format. There is other information on Spreadsheet (B) which I don't want clients to see. I still need to access that information from the Excel version of the Estimate.

 

[mikerickson]

You can also the properties of a Button to not print the object. (Forms Button)

If you can't figure out to use the Macro Recorder to get the code to make a button, coding to add code modules and writing code into them might be beyond your level of VBA experience.

I'm strongly urging you to use Copy worksheets or template workbooks to duplicate controls and their code.

 

[pingme89]

You can also the properties of a Button to not print the object. (Forms Button)

If you can't figure out to use the Macro Recorder to get the code to make a button, coding to add code modules and writing code into them might be beyond your level of VBA experience.

I'm strongly urging you to use Copy worksheets or template workbooks to duplicate controls and their code.

Hmm. I don't think using Macro Recorder is best in this scenario. I just figured out and was able to create buttons with VBA Code alone. Perhaps you underestimate my ability. The only other issue now is to figure out how to assign VBA code that would run on Workbook (B).