Command Button for Macro on a Toolbar

[Guest]

Hi fellow Excel Users
I have a couple of questions

I know how to create a command button using The Control Toolbox

1. How do I record a macro and assign it to this command button
2. How do I get the command button to sit on my toolbar.

Many thanks in advance

 

[papou]

Hello
1- Record your macro, when finished, right-click on your command button,
select view code and just enter your macro name under Private Sub
CommandButton1_Click().
eg:
Private Sub CommandButton1_Click()
MacroNameHere
End Sub

2- Command buttons are objects related to sheets and therefore cannot be
placed into the Excel toolbar.
If your wish is to execute some code when clicking on a button from the
toolbar, you may consider building a custom commandbar button and assign a
macro to it.

HTH
Cordially
Pascal

 

[Guest]

Thanks Papou. I am now getting this Error Message:

Compile error:
Expected:=

What do I do here

 

[papou]

Can you post a portion of your code?

Cordially
Pascal

 

[Guest]

For the macro?

I just recorded the macr. don't understand coding much but here it is:
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveCell.FormulaR1C1 = "WBS"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Pers. No"
Columns("F:G").Select
Selection.Delete Shift:=xlToLeft
Columns("D").Select
Selection.Insert Shift:=xlToRight
Range("F9").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll ToRight:=3
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1

 

[papou]

Which line is highlighted when the error occurs?

Cordially
Pascal

 

[Guest]

Hi Papou

What I have done is I hve taken the whole code and pasted it in the Command
Box Code and deleted one "end Sub".
Now I need to figure out How I can now put this Command button in a Tool
Menu and get that Tool menu to sit in the Toolbar Menus.
Am I making anymore sense, I don't feel like I am after the a.m I have had.

Thanks

 

[Gord Dibben]

Tools>Customize>Commands.

Scroll down to "Macros" and select.

Drag the smiley face up to your existing toolbar and right-click on the smiley
and "assign macro".

Choose your macro from the dialog and OK.

Note: the options to name the button, change the smiley to another icon.


Gord Dibben MS Excel MVP