Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

Using a central macro base for all users

I generate workbooks for the timesheets of each staff member from a master workbook. This generated workbook contains some macros. Whenever I add/update the macros they are available for the staff in the following month only. I was wondering if there could be a system wherein each of the generated workbook contains a caller macro which calls a central macro in a file residing on the server so that any changes done during the month are immediately affected to all workbooks.

PS: All macros are event-fired. None of them are user-invoked. The events at the moment are worksheet_change and worksheet_selectionchange but may have to be expanded.
0
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
  • 3
  • 3
1 Solution
 
Rgonzo1971Commented:
Hi,

you could create an XL add-in with all procedures

refer all the events to the add-in subs, for the ones you don't yet use,  keep an empty sub

to call the add-in

 
Sub MyTest() 
    Application.Run ("myaddin.xla!mySelectionChange") 
End Sub 

Open in new window

Regards
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Is there a way for a macro to install/update the addin? I do not want to go myself to install the addin on all computers. Some people bring their own laptop and I might not be available to do the installation.
0
 
Rgonzo1971Commented:
Hi,

you could send your user a file with this code to install an add in

as workbook_open code

Sub CheckAndInstall()
   
    myAddInName = "Addin Name"
    File = "S:\AddinLocation\myAddin.xla"
    
    On Error Resume Next
    Result = AddIns(myAddInName).Installed
    On Error GoTo 0
                                            
    If IsEmpty(Result) Then
        Set myAddin = Application.AddIns.Add(Filename:=File)
        myAddin.Installed = True
        Application.AddIns(myAddInName).Installed = True
    ElseIf Application.AddIns(myAddInName).Installed = False Then
        Application.AddIns(myAddInName).Installed = True
    End If
End Sub

Open in new window


and then in the add-in this code (I've changed my mind on how to change the code)

Private Sub Workbook_Open()
    'rename the module
    ThisWorkbook.VBProject.VBComponents("Module1").Name = "ModuleTemp"
    'remove the module
    ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents("ModuleTemp")
    'Import the module
    ThisWorkbook.VBProject.VBComponents.Import ("f:\GlobalCode\Module1.bas")
    ThisWorkbook.VBProject.VBComponents(ThisWorkbook.VBProject.VBComponents.Count).Name = "Module1"
        

End Function

Open in new window

Regards
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Saqib Husain, SyedEngineerAuthor Commented:
Hi, Thanks for the effort. The looks are now good. But it is going to take e some time to test it. Please bear with me while I get the time to test it.

Saqib
0
 
Rgonzo1971Commented:
Hi,

if do not want to have your add-in centrally saved you could use

MsgBox Application.StartupPath

to get the startup folder of each user

Regards
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Sorry, I could not test it but it is time to close this now,

Thanks for everything.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now