Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Macros in custom menu (Excel xp)

    I have a workbook with macros in it. In the original.xls I have a custom menu that has the 2 macros in it. I have "save as" code in the macro that saves the wkbook as a new .xls that the user will name.

    My problem is that after i run the macro and close everything out and re-open up my original workbook it references the macros from the new workbook instead of the original wkbook. This messes up some of my code. How can i get around this problem? Thank you

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macros in custom menu (Excel xp)

    I would not have them mess with the ORIGINAL file.
    Open the original, create a COPY for them to use, Have them saveas the copy. Then you won't screw around with the original.

    Alternately if these buttons are based on code, create an addin for the buttons, or put the code in personal.xls

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macros in custom menu (Excel xp)

    ok, thank you. How do i make a copy of the file using code while still in the original.xls? I thought i was making a copy by doing a "save as". Thank you for the help.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macros in custom menu (Excel xp)

    Add something like this to your personal.xls or to a button

    <pre>Sub NewCopy()
    Dim SourceFile, DestinationFile

    SourceFile = "C:test.xls"
    DestinationFile = "C:temp.xls"
    FileCopy SourceFile, DestinationFile

    Workbooks.Open DestinationFile

    Do
    fname = Application.GetSaveAsFilename
    Loop Until (fname <> False) And (fname <> DestinationFile)

    ActiveWorkbook.SaveAs FileName:=fname

    End Sub
    </pre>


    Steve

Posting Permissions

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