How can I make the print icon trigger customized macro?

[Bon]

Hello all

How can I make the print icon trigger customized macro?
My idea:
I have a MS Access user interface with buttons. When I clicked the Save
Draft button, the MS Excel workbook will be saved in a folder. Inside
the MS Excel workbook, I created a watermark macro and I assigned the
watermark macro to the Print icon. (Right click the Print icon ->
Customize -> Right click the print icon -> Assign Macro). When the user
clicks the Print icon, the DRAFT watermark will be added to all
worksheets and the print dialog box will be shown.

When I open a new workbook using one of the button in MS Access
interface and click the print icon, the assigned name will be changed.
So, an error "specified object can't be found" occurs. For example, I
assigned the macro (watermark) to the Print button in abc.xls workbook.
If I click the open button in the MS Access interface and click the
Print button, the assigned macro name will be changed to
cde.xls!watermark. The cde.xls is the recently opened workbook.

Can anyone give me some advices?

Thanks
Bon

 

[Guest]

Bon-

You could save the macro to your personal workbook. That way it should stay
connected to the print button.

Jayson

 

[Bon]

I have to assign the watermark macro to the customized excel template.
When a user clicks the Open button in MS Access user interface, the
template will be opened. After the user fill in all the information in
the template, s/he has to click the Save Draft/Save Final button in the
MS Access user interface in order to save the Draft or the document.
When the template is saved, the file name will be generated by one of
the MS Access macro.

But, I don't know why the macro name is changed automatically. For
example, the macro name is template.xls!watermark orginally. After the
Open button is clicked and clicks the Print icon, the assigned macro
name will be changed to newdocumentname.xls!watermark. Please give me
some advices.

Thanks

JNW 寫é“：

 

[Guest]

I tried recreating and I can't seem to get it to do what you have said. I
did notice however that the workbook that contains the macro needs to be open
in order for the macro to run. As far as the name changing on you, I imagine
it's because it's trying to find the macro in the workbook that is open. Try
specifying the path when you assign the macro (i.e. 'C:\Book1.xls'!test)

If this doesn't answer your question post some of your code so we can see if
we can find the problem.

 

[Bon]

By double-clicking the saved draft document in MS Access Interface, the
draft document is opened. Then, I click the Print icon in the
customized toolbar. An error is occurred - "A document with the name
'abc_6122005_2014.xls' is already open. You cannot open two documents
with the same name, even if the documents are in different folder. To
open the second document, either close the document that's currently
open, or rename one of the documents." But, there is one Excel document
opening only. I have specified the path when I assigned the macro
('C:\templatename.xls'!Watermark).

Code inside ThisWorkbook as follow:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
WaterMarkerGone
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Watermark
End Sub

Code of my watermark module as follow:
Sub Watermark()
Dim wkSheet As Integer
Dim printButtonOption As String
Set myDocument = ActiveWorkbook

For wkSheet = 1 To ActiveWorkbook.Sheets.Count
Set myWatermark = myDocument.Worksheets(wkSheet).Shapes.AddTextEffect(
_
PresetTextEffect:=msoTextEffect2, _
Text:="D R A F T", _
FontName:="Arial Black", _
FontSize:=60, _
FontBold:=False, _
FontItalic:=False, _
Left:=100, _
Top:=200)
With myWatermark
..IncrementRotation -30
..Fill.Visible = msoFalse
..Fill.Transparency = 0.5
..Fill.Solid
..Fill.ForeColor.SchemeColor = 23
..Line.Weight = 0.75
..Line.DashStyle = msoLineSolid
..Line.Style = msoLineSingle
..Line.Transparency = 0#
..Line.Visible = msoTrue
..Line.ForeColor.SchemeColor = 23
..Line.BackColor.RGB = RGB(255, 255, 255)
..ZOrder msoBringToBack
End With
Next wkSheet

printButtonOption = Application.Dialogs(xlDialogPrint).Show
If printButtonOption = False Then
WaterMarkerGone
End If
End Sub

Sub WaterMarkerGone()
Dim intSheet As Integer
Dim wkBook As Workbook
Dim wkSheet As Worksheet
Dim intShape As Integer
Dim totalCount As Integer
Dim totalShapes As Integer
Dim nmbSheet As Integer

totalCount = ActiveWorkbook.Worksheets.Count
For intSheet = 1 To totalCount
Set wkSheet = ActiveWorkbook.Worksheets(intSheet)
totalShapes = wkSheet.Shapes.Count
Do While totalShapes > 0
wkSheet.Shapes(totalShapes).Delete
totalShapes = totalShapes - 1
Loop
Next intSheet
End Sub

Please give me some advices

Thanks
Bon

 

[Guest]

Bon-

I'm sorry to say that I don't think I know enough to help out. Everything
in excel is working and it seems the problem is with Access and I don't know
much about Access let alone vba syntaxes for it.

Hope someone here can guide you.
JNW