Announcement

Collapse
No announcement yet.

restrict macro to one workbook

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • restrict macro to one workbook



    Hi
    I have a template file where I run a macro. When I save the template with another name I want to deactivate the macro for the new file. It that possible?

    Best regards
    Rune

  • #2
    Re: restrict macro to one workbook

    Hi Runes,
    Reading your message made me remember where I left off on another project where I had the same "problem", but never added that "check" feature.

    I guess you'd be able to have the macro test to see if it is asked to run in the one workbook where it is supposed to by checking the workbook's name and if the name is not matching with your criteria, exit the macro, otherwise perform the task on hand.
    HTH
    Stefan

    There maybe a different way to to this. My beginners brain would do this. ;-) And then again, i never played with Templates either.
    Code:
    Sub Test()
    If ThisWorkbook.Name = "test1.xls" Then
    MsgBox "Workbook name confirmed"
    ' Your code
    Else:
    MsgBox "Workbook name not confirmed"
    Exit Sub
    End If
    End Sub

    Comment


    • #3
      Re: restrict macro to one workbook

      As an additional thought, perhaps something as simple as
      Code:
      If ActiveWorkbook.Name <> "MyName" Then Exit Sub
      EDIT: And, I missed saying Welcome to OzGrid
      Last edited by thomach; October 19th, 2005, 09:20. Reason: Add Welcome
      Best Regards,
      Tom
      ---------------------------
      Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

      Comment


      • #4
        Re: restrict macro to one workbook

        Hi,

        I had the same problem very recently, which I managed to solve with the following code:
        Code:
        Option Explicit
        
        Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        
            Dim x As Object
            Dim endline As Integer
            
            If ActiveWorkbook.Name = "My_template_file.XLT" Then
                Exit Sub
            End If
            
            ' set default drive
            ChDrive "f"
            ChDir "My_save_path"
            
            ' delete buttons and macros
            ActiveSheet.Shapes("Button 3").Select
            Selection.Delete
            ActiveSheet.Shapes("Button 4").Select
            Selection.Delete
            
            Set x = Application.VBE.ActiveVBProject.VBComponents
            
            x.Remove vbcomponent:=x.Item("AddPage")
            x.Remove vbcomponent:=x.Item("SelectCurrency")
            x.Remove vbcomponent:=x.Item("ufSelectCurrency")
            
            Set x = Application.VBE.ActiveVBProject.VBComponents("ThisWorkbook").CodeModule
            With x
                endline = .countoflines
                .deletelines 1, endline
            End With
            
        End Sub
        First of all I delete the buttons on the template, then I delete the userform, the modules, and finally the code from the "ThisWorkbook" sheet, i.e. this code.

        Hope this helps.

        Comment


        • #5


          Re: restrict macro to one workbook

          Thank you all.
          I used the simply code to thomach, but I needed to include .xls after the file name then it works.

          Rune

          Comment

          Working...
          X