Create Button from VBA

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
130
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,570
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

Board Regular
Joined
Jan 23, 2014
Messages
130
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,570
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.)

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

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
130
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.)

Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,570
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

Board Regular
Joined
Jan 23, 2014
Messages
130
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,100,195
Messages
5,473,080
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top