Workbooks not opening Stop method

[Todd Huttenstine]

hey guys I have a really strange problem.

If Excel program is not running and I double click an
Excel file, it does not come up. I have a userform that
comes up instead and even after I close out of the
userform, the workbook I double clicked still does not
come up.

Heres what I have...
I have a file in my XLStart folder that has a workbook
initialize event. In this workbook initialize event it
opens up a userform. In the userform initialize I have
the following code:

Private Sub UserForm_Initialize()
Dim WB As Workbook
Dim UpdateFlag
Dim Res As Long
Dim UName As String * 255
Dim NN
Dim L As Long: L = 255
Dim counter As Long
Dim FndRng
'Code below if answer is NO
Res = GetUserName(UName, L)
UName = Left$(UName, L - 1)
NN = Trim(UName)

Application.ScreenUpdating = False
If bIsBookOpen("Administrator Setup.xls") Then
GoTo SkipOpen
Else
Set WB = Workbooks.Open("Q:\CS Management Reports\Reports
Setup\Administrator Files\Administrator Setup.xls")
'Windows("Book3").Activate
End If

SkipOpen:
counter = 1
Do
counter = counter + 1
'Calls the Closed workbook reference code to be used in
the match loop
p = "Q:\CS Management Reports\Reports
Setup\Administrator Files"
f = "Administrator Setup.xls"
s = "Reports Setup"
a = ("B" & counter)
'If 5 and 3 MATCHES an Authorized ID, perform found action
RemoteValue = GetValue(p, f, s, a)
If NN = RemoteValue Then
'Code to change Update Flag from Off to On (0 to 1)
'Finds the destination cell to change
FndRng = Workbooks("Administrator Setup.xls").Sheets
("Reports Setup").Range("B:B").Find(NN).Offset(0, 2).Value
Application.ScreenUpdating = True
If FndRng = 1 Then
OptionButton2.Value = True
Else
OptionButton1.Value = True
End If

'This is where it does not open the workbook
Stop

Exit Do
Else
End If
Loop Until counter = 100
'End loop
Application.ScreenUpdating = False
Workbooks("Administrator Setup.xls").Close
Application.ScreenUpdating = True
End Sub


Now as you can see right before the exit do statment I
have the Stop command. This stops the code from
executing. If the stop is not there, I experience the
problem. If the stop IS THERE, the program pauses but
once I hit CONTINUE, the code finishes executing like it
should and after the userform pops up, so does the
workbook I double clicked on.

I have insert the Stop command everywhere. I have played
with it so I know that where I have it now is the furthest
down it can go without my workbook not opening. If I were
to move the Stop command down one more line to where it
follows the Exit Do statment, then whatever workbook I
double clicked will not open. Problem happens ONLY when I
double click an Excel file when Excel is NOT running.


What could cause this problem and how do I resolve it?


Thank you
Todd huttenstine

 

[Dave Peterson]

I'm not sure what's happening, but it looks like your code checks to see if a
workbook is open. If it isn't, then your code opens it.

Then it looks like you use John Walkenbach's GetValue function to retrieve from
the workbook that you just opened. (why not just grab the value from the open
workbook?)

And something else that looks like a possible problem:

What happens if this line doesn't work--the value can't be found:

FndRng = Workbooks("Administrator Setup.xls").Sheets("Reports Setup") _
.Range("B:B").Find(NN).Offset(0, 2).Value

And I think I'd spend some time dimming my variables. FndRng looks much more
like a range variable than a simple variable that holds a value.

Lots of people write their finds like:

dim FoundCell as range
set foundcell = Workbooks("Administrator Setup.xls").Sheets("Reports Setup") _
.Range("B:B").Find(NN)
if foundcell is nothing then
'what happens then?
else
fndRng = foundcell.offset(0,2).value
end if
'(I don't like fndrng as a variable name here!)

And watch out. I try to explicitly include all the parms used in the .Find
command. xl will remember the last settings and use these.