Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding Menu Item
I setup an interactive XLS without any macro using the basic function such as
VLOOKUP, INDEX... I am trying to avoid using any macro in this sheet. However, my dilemna is that the user wants to be able to print the report for each row of data by just clicking on a button. A user can do this by select a row and print. Select another row and print. That's easy to implement with Macro. However, Is it possible to add a menu item under "File/Print" using an Add-in so it's independent of the interactive XLS I create? If so, any good site I can check on it. Thanks a bunch M |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding Menu Item
One way:
Put these in the ThisWorkbook code module of the add-in: Private Sub Workbook_Open() Const nFileMenuID As Long = 30002 Const nPrintControlID As Long = 4 Dim nPrintIndex As Long DeletePrintRowItem With Application.CommandBars.FindControl(Id:=nFileMenuI D) nPrintIndex = _ .CommandBar.FindControl(Id:=nPrintControlID).Index With .Controls.Add(Type:=msoControlButton, _ Befo=nPrintIndex + 1, _ Temporary:=True) .Caption = "Print Row(s)..." .Tag = "PrintRowTag" .OnAction = "PrintRow" .BeginGroup = False .Visible = True End With End With End Sub Private Sub DeletePrintRowItem() On Error Resume Next Application.CommandBars.FindControl(Tag:="PrintRow Tag").Delete On Error GoTo 0 End Sub Put this in a regular code module: Public Sub PrintRow() Selection.EntireRow.PrintOut Copies:=1 End Sub Obviously, this could use some additional error handling, but it should give you a start. In article , "matelot" wrote: I setup an interactive XLS without any macro using the basic function such as VLOOKUP, INDEX... I am trying to avoid using any macro in this sheet. However, my dilemna is that the user wants to be able to print the report for each row of data by just clicking on a button. A user can do this by select a row and print. Select another row and print. That's easy to implement with Macro. However, Is it possible to add a menu item under "File/Print" using an Add-in so it's independent of the interactive XLS I create? If so, any good site I can check on it. Thanks a bunch M |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding Menu Item
Thanks. Can you help clarify..
Why calling the DeletePrintRowItem Sub if the menu item is going to be there anyway? "JE McGimpsey" wrote: One way: Put these in the ThisWorkbook code module of the add-in: Private Sub Workbook_Open() Const nFileMenuID As Long = 30002 Const nPrintControlID As Long = 4 Dim nPrintIndex As Long DeletePrintRowItem With Application.CommandBars.FindControl(Id:=nFileMenuI D) nPrintIndex = _ .CommandBar.FindControl(Id:=nPrintControlID).Index With .Controls.Add(Type:=msoControlButton, _ Befo=nPrintIndex + 1, _ Temporary:=True) .Caption = "Print Row(s)..." .Tag = "PrintRowTag" .OnAction = "PrintRow" .BeginGroup = False .Visible = True End With End With End Sub Private Sub DeletePrintRowItem() On Error Resume Next Application.CommandBars.FindControl(Tag:="PrintRow Tag").Delete On Error GoTo 0 End Sub Put this in a regular code module: Public Sub PrintRow() Selection.EntireRow.PrintOut Copies:=1 End Sub Obviously, this could use some additional error handling, but it should give you a start. In article , "matelot" wrote: I setup an interactive XLS without any macro using the basic function such as VLOOKUP, INDEX... I am trying to avoid using any macro in this sheet. However, my dilemna is that the user wants to be able to print the report for each row of data by just clicking on a button. A user can do this by select a row and print. Select another row and print. That's easy to implement with Macro. However, Is it possible to add a menu item under "File/Print" using an Add-in so it's independent of the interactive XLS I create? If so, any good site I can check on it. Thanks a bunch M |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding Menu Item
It's simply a precaution against XL exiting abnormally and retaining the
menu item, then generating a second item when the add-in opens. Since the item is generated with the Temporary attribute, it's really belt-and-suspenders. In article , "matelot" wrote: Thanks. Can you help clarify.. Why calling the DeletePrintRowItem Sub if the menu item is going to be there anyway? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding Menu Item
Great! Thanks for the explanation.
"JE McGimpsey" wrote: It's simply a precaution against XL exiting abnormally and retaining the menu item, then generating a second item when the add-in opens. Since the item is generated with the Temporary attribute, it's really belt-and-suspenders. In article , "matelot" wrote: Thanks. Can you help clarify.. Why calling the DeletePrintRowItem Sub if the menu item is going to be there anyway? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Menu item disabled | Excel Worksheet Functions | |||
Menu Item name | Excel Programming | |||
Menu items added with menu item editor in older versions | Excel Discussion (Misc queries) | |||
Adding a menu item right click menu when clicking on a single. | Excel Programming | |||
New menu bar item | Excel Programming |