Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Copy Macro Module from one worksheet to another and then assign it

    Hi All,

    I have managed to copy a macro module form one master workbook to another via the below code, my issue is i then want to create a sheet called statements which then has a form control box which has the macro assigned.

    When i assign the macro it is assinging it from the master macro file so when a user opens it on the network they cant access the local master file:

    Sub STATEMENTS_COPY()

    Const MODULE_NAME As String = "STATEMENTS_MODULE" 'name of module to transfer
    Const TEMPFILE As String = "C:\IDEA\IDEA EXCEL MACROS.bas" 'temp textfile

    Name = "Global Extract All - " & Format(Now, "DD-MM-YYYY") & ".XLS"

    Workbooks.Open Filename:="\\UKFILE01\CKSCCA$\Global Statements\Global Extract All - " & Format(Now, "DD-MM-YYYY") & ".XLS"

    Workbooks(Name).Activate
    On Error Resume Next

    ThisWorkbook.VBProject.VBComponents(MODULE_NAME).E xport TEMPFILE
    Workbooks(Name).VBProject.VBComponents.Import TEMPFILE

    Kill TEMPFILE

    Windows("Global Extract All - " & Format(Now, "DD-MM-YYYY") & ".XLS").Activate


    Sheets.Add.Name = "Click For Statement"
    ActiveSheet.Buttons.Add(34.5, 24, 666, 203.25).Select
    Selection.OnAction = "STATEMENTS"
    Selection.Characters.Text = "Click To Generate Statement"
    With Selection.Characters(Start:=1, Length:=27).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    End With
    Range("A1").Select

    ActiveWorkbook.Save

    Excel.Application.Quit

    End Sub


    Any ideas what i could do?

    Thanks

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maybe another approach would work better.

    Create a workbook with nothing but the macro.
    Include in the macro the capability to open/create data workbooks.

    Now if the code needs changing it only needs to be changed in one place. You can create as many data workbooks as necessary and each will be smaller because it won't store the macro. I do this all the time as a basic template for many needs.
    DataMenu.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I would suggest including the workbook name in the OnAction property:
    Code:
    Sub STATEMENTS_COPY()
    
       Const MODULE_NAME          As String = "STATEMENTS_MODULE"   'name of module to transfer
       Const TEMPFILE             As String = "C:\IDEA\IDEA EXCEL MACROS.bas"   'temp textfile
       Dim wb                     As Workbook
       Dim btn                    As Button
    
       Name = "Global Extract All - " & Format(Now, "DD-MM-YYYY") & ".XLS"
    
       Set wb = Workbooks.Open(Filename:="\\UKFILE01\CKSCCA$\Global Statements\Global Extract All - " & Format(Now, "DD-MM-YYYY") & ".XLS")
    
       On Error Resume Next
    
       ThisWorkbook.VBProject.VBComponents(MODULE_NAME).Export TEMPFILE
       wb.VBProject.VBComponents.Import TEMPFILE
    
       Kill TEMPFILE
    
       With wb
          .Sheets.Add.Name = "Click For Statement"
          Set btn = .ActiveSheet.Buttons.Add(34.5, 24, 666, 203.25)
       End With
       With btn
          .OnAction = "'" & wb.Name & "'!STATEMENTS"
          .Caption = "Click To Generate Statement"
          With .Font
             .Name = "Arial"
             .FontStyle = "Regular"
             .Size = 10
          End With
       End With
       wb.Save
    
       Excel.Application.Quit
    
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Rory,

    Once again you have come to my rescue, we have a number or programs that run a number of reports and then spit out an unformatted version in excel which then need macros inbedded into them so the user can proceed with.

    Now on this sheet my user can click the macro and it prompts them for an account numbder for them to proceed.

    you are a god of the arena.

    Thanks

    Dean

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Dean,
    You're welcome!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •