Copying Embedded Command Buttons in Excel

[protector]

I have created a Macro that creates a new work sheet and then copies formatting from 2 other work sheets to make an order form. What I would like to do is add buttons to the spread sheet when it is created so that when they are clicked the order amount for the associated item is entered in the order column.

I have been able to create the buttons manually on one of the work sheets that supplies the formatting and get the buttons to perform the actions I need but when I run the macro the buttons are not added to the new work sheet.

Any help anyone could give would be appreciated

Craig

 

[Loomah]

Hi Craig
I'm wide open to learning something myself here, but from what I can tell you may need to create your buttons from the 'Forms' toolbar and not the 'Control Toolbox'

If you use the forms toolbar your button should becopied but more importantly the code will remain associated with the button. This doesn't happen with buttons created from the control toolbox.

That said, this (recorded) code will give you a start. It copied one of each kind of button as a demo.

Sub Copy_Buttons()
ActiveSheet.Shapes("Button 2&quot.Select
Selection.Copy
Sheets("Sheet2&quot.Select
ActiveSheet.Paste
Sheets("Sheet1&quot.Select
ActiveSheet.Shapes("CommandButton1&quot.Select
Selection.Copy
Sheets("Sheet2&quot.Select
ActiveSheet.Paste
Range("A1&quot.Select
End Sub

If you MUST go with a command button, it'll take a better man than me to tell you how to assign your code to the new button.

;-) If a man says something and there are no women there to hear him, is he still wrong?
"...Three Lions On A Shirt..."

 

[acron]

Why not have a blank worksheet as a template, which includes all buttons, code and formatting. You canb have this sheet hidden. When you need a new sheet just copy the 'Template' sheet and make the copy visible.

A.C.

 

[protector]

Hello people

Thanks for the help, didn't realise there were 2 methods we could use to add buttons, adding them from the form bar worked a dream. Acron, I coundn't figure out how to create and save a template, our network looks like it has been set up so we are unable to do this, but I used your idea about hiding the work sheet, mainly so other users are unable to make changes.
I have put the code for one button below and also the revised code for the creation of the new order form.

Again thanks for the advise, see you on the flip side
Craig
Sub Button1_Click()
'
' Button1_Click Macro
' Macro recorded 26/06/02 by brownc5
'

' Enter item to be ordered on the order form

ActiveSheet.Select ' Select new sheet
Range("J8&quot.Select ' Select Order item cell
ActiveCell.FormulaR1C1 = "6(1 Tray)" ' Enter amount to be ordered

End Sub
Sub Create_New_Order_Form()
Dim todaysDate As Variant
Dim newSheet As Worksheet
'
' Create_New_Order_Form Macro
' Creates a new order form for Cafe Express
'
' Keyboard Shortcut: Ctrl+n
'
todaysDate = Format(Date, "dd.mm.yy&quot 'get date
Sheets("Order Form&quot.Visible = True ' Unhide order form

Sheets("Order Form&quot.Select 'copy order sheet
Range("A1:J20&quot.Select
Selection.Copy

Sheets("Order Form&quot.Visible = False ' Hide Order Form

Set newSheet = Worksheets.Add 'create new worksheet
newSheet.Name = todaysDate 'change name of new worksheet
ActiveSheet.Paste 'paste copied form
Columns("D&quot.ColumnWidth = 26.57 'change column widths
Columns("C:C&quot.ColumnWidth = 14
Columns("I:I&quot.ColumnWidth = 16
Rows("8:20&quot.RowHeight = 18.75

Range("J8&quot.Select 'select first item order cell

End Sub