+ Reply to Thread
Results 1 to 4 of 4

VBA Code to disable only specific macro when saving backup file

Hybrid View

  1. #1
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,336

    VBA Code to disable only specific macro when saving backup file

    Hi All

    Have been searching the web to no avail.

    I have a Master file that generates a new order no on opening... I did not want to have to generate by pressing buttons.

    I would then want to save a backup of this file as macro enabled but only disabling the specific macro that generates a new order no and leaving the other macros available.

    Any ideas

  2. #2
    Valued Forum Contributor Kamboj's Avatar
    Join Date
    09-25-2014
    Location
    India
    MS-Off Ver
    2003 - 2010
    Posts
    430

    Re: VBA Code to disable only specific macro when saving backup file

    use this code
    This delete ("Module2")
    
    Sub DeleteMacro()
        Dim x As Integer
        On Error Resume Next
        With ActiveWorkbook.VBProject
            For x = .VBComponents.Count To 1 Step -1
                If .VBComponents(x).Name = "Module2" Then
                .VBComponents.Remove .VBComponents(x)
                End If
            Next x
        End With
    End Sub
    Last edited by Kamboj; 01-06-2016 at 08:55 AM. Reason: end code
    Kamboj
    _________________________________________________________________________________
    Mark the thread as SOLVED if my answer satisfy you.

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,336

    Re: VBA Code to disable only specific macro when saving backup file

    Hi Kamboj

    Thank you for your reply. I inserted the provided code as per below.....The workbook hangs when running the Save Macro.
    Any ideas?

    Sub Save_File()
    Dim Path As String
    Dim FName1 As String
    Dim FName2 As String
    Dim x As Integer
    ActiveSheet.Shapes("Shape1").Visible = False
    ActiveSheet.Shapes("Shape3").Visible = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Sheets(7).Visible = False
    
        On Error Resume Next
        With ActiveWorkbook.VBProject
            For x = .VBComponents.Count To 1 Step -1
                If .VBComponents(x).Name = "Module2" Then
                .VBComponents.Remove .VBComponents(x)
                End If
            Next x
        End With
    
    Path = "C:\Users\Steven\Desktop\Levant\Client backup\" & Stage1document.Range("C12")
    FName1 = Range("C12")
    FName2 = Range("C14")
    MkDir Path
    
    ActiveWorkbook.SaveAs Filename:=Path & "\" & FName1 & "-" & FName2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, WriteResPassword:="01012016"
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    If Application.Workbooks.Count = 1 Then
            
            Application.Quit
            
        Else
        
            ActiveWorkbook.Close
            
        End If
    
    End Sub

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,336

    Re: VBA Code to disable only specific macro when saving backup file

    Hi Kamboj

    I found the following code which works perfectly, however what can I add to the code that will first check if module exists to remove and if not, to do nothing.


    Sub Delete_Module()
    Dim vbCom As Object
    
    Set vbCom = Application.VBE.ActiveVBProject.VBComponents
    
    vbCom.Remove VBComponent:= _
    vbCom.Item("Module1")
    
    End Sub
    Last edited by sintek; 01-06-2016 at 11:27 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Saving a Backup as a Different File
    By Vlade777 in forum Excel General
    Replies: 2
    Last Post: 01-30-2014, 09:52 AM
  2. [SOLVED] Shared file locks for saving when a specific macro is run
    By Sleepyshy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2013, 04:37 PM
  3. Saving Backup Copies before Updateing File
    By kjsconv in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2013, 09:12 PM
  4. Replies: 6
    Last Post: 03-07-2012, 04:56 PM
  5. Saving a File with specific file name using Macro
    By LstarS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-19-2009, 06:13 AM
  6. Auto Saving Via Macro To Specific File?
    By stevesunfold in forum Excel General
    Replies: 4
    Last Post: 04-11-2008, 11:57 AM
  7. Saving backup file on file open
    By AmyTaylor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2006, 10:48 AM
  8. Code to select 'Disable Macro' when opening an EXTERNAL FILE
    By G rumpy O ld D uffer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2005, 02:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1