Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting a macro upon save (Excel 97)

    Hi,

    I don't know if this is possible. I have created an Excel workbook which myself and several colleagues will be using as a crude template. Each of us look after a different business area and I have added a macro to ThisWorkbook, Open to prompt the user to identify themselves.
    This then populates a cell with the business area name for that person.
    Each of us will on a weekly basis save this document with a new filename. I then no longer want the Open event to run.
    Is there some way of adding some code to the BeforeSave event to delete the Open event macro (i.e. create a macro which removes another macro)?

    Let's say the Open event macro is called 'Prompt'. Can anyone help?

    Many thanks,

    Cunners

  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: Deleting a macro upon save (Excel 97)

    You could just add a line to check the file name, this will disable it once the name is changed (though not delete it).

    <pre>Private Sub Workbook_Open()
    If UCase(ThisWorkbook.Name) = "MYTEMPLATE.XLS" Then
    'Your code here
    End If
    End Sub</pre>


    Chip Pearson has some code which can delete code in a module if you want to still go that route.

    Steve

  3. #3
    New Lounger
    Join Date
    May 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting a macro upon save (Excel 97)

    Many thanks,

    I hadn't thought of taking that route. Works like a peach!

    Cunners

Posting Permissions

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