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

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

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

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?

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?