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.

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.

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?

Originally Posted by terriertrip

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

@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.

Please Login or Register  to view this content.

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.

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.

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.

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.

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.