Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: restrict macro to one workbook

  1. #1
    Join Date
    19th October 2005
    Posts
    31

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th July 2005
    Posts
    214

    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.
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: restrict macro to one workbook

    As an additional thought, perhaps something as simple as
    VB:
    If ActiveWorkbook.Name <> "MyName" Then Exit Sub 
    
    
    EDIT: And, I missed saying Welcome to OzGrid
    Last edited by thomach; October 19th, 2005 at 10: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.

  4. #4
    Join Date
    14th October 2005
    Location
    Brussels, Belgium
    Posts
    48

    Re: restrict macro to one workbook

    Hi,

    I had the same problem very recently, which I managed to solve with the following code:
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th October 2005
    Posts
    31

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Restrict Workbook Access To One Computer
    By DOC in forum EXCEL HELP
    Replies: 13
    Last Post: October 3rd, 2012, 09:59
  2. Restrict Cell Value Macro
    By Gon in forum EXCEL HELP
    Replies: 7
    Last Post: December 20th, 2006, 09:41
  3. Replies: 3
    Last Post: October 3rd, 2006, 03:20
  4. Restrict the opening of a Workbook
    By Ray Drayson in forum EXCEL HELP
    Replies: 5
    Last Post: February 20th, 2006, 23:07
  5. VBA: Restrict access to workbook
    By Pesky Weasel in forum OPEN SOURCE: Hey! That is Cool!
    Replies: 0
    Last Post: September 23rd, 2003, 15:00

Bookmarks

Posting Permissions

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