Serious Macro Menu Help
 

Serious Macro Menu Help

I have been frustrasted for a month or so trying to create an Excel Menu
macro.
1. Macro opens a popup menu with a list of terms for expenses. By scrolling
down, I need each term when selected to refer to a submenu with terms.
2. Once name is selected the macro inputs the text in a cell, moves over one
cell to input the kind of expense, then moves over to next cell to allow
input by the user of the amount.
3. After User entering Amount the macro moves back (left) 2 cells and
down 1 row. Ready for next expense.
PS If this is asking too much then A. Where do I find how to create a popup
menu with terms? B. How do I stop a macro and allow user input?
Thanks in Advance

Serious Macro Menu Help
 

On 13 Jan., 01:36, TexYankee
wrote:
Serious Macro Menu Help

I have been frustrasted for a month or so trying to create an Excel Menu
macro.
1. Macro opens a popup menu with a list of terms for expenses. *By scrolling
down, I need each term when selected to refer to a submenu with terms. *
2. Once name is selected the macro inputs the text in a cell, moves over one
cell to input the kind of expense, then moves over to next cell to allow
input by the user of the amount.
3. After User entering Amount the macro moves back (left) 2 cells and
down 1 row. Ready for next expense.
PS If this is asking too much then A. Where do I find how to create a popup
menu with terms? B. How do I stop a macro and allow user input?
Thanks in Advance

Hi
Use a Inputbox to allow the user to enter data.

Answer = Inputbox("Enter amount ", "Header")

Have a look at this. Note OnAction = "MyMacro" call a macro named
MyMacro.

Public Const APPNAME As String = "Expenses"
Dim MyMenuBar As CommandBar
Dim cbPop As CommandBarControl
Dim cbCtl As CommandBarControl
Dim cbSub As CommandBarControl

Sub CreatePopupMenu()
On Error Resume Next
'Application.CommandBars(APPNAME).Delete ' Delete old menu with
same name

'Create MenuBar
Set MyMenuBar = Application.CommandBars.Add(APPNAME,
Position:=msoBarTop, temporary:=False)
MyMenuBar.Visible = True


' Create a popup control
Set cbPop =
Application.CommandBars(APPNAME).Controls.Add(Type :=msoControlPopup)
cbPop.Caption = APPNAME
cbPop.Visible = True

'---------------------------------------------------------
' PopUpMenu items
'------------------------------------------------


Set cbCtl = cbPop.Controls.Add(Type:=msoControlButton)
With cbCtl
.Visible = True
.Style = msoButtonCaption
.Caption = "&Expenses 1"
.OnAction = "Expenses1" ' Call macro when selected
End With

Set cbCtl = cbPop.Controls.Add(Type:=msoControlButton)
With cbCtl
.Visible = True
.Style = msoButtonCaption
.Caption = "E&xpenses 2"
.OnAction = "Expenses2" ' Call macro when selected
End With

'-------------------------------------------------
' SubMenu
'------------------------------------------------

Set cbSub = cbPop.Controls.Add(Type:=msoControlPopup)
With cbSub
.Visible = True
.Caption = "&Terms1"
End With

Set cbCtl = cbSub.Controls.Add(Type:=msoControlButton)
With cbCtl
.Visible = True
.Caption = "Ex&penses 3"
.OnAction = "Expenses3" ' Call macro when selected
End With
End Sub

Regards,

Per

Serious Macro Menu Help
 

Thanks I am anxious to put your suggestions to work.

"Per Jessen" wrote:

On 13 Jan., 01:36, TexYankee
wrote:
Serious Macro Menu Help

I have been frustrasted for a month or so trying to create an Excel Menu
macro.
1. Macro opens a popup menu with a list of terms for expenses. By scrolling
down, I need each term when selected to refer to a submenu with terms.
2. Once name is selected the macro inputs the text in a cell, moves over one
cell to input the kind of expense, then moves over to next cell to allow
input by the user of the amount.
3. After User entering Amount the macro moves back (left) 2 cells and
down 1 row. Ready for next expense.
PS If this is asking too much then A. Where do I find how to create a popup
menu with terms? B. How do I stop a macro and allow user input?
Thanks in Advance

Hi
Use a Inputbox to allow the user to enter data.

Answer = Inputbox("Enter amount ", "Header")

Have a look at this. Note OnAction = "MyMacro" call a macro named
MyMacro.

Public Const APPNAME As String = "Expenses"
Dim MyMenuBar As CommandBar
Dim cbPop As CommandBarControl
Dim cbCtl As CommandBarControl
Dim cbSub As CommandBarControl

Sub CreatePopupMenu()
On Error Resume Next
'Application.CommandBars(APPNAME).Delete ' Delete old menu with
same name

'Create MenuBar
Set MyMenuBar = Application.CommandBars.Add(APPNAME,
Position:=msoBarTop, temporary:=False)
MyMenuBar.Visible = True


' Create a popup control
Set cbPop =
Application.CommandBars(APPNAME).Controls.Add(Type :=msoControlPopup)
cbPop.Caption = APPNAME
cbPop.Visible = True

'---------------------------------------------------------
' PopUpMenu items
'------------------------------------------------


Set cbCtl = cbPop.Controls.Add(Type:=msoControlButton)
With cbCtl
.Visible = True
.Style = msoButtonCaption
.Caption = "&Expenses 1"
.OnAction = "Expenses1" ' Call macro when selected
End With

Set cbCtl = cbPop.Controls.Add(Type:=msoControlButton)
With cbCtl
.Visible = True
.Style = msoButtonCaption
.Caption = "E&xpenses 2"
.OnAction = "Expenses2" ' Call macro when selected
End With

'-------------------------------------------------
' SubMenu
'------------------------------------------------

Set cbSub = cbPop.Controls.Add(Type:=msoControlPopup)
With cbSub
.Visible = True
.Caption = "&Terms1"
End With

Set cbCtl = cbSub.Controls.Add(Type:=msoControlButton)
With cbCtl
.Visible = True
.Caption = "Ex&penses 3"
.OnAction = "Expenses3" ' Call macro when selected
End With
End Sub

Regards,

Per