XLA

[Guest]

I have used a macro I wrote and added it into excel. How do I now use it?
Can I create a menu line under tools say, or a shortcut in toolbar?

 

[Guest]

hi,
to run macro
Tools>macro>Macros>pick from list
or alt+F8
To assign macro to icon
Right Click any toobar then click customize.
In the left pane, scroll and click macro
Drag smiley face to toolbar(anywhere you want it)
on the customize box, click modify selection
click assign macro (at the bottom)
to change icon immage
on the customize box, click modify selecton
click change button imgage
or click edit buttom image(design your own)

 

[Bob Phillips]

add some code that creates the menu when the workbook opens

Here is some sample code to create a toolbar button on the Formatting as
suggested. This code would go in the ThisWorkbok code module of the addin.

I would also add my usual corollary that to see what FaceIds are available,
visit John Walkenbach's site at http://j-walk.com/ss/excel/tips/tip67.htm

Option Explicit

Dim sMenu As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

sMenu = "myButton"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl
Dim newMenu As Object 'CommandBarControl
Dim ctrlButton As Object 'CommandBarControl

sMenu = "Margin Calculator"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0

Set oCB = Application.CommandBars("Formatting")
Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)

With oCtl
.BeginGroup = True
.Caption = sMenu
.FaceId = 197
.Style = msoButtonIconAndCaption
.OnAction = "myMacro"
End With

End Sub

 

[Jim Rech]

FYI, John's FaceID utility is a static picture of the tool button faces in
Excel 97. For a dynamic look at the faces actually present in the user's
running version of Excel get this utility from Stephen Bullen's site:

http://www.oaltd.co.uk/DLCount/DLCount.asp?file=BtnFaces.zip

--
Jim Rech
Excel MVP
| add some code that creates the menu when the workbook opens
|
| Here is some sample code to create a toolbar button on the Formatting as
| suggested. This code would go in the ThisWorkbok code module of the addin.
|
| I would also add my usual corollary that to see what FaceIds are
available,
| visit John Walkenbach's site at http://j-walk.com/ss/excel/tips/tip67.htm
|
| Option Explicit
|
| Dim sMenu As String
|
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
|
| sMenu = "myButton"
|
| On Error Resume Next
| Application.CommandBars("Formatting").Controls(sMenu).Delete
| On Error GoTo 0
| End Sub
|
| Private Sub Workbook_Open()
| Dim oCB As CommandBar
| Dim oCtl As CommandBarControl
| Dim newMenu As Object 'CommandBarControl
| Dim ctrlButton As Object 'CommandBarControl
|
| sMenu = "Margin Calculator"
|
| On Error Resume Next
| Application.CommandBars("Formatting").Controls(sMenu).Delete
| On Error GoTo 0
|
| Set oCB = Application.CommandBars("Formatting")
| Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)
|
| With oCtl
| .BeginGroup = True
| .Caption = sMenu
| .FaceId = 197
| .Style = msoButtonIconAndCaption
| .OnAction = "myMacro"
| End With
|
| End Sub
|
|
| --
|
| HTH
|
| RP
|
| | > I have used a macro I wrote and added it into excel. How do I now use
it?
| > Can I create a menu line under tools say, or a shortcut in toolbar?
|
|