+ Reply to Thread
Results 1 to 12 of 12

vba excel - Prevent opening workbook with duplicate vba code but different file names

  1. #1
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    vba excel - Prevent opening workbook with duplicate vba code but different file names

    I have a workbook with a vba project that gathers input and saves the workbook as a new file name. My Workbook contains VBA userforms and opens on the same worksheet everytime.

    I want to prevent opening of the original workbook if:
    1.) if the original workbook is already open
    2.) if the workbook with a new file name is opened (which contains the same vba code within as the original workbook)

    Is there someway to perhaps check upon opening akin to:

    Please Login or Register  to view this content.
    I know the syntax is incorrect, but that's the gist.

  2. #2
    Registered User
    Join Date
    06-26-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    22

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    Please Login or Register  to view this content.
    Checking to see if a workbook with a particular name is no problem. I believe checking to see if the workbook with a new file name is open will require you to manufacture some means of identification. You could have a hidden sheet with a particular name, a particular set of sheets, a cell comment in a particular place with a particular value, a cell value of a particular cell, some unique metadata such as author or notes. At least, that's the case to the best of my knowledge. There may be some possibility to check for code by reading the xml from an open workbook, but I don't know how right offhand.

  3. #3
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    Thanks for the help.

    Trying to open another workbook (with the same name) while it is open doesn't seem to affect it. I've found this is because a userform routine is always running at any given time, thereby prohibiting an identical workbook from opening while it is all ready open.

    That said, since the issue is narrowed down to the workbook being opened (if both workbooks are different names), using sheet identification is the best route to test if a workbook holding the vba project is already open.

    Every workbook project has the same set of worksheets with the same name (all sheets save one are hidden). The worksheet names never change from original to new workbook and tabs are disabled so the user never interacts with any of the sheets. What would the correct syntax to indicate a sheet comparison would be (using say Sheet1 as an example)?

    Would it just be as simple as wsT1 as worksheet? Then using the loop to compare the sheets if wsT1.Name = "Sheet1" Then

    How would I go about that?

  4. #4
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,017

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    Quote Originally Posted by terriertrip View Post
    ... I want to prevent opening of the original workbook if:
    1.) if the original workbook is already open
    2.) if the workbook with a new file name is opened (which contains the same vba code within as the original workbook)
    I understand that it is about a different instance of excel ?
    In the same instance of excel you will receive an information message confirming the reopening of the file ... the same file.

  5. #5
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    @porucha vevrku. #1 is not really an issue after testing some more. Since there is always a routine running within the workbook upon open, attempting to open the same workbook again does nothing. Not even a message. Excel will not open the workbook again.

    For example, I cannot have Userform5 from one instance running and then try and open another instance that tries to run Userform1, because both workbooks are of the same name, so somehow vba recognizes that and will not run 2 different forms. How it is doing this is either by a complete programming accident and way above my understanding.

    However, if I have changed the name of the workbook to a new name, and reopen the original workbook, that's where I run into problems. I can't have 2 different workbooks (in name only - however vba is identical for both) open at the same time.

  6. #6
    Registered User
    Join Date
    06-26-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    22

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    Thanks for the reply. There are nearly 40 sheets. Would I have to add every sheet? Or could I get away with just using a "key" sheet (my cover sheet for example) to quickly surmise if they are the same workbook?

    All sheet names are unique (but are never changed by the user since they are hidden). I don't use the default "Sheet1" naming convention, but name it "cvr" for Sheet1 as an example.

  8. #8
    Registered User
    Join Date
    06-26-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    22

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    Instead of adding them individually as I do above, you could loop through each ws in ThisWorkbook.worksheets and add each ws.name to the dictionary.

    However, I think a better option is just add some reasonable number of sheet names to the dictionary individually as above. Otherwise, you may run into version control issues down the road when you change the sheets in ThisWorkbook, yet your users may be using a version with the wrong number of sheets or different sheet names. In that case, you wouldn't achieve a 100% match and your error wouldn't be triggered.

  9. #9
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    I see where you're going with that, however upon code execution (first sub called on Workbook_Open event), the error is thrown even if there is no workbook open.

    Additionally, if I do have a renamed workbook open and I open an original workbook with added code, the error is thrown but the original workbook continues to open.

  10. #10
    Registered User
    Join Date
    06-26-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    22

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    I understand. My bad. I overlooked the fact that you're using the workbook_open event. This is a personal preference, but I avoid event based procedures such as workbook_open whenever possible so that code is only executed when the user initiates it. I don't know your circumstances, so maybe event based procedures are unavoidable for you. If there's something absolutely necessary to perform, such as ensuring the user has only one workbook of this type open at any given time, I usually write a sub like the one above and call it everywhere necessary. It should run near instantaneously, so there won't be any noticeable delay due to processing time. It's just one more line of code in your other subs or functions. In the situation I'm describing now, the user would be allowed to have two of these workbooks open simultaneously, but would be unable to run any procedures until only one of the workbooks of this type is open. Just a thought. Might not work with your application design.

    If you must use the workbook_open event...

    Regarding your last post's first sentence - If I understand correctly, you could use an if statement within your loop to not check for matching sheet names if wb = thisworkbook. The loop would still check every other open workbook. That should prevent the error being thrown even if there is no workbook open. In fact, now that I think of it, I suppose you should do this even if you're not using workbook_open event. Good catch.

    Regarding your last post's second sentence - This is a case only applicable to using the workbook_open event. In this case, you could just use the thisworkbook.close method immediately after your msgbox. Thisworkbook is the second workbook opened of this type and you want to close it.

  11. #11
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    This particular event based procedure is necessary for my application design and user interface, unfortunately. As I have it set, the code you supplied only needs to be called upon the open event. I have Activate and Deactivate events to deal with switching between workbooks. For the most part my users do not use any other macro-based files.

    I fooled around this weekend with your rec on adding the if statement specifically using the If Not wb Is ThisWorkbook Then. I tried inserting within the loop in various locations, but get the same result. That would be: The If Then suppresses the message, however if I have the revised wb open (with initial form running) and then open the original wb, the original wb still opens, with one nuance: the program (forms) do not execute for the original wb (a good thing).

    The project wb has a splash screen upon open (in addition to some window formatting: sizing, tabs, menu bar, etc.). If the revised wb is open (and running) and I open the original wb, the splash screen does not run, but the window formatting occurs. I believe this is due to the Activate event which formats the excel window a certain way. To verify some of this, using a msgbox in the original wb's workbook_open event (very first line), I can see that the original wb is not executing the open_event routine.

  12. #12
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - Prevent opening workbook with duplicate vba code but different file names

    I think I've somewhat isolated the problem. The Workbook_Open event does not appear to work because there is always a userform loaded, therefore any event based procedure is nullified by the routine already running. However, I've gotten closer to a desired result using Workbook_Deactivate routine which is weird since that is in itself event based. The Workbook_Deactivate is the only thing that seems to work while the userform is running. If you have any insight into how to address a couple of the issues caused by this process, I'm all ears.
    Please Login or Register  to view this content.
    1. if a blank workbook is opened as a test (Book1.xlsm), the message is thrown when Book1 becomes the Active workbook, but Book1 does not close (error trap allows it to step through)
    2. when closing the revised workbook, the message is thrown, then the workbook closes

    On a positive note, if the revised workbook is open and the original is opened, the message is thrown and the original workbook closes, so getting closer!

    EDIT:
    changing <> to Like fixes both issues above, however when the message is thrown if the original workbook is opened, the original workbook does not close. Help please. so close!
    Last edited by terriertrip; 07-03-2018 at 06:48 PM. Reason: Changed code

+ 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. [SOLVED] Macto to amend code to prevent opening up a duplicate file
    By flupsie in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-15-2018, 01:24 AM
  2. VBA to Lock and prevent opening of the excel file
    By Excelguruintraining in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2015, 02:26 PM
  3. [SOLVED] Upon opening file i get an excel alert message. How to prevent this?
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-24-2014, 09:11 AM
  4. Prevent other user opening excel file.
    By ebin charles in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-08-2014, 02:13 PM
  5. Prevent excel from Opening with a new workbook
    By jerrydixon in forum Excel General
    Replies: 1
    Last Post: 08-14-2008, 03:24 PM
  6. How to prevent opening a blank excel file
    By st24961 in forum Excel General
    Replies: 1
    Last Post: 05-09-2007, 10:22 PM
  7. Replies: 6
    Last Post: 03-02-2005, 11:53 AM

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