How to initiate macros in user-friendly fashion
 

I have created several macros that I want someone else, who is not that
well-versed, to be able to run easily, over and over. I am afraid to just
assign keyboard controls with a letter (such as cntrl-a), or such things, as
they might inadvertently trigger a macro when they are just typing stuff in.

So, something I recall seeing, but don't recall how to set-up, is that the
name of selected (though not all, I hope) macros appear on the Tools
dropdown within EXCEL. Even better, if easy to do, would be the clickable
buttons you can put on a worksheet and click to initiate macros, - but if
that's tricky, I don't need that now.

Can someone tell me how this is done, please?

Thanks much

Dean

How to initiate macros in user-friendly fashion
 

Dean,

Here is an example of a menu on the main Excel menu


'If you put code in the appropriate workbook open event, and
'delete it in the close it will exist only for that workbook.

'Here is an example of a building a commandbar on the fly
'when you open a workbook. It adds a sub-menu to the Tools menu.

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")

Set oCtl = oCb.Controls.Add(Type:=msoControlPopup, temporary:=True)
With oCtl
.Caption = "myMenu"

Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"

Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMeny").Delete
End Sub

'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Grace" wrote in message
...
I have created several macros that I want someone else, who is not that
well-versed, to be able to run easily, over and over. I am afraid to just
assign keyboard controls with a letter (such as cntrl-a), or such things,
as
they might inadvertently trigger a macro when they are just typing stuff
in.

So, something I recall seeing, but don't recall how to set-up, is that the
name of selected (though not all, I hope) macros appear on the Tools
dropdown within EXCEL. Even better, if easy to do, would be the clickable
buttons you can put on a worksheet and click to initiate macros, - but if
that's tricky, I don't need that now.

Can someone tell me how this is done, please?

Thanks much

Dean

How to initiate macros in user-friendly fashion
 

Thanks Bob. I am a little confused. I think you just wanted me to get to
the area where I write the macros for this file and copy this in. So far, I
copied in just the workbook open one, not the close one - does the latter
shut it down, so you don't get it in another file?

In any event, I copied it in. The compiler didn't burp or anything. I
saved the file, closed it and re-opened it. My understanding is that, when
I go to tools, I should now see the names of macros there. I don't see any
difference yet. What could be wrong? By the way, can I show only some, but
not all, of the macros this way?

Thanks much
"Bob Phillips" wrote in message
...
Dean,

Here is an example of a menu on the main Excel menu


'If you put code in the appropriate workbook open event, and
'delete it in the close it will exist only for that workbook.

'Here is an example of a building a commandbar on the fly
'when you open a workbook. It adds a sub-menu to the Tools menu.

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")

Set oCtl = oCb.Controls.Add(Type:=msoControlPopup, temporary:=True)
With oCtl
.Caption = "myMenu"

Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"

Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMeny").Delete
End Sub

'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Grace" wrote in message
...
I have created several macros that I want someone else, who is not that
well-versed, to be able to run easily, over and over. I am afraid to
just
assign keyboard controls with a letter (such as cntrl-a), or such things,
as
they might inadvertently trigger a macro when they are just typing stuff
in.

So, something I recall seeing, but don't recall how to set-up, is that
the
name of selected (though not all, I hope) macros appear on the Tools
dropdown within EXCEL. Even better, if easy to do, would be the
clickable
buttons you can put on a worksheet and click to initiate macros, - but if
that's tricky, I don't need that now.

Can someone tell me how this is done, please?

Thanks much

Dean

How to initiate macros in user-friendly fashion
 

Grace,

You should add the second macro to tidy up afterwards.

You won't see anything on the Tools menu, but you should see a 'myMenu' on
the Worksheet Menu Bar, the one at the top, with 2 sample buttons on it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Grace" wrote in message
...
Thanks Bob. I am a little confused. I think you just wanted me to get to
the area where I write the macros for this file and copy this in. So far,
I
copied in just the workbook open one, not the close one - does the latter
shut it down, so you don't get it in another file?

In any event, I copied it in. The compiler didn't burp or anything. I
saved the file, closed it and re-opened it. My understanding is that,
when
I go to tools, I should now see the names of macros there. I don't see
any
difference yet. What could be wrong? By the way, can I show only some,
but
not all, of the macros this way?

Thanks much
"Bob Phillips" wrote in message
...
Dean,

Here is an example of a menu on the main Excel menu


'If you put code in the appropriate workbook open event, and
'delete it in the close it will exist only for that workbook.

'Here is an example of a building a commandbar on the fly
'when you open a workbook. It adds a sub-menu to the Tools menu.

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")

Set oCtl = oCb.Controls.Add(Type:=msoControlPopup, temporary:=True)
With oCtl
.Caption = "myMenu"

Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"

Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMeny").Delete
End Sub

'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Grace" wrote in message
...
I have created several macros that I want someone else, who is not that
well-versed, to be able to run easily, over and over. I am afraid to
just
assign keyboard controls with a letter (such as cntrl-a), or such
things,
as
they might inadvertently trigger a macro when they are just typing
stuff
in.

So, something I recall seeing, but don't recall how to set-up, is that
the
name of selected (though not all, I hope) macros appear on the Tools
dropdown within EXCEL. Even better, if easy to do, would be the
clickable
buttons you can put on a worksheet and click to initiate macros, - but
if
that's tricky, I don't need that now.

Can someone tell me how this is done, please?

Thanks much

Dean

How to initiate macros in user-friendly fashion
 

I assume you mean the area where one sees (from left to right), File, Edit,
View... Help

I don't see it. I also don't see it in my list of macros (when I do tools,
macro), though that may be because it is "private". Any idea what I could
be doing wrong? I cut and pasted your macro out of the post. Could you try
that in a new file and see if that works?

Thanks again!

"Bob Phillips" wrote in message
...
Grace,

You should add the second macro to tidy up afterwards.

You won't see anything on the Tools menu, but you should see a 'myMenu' on
the Worksheet Menu Bar, the one at the top, with 2 sample buttons on it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Grace" wrote in message
...
Thanks Bob. I am a little confused. I think you just wanted me to get
to
the area where I write the macros for this file and copy this in. So
far,
I
copied in just the workbook open one, not the close one - does the latter
shut it down, so you don't get it in another file?

In any event, I copied it in. The compiler didn't burp or anything. I
saved the file, closed it and re-opened it. My understanding is that,
when
I go to tools, I should now see the names of macros there. I don't see
any
difference yet. What could be wrong? By the way, can I show only some,
but
not all, of the macros this way?

Thanks much
"Bob Phillips" wrote in message
...
Dean,

Here is an example of a menu on the main Excel menu


'If you put code in the appropriate workbook open event, and
'delete it in the close it will exist only for that workbook.

'Here is an example of a building a commandbar on the fly
'when you open a workbook. It adds a sub-menu to the Tools menu.

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")

Set oCtl = oCb.Controls.Add(Type:=msoControlPopup, temporary:=True)
With oCtl
.Caption = "myMenu"

Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"

Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMeny").Delete
End Sub

'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Grace" wrote in message
...
I have created several macros that I want someone else, who is not
that
well-versed, to be able to run easily, over and over. I am afraid to
just
assign keyboard controls with a letter (such as cntrl-a), or such
things,
as
they might inadvertently trigger a macro when they are just typing
stuff
in.

So, something I recall seeing, but don't recall how to set-up, is that
the
name of selected (though not all, I hope) macros appear on the Tools
dropdown within EXCEL. Even better, if easy to do, would be the
clickable
buttons you can put on a worksheet and click to initiate macros, - but
if
that's tricky, I don't need that now.

Can someone tell me how this is done, please?

Thanks much

Dean

How to initiate macros in user-friendly fashion
 

Has anyone in this thread taught you that it is VERY easy to assign a
macro to a button. Go to ViewToolbarsForms and you should get a
toolbar now which shows a variety of forms Click on the one entitled
"Button". Then you just drag it the size of the button you would like.
You can even add text to the button. Then to add the macro, right
click on it, choose assign macro and you can then just the select the
macro you have made. Then when the user clicks the button it will run
the macro.

How to initiate macros in user-friendly fashion
 

The macro will automatically run next time you open the workbook, but you
can force it to run by going to the VBE and the ThisWorkbook module and run
that procedure.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Grace" wrote in message
...
I assume you mean the area where one sees (from left to right), File,
Edit,
View... Help

I don't see it. I also don't see it in my list of macros (when I do
tools,
macro), though that may be because it is "private". Any idea what I could
be doing wrong? I cut and pasted your macro out of the post. Could you
try
that in a new file and see if that works?

Thanks again!

"Bob Phillips" wrote in message
...
Grace,

You should add the second macro to tidy up afterwards.

You won't see anything on the Tools menu, but you should see a 'myMenu'
on
the Worksheet Menu Bar, the one at the top, with 2 sample buttons on it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Grace" wrote in message
...
Thanks Bob. I am a little confused. I think you just wanted me to get
to
the area where I write the macros for this file and copy this in. So
far,
I
copied in just the workbook open one, not the close one - does the
latter
shut it down, so you don't get it in another file?

In any event, I copied it in. The compiler didn't burp or anything. I
saved the file, closed it and re-opened it. My understanding is that,
when
I go to tools, I should now see the names of macros there. I don't see
any
difference yet. What could be wrong? By the way, can I show only
some,
but
not all, of the macros this way?

Thanks much
"Bob Phillips" wrote in message
...
Dean,

Here is an example of a menu on the main Excel menu


'If you put code in the appropriate workbook open event, and
'delete it in the close it will exist only for that workbook.

'Here is an example of a building a commandbar on the fly
'when you open a workbook. It adds a sub-menu to the Tools menu.

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")

Set oCtl = oCb.Controls.Add(Type:=msoControlPopup, temporary:=True)
With oCtl
.Caption = "myMenu"

Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"

Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMeny").Delete
End Sub

'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Grace" wrote in message
...
I have created several macros that I want someone else, who is not
that
well-versed, to be able to run easily, over and over. I am afraid
to
just
assign keyboard controls with a letter (such as cntrl-a), or such
things,
as
they might inadvertently trigger a macro when they are just typing
stuff
in.

So, something I recall seeing, but don't recall how to set-up, is
that
the
name of selected (though not all, I hope) macros appear on the Tools
dropdown within EXCEL. Even better, if easy to do, would be the
clickable
buttons you can put on a worksheet and click to initiate macros, -
but
if
that's tricky, I don't need that now.

Can someone tell me how this is done, please?

Thanks much

Dean

How to initiate macros in user-friendly fashion
 

Wow, I see that. Pretty nice, Thx.

"Chip" wrote in message
oups.com...
No you can do whatever you like to it: change the font color, font
size, the size of the button, etc....

What everyone has been showing you is for when you want to have excel
run a macro without your users having to do anything but it doesnt look
as cool =)


You can assign any of those forms in that toolbar to have a macro
attached...i.e. a checkbox, a drop-down list, etc.

How to initiate macros in user-friendly fashion
 

John Walkenbach as a sample workbook that adds a bunch of routines to the
worksheet menu bar. He calls it MenuMaker.xls.

You can find it:
http://j-walk.com/ss/excel/tips/tip53.htm

Grace wrote:

I have created several macros that I want someone else, who is not that
well-versed, to be able to run easily, over and over. I am afraid to just
assign keyboard controls with a letter (such as cntrl-a), or such things, as
they might inadvertently trigger a macro when they are just typing stuff in.

So, something I recall seeing, but don't recall how to set-up, is that the
name of selected (though not all, I hope) macros appear on the Tools
dropdown within EXCEL. Even better, if easy to do, would be the clickable
buttons you can put on a worksheet and click to initiate macros, - but if
that's tricky, I don't need that now.

Can someone tell me how this is done, please?

Thanks much

Dean

--

Dave Peterson

How to initiate macros in user-friendly fashion
 

Once you have created a button, you can move your mouse pointer over it,
hold down the Ctrl key then left click to "Select" the button.
To move it click and drag the perimeter.
To resize move your mouse til you see the arrows over the perimeter. Drag
the arrow.
Change the Caption by simply typing a new word(s)
Change the font the same as if you had selected a cell.


"Grace" wrote:

Boy, are you right! It is very easy, although I would like to know, after
you assign the macro, is it too late to move the buttons around, change
their size, or move them around?

This is the best way to go, as it appears so impressive!

Thx to you and Bob. I am done on this thread, other than the non-critical
questions above.

"Chip" wrote in message
ups.com...
Has anyone in this thread taught you that it is VERY easy to assign a
macro to a button. Go to ViewToolbarsForms and you should get a
toolbar now which shows a variety of forms Click on the one entitled
"Button". Then you just drag it the size of the button you would like.
You can even add text to the button. Then to add the macro, right
click on it, choose assign macro and you can then just the select the
macro you have made. Then when the user clicks the button it will run
the macro.

How to initiate macros in user-friendly fashion
 

Another variation on this is to use instead of a Button, you can also use
Drawing Shapes and assign macros to them.

"Grace" wrote:

Boy, are you right! It is very easy, although I would like to know, after
you assign the macro, is it too late to move the buttons around, change
their size, or move them around?

This is the best way to go, as it appears so impressive!

Thx to you and Bob. I am done on this thread, other than the non-critical
questions above.

"Chip" wrote in message
ups.com...
Has anyone in this thread taught you that it is VERY easy to assign a
macro to a button. Go to ViewToolbarsForms and you should get a
toolbar now which shows a variety of forms Click on the one entitled
"Button". Then you just drag it the size of the button you would like.
You can even add text to the button. Then to add the macro, right
click on it, choose assign macro and you can then just the select the
macro you have made. Then when the user clicks the button it will run
the macro.

How to initiate macros in user-friendly fashion
 

Thank you, I have bookmarked it for future use - thanks to everyone. I am
in good shape!

"Dave Peterson" wrote in message
...
John Walkenbach as a sample workbook that adds a bunch of routines to the
worksheet menu bar. He calls it MenuMaker.xls.

You can find it:
http://j-walk.com/ss/excel/tips/tip53.htm

Grace wrote:

I have created several macros that I want someone else, who is not that
well-versed, to be able to run easily, over and over. I am afraid to
just
assign keyboard controls with a letter (such as cntrl-a), or such things,
as
they might inadvertently trigger a macro when they are just typing stuff
in.

So, something I recall seeing, but don't recall how to set-up, is that
the
name of selected (though not all, I hope) macros appear on the Tools
dropdown within EXCEL. Even better, if easy to do, would be the
clickable
buttons you can put on a worksheet and click to initiate macros, - but if
that's tricky, I don't need that now.

Can someone tell me how this is done, please?

Thanks much

Dean

--

Dave Peterson