Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Old Menu Macro | Setting up and Configuration of Excel | |||
Macro in Menu | Excel Discussion (Misc queries) | |||
MACRO MENU | Excel Discussion (Misc queries) | |||
Macro Menu | Excel Programming | |||
How to get a vba macro to show up in the macro menu | Excel Programming |