Make global Macro/Add-in for all active Workbooks/Sheets

S

SunRace

I need help…. I am trying to create document classification macro for Excel.
Macro is ready and it gets activated on SaveAs. Now what I want to do is – I
want to save this macro as excel Add-In so that I can install this in all
users login. I am following instructions from following article. Add-in is
getting configured. But the problem which I am facing is when I do save as in
new workbook - the macro or add-in does not get activated. What I could
understand was that the macro is limited to the workbook in which it was
created. How do I make this macro/add-in global for all active and new
workbooks which I will create?

http://de.wikipedia.org/wiki/Wikipedia:Helferlein/VBA-Macro_for_EXCEL_tableconversion
 
J

Joel

SunRace said:
I need help…. I am trying to create document classification macro for Excel.
Macro is ready and it gets activated on SaveAs. Now what I want to do is – I
want to save this macro as excel Add-In so that I can install this in all
users login. I am following instructions from following article. Add-in is
getting configured. But the problem which I am facing is when I do save as in
new workbook - the macro or add-in does not get activated. What I could
understand was that the macro is limited to the workbook in which it was
created. How do I make this macro/add-in global for all active and new
workbooks which I will create?

http://de.wikipedia.org/wiki/Wikipedia:Helferlein/VBA-Macro_for_EXCEL_tableconversion
 
J

Joel

The trick is assigning variables for each workbook that is opened. I don't
know which workbook contains the macro from your description. The SAVEAS
event I believe will only work when you are saving the file where the AVEAS
event is located.

1) You can always refere to the workbook where the macro is located as
Thisworkbook.

2) Just after you open a workbook set a vaiable to the new workbook. this
way you can refferrence the newbk when ever you need to look at the newbk.

workbooks.open Filename:="xyz.xls"
set newbk = activeworkbook

I think the best way is to put the
 
J

Joel

This may be a dumb answer, but why don't you just save the new file
immediately after you open the file. Then the new workbook is no longer a
new workbook it is an old workbook

Another option is to check if the file exists before opening the file. if
the file doesn't exist, then create the newworkbook using a file copy (from
scripting object). When you open the workbook it is not a new workbook.
 
B

Bob Phillips

Try referring to Activeworkbook in your addin code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
S

SunRace

Hello,

Here is my code....I dont have activeworkbook any where...
----
Public Sub MacroR()
'
' MacroR Macro
' Macro recorded 28/01/2008 by Suraj.Jadhav
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Document Classification: Restricted"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
'.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

Sub MacroC()
'
' MacroR Macro
' Macro recorded 28/01/2008 by Suraj.Jadhav
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Document Classification: Confidential"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
'.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
Sub MacroI()
'
' MacroR Macro
' Macro recorded 28/01/2008 by Suraj.Jadhav
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Document Classification: Internal"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
'.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
Sub MacroP()
'
' MacroR Macro
' Macro recorded 28/01/2008 by Suraj.Jadhav
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Document Classification: Public"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
'.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
-----

-----

Private Sub CommandButton1_Click()

'ShowSaveAsDialog

Unload Me
End Sub
Sub ShowSaveAsDialog()
Dim dlgSaveAs As FileDialog
Set dlgSaveAs =
Application.FileDialog(FileDialogType:=msoFileDialogSaveAs)

dlgSaveAs.Show
dlgSaveAs.Execute
' If Windows(2).Active = False Then Windows(2).Activate
End Sub

Private Sub OptionButton1_Click()
Call MacroC
End Sub

Private Sub OptionButton2_Click()
Call MacroI
End Sub

Private Sub OptionButton3_Click()
Call MacroP
End Sub

Private Sub OptionButton4_Click()
Call MacroR
End Sub

Private Sub UserForm_Click()

End Sub
-----
-----
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
Place_Footer.Show
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top