Copy of Excel File won't open correctly based upon new values in "this Workbook"

BROOKLYN1950
BROOKLYN1950 used Ask the Experts™
on
I have an Excel application based upon a single file in a windows 2008 server environment.

The user does his thing and I use a savecopyas, which saves the file to a different location, with a different name, and a new value_open such as 1, which the This Workbook object is supposed to interpret to know which screen to open up with.  Further on down the process there is a second savecopyas, to a new location, new file name, with  a new opening value set.  the original file is now closed without saving.

No matter what i do, the second saved file always opens with an application defined error message when opened by a different user using thier own terminal services log in. .  It doesn't seem to evaluate the if statement in the This workbook object at all.

I have tried many options to get the This Workbook to close once the If statement has been satisfied, but it seems to always try to run a portion of the next staement, despite it being false.

----------------------------------------------------------------------------------------------------------------------------
Option Explicit
Public Sub workbook_open()
Dim open_value As Range
Dim TC As Range

Set TC = Range("SelectedSheet")
Set open_value = Range("open_value")
'Application.VBE.MainWindow.Visible = True
ThisWorkbook.Activate

Application.ScreenUpdating = False

Sheets("load").Visible = True ' this opens up a display sheet so Excel has a sheet visible
Sheets("sigsubmit").Visible = True ' this opens up the sheet which contains the open_value
Call weeks_monday 'sets the current week for drop down menu

If open_value.Value = 1 Then
Sheets("sigsubmit").Visible = False
ADMIN.Show
Exit Sub
End If

If open_value.Text = "BLANK" Then existing_Job
If open_value.Text = "JOBUPDATE" Then UpdateSiteInfo.Show
Sheets("sigsubmit").Visible = False

End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How does the code you posted relate to your question? I don't see any "save" code.

Which 'If' are you referring to?

Do you know how to use Debug to set a breakpoint and then step through the code to see what is happening line by line? If not then you might want to look at this article that I wrote. It's aimed at VB6 but most of it applies to VBA as well.

Author

Commented:
The save code is part of a different module.

THe if statement is the
If open_value.Value = 1 Then
listed above in my code.

I  put a breakpoint in the code in the This workbook object, saved the file so it would open the editor but it wouldn't save.  Are theere limitaions and restrictions to the This wokbook onject?
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Put a breakpoint on the If open_value.Value = 1 line.  Then put the cursor over open_value.Value. Is it '1' ?

Author

Commented:
As i explained above, I  put a breakpoint in the This workbook object.  after I saved it, the file opened again without the breakpoint.
"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I'm not sure what you are telling me.

If you are telling me that you added a breakpoint and then closed the workbook expecting to still have a breakpoint when you reopened the workbook, that's not the way it works. Breakpoints are temporary and they are not saved when you close and reopen a workbook. In order to test what's happening you need to place the breakpoint in the code before the code is executed.

If you are telling me that you have a breakpoint and the code didn't stop there then that means that there is some condition further up in the code that is causing the line that the breakpoint is on to be skipped.

Author

Commented:
Ah,

Thats the problem, the code is being executed from the this workbook object, in essence giving the file the instructions on how to open.  The code bombs before the file is finished openiing so i can't debug it.

Author

Commented:
The code is in the Public sub Workbook_open() in the This workbook object
Most Valuable Expert 2011
Awarded 2010

Commented:
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I believe he is saying in post ID: 37866842 that he found the answer based on my comments.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial