Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make global Macro/Add-in for all active Workbooks/Sheets
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/Wikiped...ableconversion |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make global Macro/Add-in for all active Workbooks/Sheets
"SunRace" wrote: 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/Wikiped...ableconversion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make global Macro/Add-in for all active Workbooks/Sheets
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 "SunRace" wrote: 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/Wikiped...ableconversion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make global Macro/Add-in for all active Workbooks/Sheets
How we created add-in of any macro which can work for any new workbook or
existing? I tried this also but this works only incase of new workbook...it does not work if I open old workbook...http://www.vbforums.com/showthread.php?t=348490 and trying this now....http://www.contextures.com/xlToolbar02.html "Joel" wrote: 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 "SunRace" wrote: 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/Wikiped...ableconversion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make global Macro/Add-in for all active Workbooks/Sheets
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. "SunRace" wrote: How we created add-in of any macro which can work for any new workbook or existing? I tried this also but this works only incase of new workbook...it does not work if I open old workbook...http://www.vbforums.com/showthread.php?t=348490 and trying this now....http://www.contextures.com/xlToolbar02.html "Joel" wrote: 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 "SunRace" wrote: 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/Wikiped...ableconversion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make global Macro/Add-in for all active Workbooks/Sheets
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) "SunRace" wrote in message ... How we created add-in of any macro which can work for any new workbook or existing? I tried this also but this works only incase of new workbook...it does not work if I open old workbook...http://www.vbforums.com/showthread.php?t=348490 and trying this now....http://www.contextures.com/xlToolbar02.html "Joel" wrote: 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 "SunRace" wrote: 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/Wikiped...ableconversion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make global Macro/Add-in for all active Workbooks/Sheets
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:=msoFileDial ogSaveAs) 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 ----- "Bob Phillips" wrote: 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) "SunRace" wrote in message ... How we created add-in of any macro which can work for any new workbook or existing? I tried this also but this works only incase of new workbook...it does not work if I open old workbook...http://www.vbforums.com/showthread.php?t=348490 and trying this now....http://www.contextures.com/xlToolbar02.html "Joel" wrote: 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 "SunRace" wrote: 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/Wikiped...ableconversion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to find cell content in sheets and make sheet active | Excel Discussion (Misc queries) | |||
Run macro when Sheets are active | Excel Discussion (Misc queries) | |||
group workbooks/files to make global changes? | New Users to Excel | |||
make cell active in beginning of macro | Excel Programming | |||
How do I make a 'global' macro? | Excel Programming |