Disable Workbook_open () after Saving As a new file?

afc171

Board Regular
Joined
Jan 14, 2017
Messages
143
Office Version
  1. 2013
Platform
  1. Windows
Hi guys,
I have a workbook that when opening it compares a cell to another workbook to make sure it is the latest version number one that is running.
The issue I have is this sheet goes saved as a new workbook which then when you open runs the same code. I need to save it as macro enabled as it has a print button.
Is it possible to disable/not run the workbook_open on a newly saved sheet?

Thanks

VBA Code:
Sub Workbook_Open()
    Dim WS As Worksheet
    Sheets("QR9 Order Summary").Activate
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .StatusBar = "Checking Version.  Please wait..."
    End With
    Workbooks.Open "C:\Users\ad\Desktop\LC_Version.xlsx", , True
    Workbooks("Consumables Mastersheet V2.6 LC.xlsm").Activate
    If Workbooks("Consumables Mastersheet V2.6 LC.xlsm").Sheets("QR9 Order Summary").Range("S3").Value <> Workbooks("LC_Version.xlsx").Sheets("Version").Range("A2").Value Then
        Application.StatusBar = "This is an old version of 'Consumables Mastersheet'.  Please notify manager."
itype = vbAbort
ireply = MsgBox("This is an old version of 'Consumables Mastersheet'. Please notify manager!", vbCritical, "Old Version")

    Application.DisplayAlerts = False
    Application.Quit
        Workbooks("LC_Version.xlsx").Close SaveChanges:=False
        With Application
            .EnableEvents = True
            .StatusBar = ""
        End With
        Workbooks("Consumables Mastersheet V2.6 LC.xlsm").Close SaveChanges:=False
        Application.Quit
    Else
    Application.StatusBar = "Version Match Please Continue"
    Application.Wait (Now + TimeValue("0:00:02"))
    End If
    Workbooks("LC_Version.xlsx").Close SaveChanges:=False
    Application.StatusBar = ""
        
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Nevermind, figured it out by using code below at the beginning. Although might have to stop using the version number in the file name ;)

VBA Code:
If ThisWorkbook.Name <> "Consumables Mastersheet V2.6 LC.xlsm" Then Exit Sub

Actually if someone has a better idea on how to look up a value without using another spreadsheet to lookup a number somewhere that might be better.
I just need people to stop copying the actual file (and use a desktop shortcut instead) and running an older version from what is saved in a folder where it should be opened from.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,958
Messages
6,122,475
Members
449,087
Latest member
RExcelSearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top