Hello all:
I have a big (lots of code) multipage userform that asks a user for a
password which is on a list in a hidden sheet, in the first column. This
list was previously populated, randomized, by another form, and is different
for each occasion.

When the password is entered the form loops through the first column looking
for a match. If one is found, the form is populated with any existing data
from that row on the sheet, and then allows the user to step through 14 pages
of survey, or save for later at any point in the process. Here's the problem:

The form runs perfectly on my computer (the actual file sits on the public
drive, accessible by all authorized users), but when anyone else tries to run
it, it fails and crashes Excel. All of us are using Excel 2003. If I sit
down at that user's computer, open VBE and step through the procedure to find
the trouble, it goes all the way through and opens the form very nicely.
Further, if I then save it, that user can open it and run it at normal speed
with no trouble - everything works!

It works until I do the same thing from someone else's computer and then
only that person can run it. Except that I can still run it from my
workstation.

So, any hints? Why would step-through work when run won't, and why would
that temporarily fix it, and what does that mean about where to look for the
trouble?

I'll add in the code for the loop here, because that would seem to be the
most likely trouble spot, though it could easily be somewhere else.


Private Sub cmdEnterPassword_Click()
' Check to see if the password is in the evaluation file
Application.ScreenUpdating = False
If txtPassword.Value = "" Then 'Check for blanks
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

'Unprotect the sheet and open the hidden sheet to look at passwords
ActiveSheet.Unprotect ("appraisal")
ActiveWorkbook.Sheets("Data").Activate

'Start from the top
Range("A1").Select

Do
If txtPassword.Value = "" Then
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

If ActiveCell.Value = txtPassword Then

Application.ScreenUpdating = True

ElseIf ActiveCell.Value = 0 Then
'Go back to the cover sheet
ActiveWorkbook.Sheets("Start").Activate
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")

End

'If the entry is nonzero but not found yet, keep looking

ElseIf ActiveCell.Value < txtPassword Then
ActiveCell.Offset(1, 0).Select

End If
Loop Until ActiveCell.Value = txtPassword



Any suggestions will be very welcome!

Regards,
Othello

I had a very similar problem with a program I was running. I didn't
find that the saving of the workbook "fixed" the problem until I was
about ready to give up. Still to this day no one can give me an answer
as to what the exact problem was.

My program was a 6 form wizard that built a budget for our company.
The first three forms ran fine, until the user pressed the next button
to head to the fourth form where I too had a Multipage. When the user
presses next, there was about a 70% Excel crash (not a VBA crash). I
found that if I also went to the users machine where the crash occured
and stepped through it, the crash would NEVER happen, but at full
speed, it would crash. I ended up "fixing" it the cheezy way and just
adding:

'Lets save this thing to work around the problem...
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

It works and now we are using the program, but I still want to find a
real fix for this problem. My code (which is too long to post here)
did closing functions for the third form and initialization and
activation for the fourth form. During the initialization and
activation, it loaded information for 40 textboxes, 24 of which had
change functions.

Anyway, I will be watching this post for any new information.

theSquirrel



Othello wrote:
Hello all:
I have a big (lots of code) multipage userform that asks a user for a
password which is on a list in a hidden sheet, in the first column. This
list was previously populated, randomized, by another form, and is different
for each occasion.

When the password is entered the form loops through the first column looking
for a match. If one is found, the form is populated with any existing data
from that row on the sheet, and then allows the user to step through 14 pages
of survey, or save for later at any point in the process. Here's the problem:

The form runs perfectly on my computer (the actual file sits on the public
drive, accessible by all authorized users), but when anyone else tries to run
it, it fails and crashes Excel. All of us are using Excel 2003. If I sit
down at that user's computer, open VBE and step through the procedure to find
the trouble, it goes all the way through and opens the form very nicely.
Further, if I then save it, that user can open it and run it at normal speed
with no trouble - everything works!

It works until I do the same thing from someone else's computer and then
only that person can run it. Except that I can still run it from my
workstation.

So, any hints? Why would step-through work when run won't, and why would
that temporarily fix it, and what does that mean about where to look for the
trouble?

I'll add in the code for the loop here, because that would seem to be the
most likely trouble spot, though it could easily be somewhere else.


Private Sub cmdEnterPassword_Click()
' Check to see if the password is in the evaluation file
Application.ScreenUpdating = False
If txtPassword.Value = "" Then 'Check for blanks
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

'Unprotect the sheet and open the hidden sheet to look at passwords
ActiveSheet.Unprotect ("appraisal")
ActiveWorkbook.Sheets("Data").Activate

'Start from the top
Range("A1").Select

Do
If txtPassword.Value = "" Then
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

If ActiveCell.Value = txtPassword Then

Application.ScreenUpdating = True

ElseIf ActiveCell.Value = 0 Then
'Go back to the cover sheet
ActiveWorkbook.Sheets("Start").Activate
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")

End

'If the entry is nonzero but not found yet, keep looking

ElseIf ActiveCell.Value < txtPassword Then
ActiveCell.Offset(1, 0).Select

End If
Loop Until ActiveCell.Value = txtPassword



Any suggestions will be very welcome!

Regards,
Othello

I made a copy of my code if anyone was interested to see it...

Its the full Next button code, form initialize and form activation
along with the helper functions

http://home.socal.rr.com/wowggw/Stuff/Example.bas


On Oct 31, 10:27 am, "
wrote:
I had a very similar problem with a program I was running. I didn't
find that the saving of the workbook "fixed" the problem until I was
about ready to give up. Still to this day no one can give me an answer
as to what the exact problem was.

My program was a 6 form wizard that built a budget for our company.
The first three forms ran fine, until the user pressed the next button
to head to the fourth form where I too had a Multipage. When the user
presses next, there was about a 70% Excel crash (not a VBA crash). I
found that if I also went to the users machine where the crash occured
and stepped through it, the crash would NEVER happen, but at full
speed, it would crash. I ended up "fixing" it the cheezy way and just
adding:

'Lets save this thing to work around the problem...
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

It works and now we are using the program, but I still want to find a
real fix for this problem. My code (which is too long to post here)
did closing functions for the third form and initialization and
activation for the fourth form. During the initialization and
activation, it loaded information for 40 textboxes, 24 of which had
change functions.

Anyway, I will be watching this post for any new information.

theSquirrel



Othello wrote:
Hello all:
I have a big (lots of code) multipage userform that asks a user for a
password which is on a list in a hidden sheet, in the first column. This
list was previously populated, randomized, by another form, and is different
for each occasion.

When the password is entered the form loops through the first column looking
for a match. If one is found, the form is populated with any existing data
from that row on the sheet, and then allows the user to step through 14 pages
of survey, or save for later at any point in the process. Here's the problem:

The form runs perfectly on my computer (the actual file sits on the public
drive, accessible by all authorized users), but when anyone else tries to run
it, it fails and crashes Excel. All of us are using Excel 2003. If I sit
down at that user's computer, open VBE and step through the procedure to find
the trouble, it goes all the way through and opens the form very nicely.
Further, if I then save it, that user can open it and run it at normal speed
with no trouble - everything works!

It works until I do the same thing from someone else's computer and then
only that person can run it. Except that I can still run it from my
workstation.

So, any hints? Why would step-through work when run won't, and why would
that temporarily fix it, and what does that mean about where to look for the
trouble?

I'll add in the code for the loop here, because that would seem to be the
most likely trouble spot, though it could easily be somewhere else.

Private Sub cmdEnterPassword_Click()
' Check to see if the password is in the evaluation file
Application.ScreenUpdating = False
If txtPassword.Value = "" Then 'Check for blanks
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

'Unprotect the sheet and open the hidden sheet to look at passwords
ActiveSheet.Unprotect ("appraisal")
ActiveWorkbook.Sheets("Data").Activate

'Start from the top
Range("A1").Select

Do
If txtPassword.Value = "" Then
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

If ActiveCell.Value = txtPassword Then

Application.ScreenUpdating = True

ElseIf ActiveCell.Value = 0 Then
'Go back to the cover sheet
ActiveWorkbook.Sheets("Start").Activate
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")

End

'If the entry is nonzero but not found yet, keep looking

ElseIf ActiveCell.Value < txtPassword Then
ActiveCell.Offset(1, 0).Select

End If
Loop Until ActiveCell.Value = txtPassword

Any suggestions will be very welcome!

Regards,
Othello- Hide quoted text -- Show quoted text -

Well, I'll be switched!

That worked like a charm, squirrel, and I Heaven knows how long it would
have taken me to think of saving as a work-around, even though I already had
the clue of haveing to save it at each person's machine to make it work.

Is there a way to get somebody at Microsoft to look into this without
springing for big fees?

Anyway, thanks a ton.


Regards,
Othello

" wrote:

I had a very similar problem with a program I was running. I didn't
find that the saving of the workbook "fixed" the problem until I was
about ready to give up. Still to this day no one can give me an answer
as to what the exact problem was.

My program was a 6 form wizard that built a budget for our company.
The first three forms ran fine, until the user pressed the next button
to head to the fourth form where I too had a Multipage. When the user
presses next, there was about a 70% Excel crash (not a VBA crash). I
found that if I also went to the users machine where the crash occured
and stepped through it, the crash would NEVER happen, but at full
speed, it would crash. I ended up "fixing" it the cheezy way and just
adding:

'Lets save this thing to work around the problem...
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

It works and now we are using the program, but I still want to find a
real fix for this problem. My code (which is too long to post here)
did closing functions for the third form and initialization and
activation for the fourth form. During the initialization and
activation, it loaded information for 40 textboxes, 24 of which had
change functions.

Anyway, I will be watching this post for any new information.

theSquirrel



Othello wrote:
Hello all:
I have a big (lots of code) multipage userform that asks a user for a
password which is on a list in a hidden sheet, in the first column. This
list was previously populated, randomized, by another form, and is different
for each occasion.

When the password is entered the form loops through the first column looking
for a match. If one is found, the form is populated with any existing data
from that row on the sheet, and then allows the user to step through 14 pages
of survey, or save for later at any point in the process. Here's the problem:

The form runs perfectly on my computer (the actual file sits on the public
drive, accessible by all authorized users), but when anyone else tries to run
it, it fails and crashes Excel. All of us are using Excel 2003. If I sit
down at that user's computer, open VBE and step through the procedure to find
the trouble, it goes all the way through and opens the form very nicely.
Further, if I then save it, that user can open it and run it at normal speed
with no trouble - everything works!

It works until I do the same thing from someone else's computer and then
only that person can run it. Except that I can still run it from my
workstation.

So, any hints? Why would step-through work when run won't, and why would
that temporarily fix it, and what does that mean about where to look for the
trouble?

I'll add in the code for the loop here, because that would seem to be the
most likely trouble spot, though it could easily be somewhere else.


Private Sub cmdEnterPassword_Click()
' Check to see if the password is in the evaluation file
Application.ScreenUpdating = False
If txtPassword.Value = "" Then 'Check for blanks
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

'Unprotect the sheet and open the hidden sheet to look at passwords
ActiveSheet.Unprotect ("appraisal")
ActiveWorkbook.Sheets("Data").Activate

'Start from the top
Range("A1").Select

Do
If txtPassword.Value = "" Then
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

If ActiveCell.Value = txtPassword Then

Application.ScreenUpdating = True

ElseIf ActiveCell.Value = 0 Then
'Go back to the cover sheet
ActiveWorkbook.Sheets("Start").Activate
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")

End

'If the entry is nonzero but not found yet, keep looking

ElseIf ActiveCell.Value < txtPassword Then
ActiveCell.Offset(1, 0).Select

End If
Loop Until ActiveCell.Value = txtPassword



Any suggestions will be very welcome!

Regards,
Othello

Not an answer to your situation or the OP's, but when a .save solve the
problem, it seems that the cause is due memory being held by Excel, that is
not released.
I would guess maybe the Undo buffer expands and is not fully cleared. Saving
clear it, as there is no longer anything to undo. Seems that it happens more
often with intensive graphic/chart manipulation, rather than purely with
data though.
The presence of a MultiPage in both your project may be an indication also.
If inclined, you could replace it with frames and buttons instead of the
tabs to see if it's the cause.
AFAIK, the main differences between running normally and stepping through
code are :
- timing, using .OnTime or Timer class get interrupted
- .ScreenUpdating is always true
- As the VBE is visible, all the VBEComponents are created, which may not be
the case otherwise.

I assume you are not doing anything funky with subclassing and/or Windows
API ?

NickHK

wrote in message
ps.com...
I had a very similar problem with a program I was running. I didn't
find that the saving of the workbook "fixed" the problem until I was
about ready to give up. Still to this day no one can give me an answer
as to what the exact problem was.

My program was a 6 form wizard that built a budget for our company.
The first three forms ran fine, until the user pressed the next button
to head to the fourth form where I too had a Multipage. When the user
presses next, there was about a 70% Excel crash (not a VBA crash). I
found that if I also went to the users machine where the crash occured
and stepped through it, the crash would NEVER happen, but at full
speed, it would crash. I ended up "fixing" it the cheezy way and just
adding:

'Lets save this thing to work around the problem...
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

It works and now we are using the program, but I still want to find a
real fix for this problem. My code (which is too long to post here)
did closing functions for the third form and initialization and
activation for the fourth form. During the initialization and
activation, it loaded information for 40 textboxes, 24 of which had
change functions.

Anyway, I will be watching this post for any new information.

theSquirrel



Othello wrote:
Hello all:
I have a big (lots of code) multipage userform that asks a user for a
password which is on a list in a hidden sheet, in the first column.
This
list was previously populated, randomized, by another form, and is
different
for each occasion.

When the password is entered the form loops through the first column
looking
for a match. If one is found, the form is populated with any existing
data
from that row on the sheet, and then allows the user to step through 14
pages
of survey, or save for later at any point in the process. Here's the
problem:

The form runs perfectly on my computer (the actual file sits on the
public
drive, accessible by all authorized users), but when anyone else tries
to run
it, it fails and crashes Excel. All of us are using Excel 2003. If I
sit
down at that user's computer, open VBE and step through the procedure to
find
the trouble, it goes all the way through and opens the form very nicely.
Further, if I then save it, that user can open it and run it at normal
speed
with no trouble - everything works!

It works until I do the same thing from someone else's computer and then
only that person can run it. Except that I can still run it from my
workstation.

So, any hints? Why would step-through work when run won't, and why
would
that temporarily fix it, and what does that mean about where to look for
the
trouble?

I'll add in the code for the loop here, because that would seem to be
the
most likely trouble spot, though it could easily be somewhere else.


Private Sub cmdEnterPassword_Click()
' Check to see if the password is in the evaluation file
Application.ScreenUpdating = False
If txtPassword.Value = "" Then 'Check for blanks
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

'Unprotect the sheet and open the hidden sheet to look at passwords
ActiveSheet.Unprotect ("appraisal")
ActiveWorkbook.Sheets("Data").Activate

'Start from the top
Range("A1").Select

Do
If txtPassword.Value = "" Then
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

If ActiveCell.Value = txtPassword Then

Application.ScreenUpdating = True

ElseIf ActiveCell.Value = 0 Then
'Go back to the cover sheet
ActiveWorkbook.Sheets("Start").Activate
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")

End

'If the entry is nonzero but not found yet, keep looking

ElseIf ActiveCell.Value < txtPassword Then
ActiveCell.Offset(1, 0).Select

End If
Loop Until ActiveCell.Value = txtPassword



Any suggestions will be very welcome!

Regards,
Othello

Thanks for you r thoughts Nick, when we do the first overhaul I'll try with
frames and see what happens. My project does not have any API calls or
oddball subclasses. The possibility of the memory overload is intriguing
because it would answer the question of why the problem arose on onther
people's machines and not on mine, which has upgraded ram and paging memory.

Regards,
Othello

"NickHK" wrote:

Not an answer to your situation or the OP's, but when a .save solve the
problem, it seems that the cause is due memory being held by Excel, that is
not released.
I would guess maybe the Undo buffer expands and is not fully cleared. Saving
clear it, as there is no longer anything to undo. Seems that it happens more
often with intensive graphic/chart manipulation, rather than purely with
data though.
The presence of a MultiPage in both your project may be an indication also.
If inclined, you could replace it with frames and buttons instead of the
tabs to see if it's the cause.
AFAIK, the main differences between running normally and stepping through
code are :
- timing, using .OnTime or Timer class get interrupted
- .ScreenUpdating is always true
- As the VBE is visible, all the VBEComponents are created, which may not be
the case otherwise.

I assume you are not doing anything funky with subclassing and/or Windows
API ?

NickHK

wrote in message
ps.com...
I had a very similar problem with a program I was running. I didn't
find that the saving of the workbook "fixed" the problem until I was
about ready to give up. Still to this day no one can give me an answer
as to what the exact problem was.

My program was a 6 form wizard that built a budget for our company.
The first three forms ran fine, until the user pressed the next button
to head to the fourth form where I too had a Multipage. When the user
presses next, there was about a 70% Excel crash (not a VBA crash). I
found that if I also went to the users machine where the crash occured
and stepped through it, the crash would NEVER happen, but at full
speed, it would crash. I ended up "fixing" it the cheezy way and just
adding:

'Lets save this thing to work around the problem...
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

It works and now we are using the program, but I still want to find a
real fix for this problem. My code (which is too long to post here)
did closing functions for the third form and initialization and
activation for the fourth form. During the initialization and
activation, it loaded information for 40 textboxes, 24 of which had
change functions.

Anyway, I will be watching this post for any new information.

theSquirrel



Othello wrote:
Hello all:
I have a big (lots of code) multipage userform that asks a user for a
password which is on a list in a hidden sheet, in the first column.
This
list was previously populated, randomized, by another form, and is
different
for each occasion.

When the password is entered the form loops through the first column
looking
for a match. If one is found, the form is populated with any existing
data
from that row on the sheet, and then allows the user to step through 14
pages
of survey, or save for later at any point in the process. Here's the
problem:

The form runs perfectly on my computer (the actual file sits on the
public
drive, accessible by all authorized users), but when anyone else tries
to run
it, it fails and crashes Excel. All of us are using Excel 2003. If I
sit
down at that user's computer, open VBE and step through the procedure to
find
the trouble, it goes all the way through and opens the form very nicely.
Further, if I then save it, that user can open it and run it at normal
speed
with no trouble - everything works!

It works until I do the same thing from someone else's computer and then
only that person can run it. Except that I can still run it from my
workstation.

So, any hints? Why would step-through work when run won't, and why
would
that temporarily fix it, and what does that mean about where to look for
the
trouble?

I'll add in the code for the loop here, because that would seem to be
the
most likely trouble spot, though it could easily be somewhere else.


Private Sub cmdEnterPassword_Click()
' Check to see if the password is in the evaluation file
Application.ScreenUpdating = False
If txtPassword.Value = "" Then 'Check for blanks
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

'Unprotect the sheet and open the hidden sheet to look at passwords
ActiveSheet.Unprotect ("appraisal")
ActiveWorkbook.Sheets("Data").Activate

'Start from the top
Range("A1").Select

Do
If txtPassword.Value = "" Then
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

If ActiveCell.Value = txtPassword Then

Application.ScreenUpdating = True

ElseIf ActiveCell.Value = 0 Then
'Go back to the cover sheet
ActiveWorkbook.Sheets("Start").Activate
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")

End

'If the entry is nonzero but not found yet, keep looking

ElseIf ActiveCell.Value < txtPassword Then
ActiveCell.Offset(1, 0).Select

End If
Loop Until ActiveCell.Value = txtPassword



Any suggestions will be very welcome!

Regards,
Othello

I had a very similar problem with a program I was running. I didn't
find that the saving of the workbook "fixed" the problem until I was
about ready to give up. Still to this day no one can give me an answer
as to what the exact problem was.

My program was a 6 form wizard that built a budget for our company.
The first three forms ran fine, until the user pressed the next button
to head to the fourth form where I too had a Multipage. When the user
presses next, there was about a 70% Excel crash (not a VBA crash). I
found that if I also went to the users machine where the crash occured
and stepped through it, the crash would NEVER happen, but at full
speed, it would crash. I ended up "fixing" it the cheezy way and just
adding:

'Lets save this thing to work around the problem...
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

It works and now we are using the program, but I still want to find a
real fix for this problem. My code (which is too long to post here)
did closing functions for the third form and initialization and
activation for the fourth form. During the initialization and
activation, it loaded information for 40 textboxes, 24 of which had
change functions.

Anyway, I will be watching this post for any new information.

theSquirrel



Othello wrote:
Hello all:
I have a big (lots of code) multipage userform that asks a user for a
password which is on a list in a hidden sheet, in the first column. This
list was previously populated, randomized, by another form, and is different
for each occasion.

When the password is entered the form loops through the first column looking
for a match. If one is found, the form is populated with any existing data
from that row on the sheet, and then allows the user to step through 14 pages
of survey, or save for later at any point in the process. Here's the problem:

The form runs perfectly on my computer (the actual file sits on the public
drive, accessible by all authorized users), but when anyone else tries to run
it, it fails and crashes Excel. All of us are using Excel 2003. If I sit
down at that user's computer, open VBE and step through the procedure to find
the trouble, it goes all the way through and opens the form very nicely.
Further, if I then save it, that user can open it and run it at normal speed
with no trouble - everything works!

It works until I do the same thing from someone else's computer and then
only that person can run it. Except that I can still run it from my
workstation.

So, any hints? Why would step-through work when run won't, and why would
that temporarily fix it, and what does that mean about where to look for the
trouble?

I'll add in the code for the loop here, because that would seem to be the
most likely trouble spot, though it could easily be somewhere else.


Private Sub cmdEnterPassword_Click()
' Check to see if the password is in the evaluation file
Application.ScreenUpdating = False
If txtPassword.Value = "" Then 'Check for blanks
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

'Unprotect the sheet and open the hidden sheet to look at passwords
ActiveSheet.Unprotect ("appraisal")
ActiveWorkbook.Sheets("Data").Activate

'Start from the top
Range("A1").Select

Do
If txtPassword.Value = "" Then
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")
End
End If

If ActiveCell.Value = txtPassword Then

Application.ScreenUpdating = True

ElseIf ActiveCell.Value = 0 Then
'Go back to the cover sheet
ActiveWorkbook.Sheets("Start").Activate
Application.ScreenUpdating = True
MsgBox ("That Password was not found. Please try again.")

End

'If the entry is nonzero but not found yet, keep looking

ElseIf ActiveCell.Value < txtPassword Then
ActiveCell.Offset(1, 0).Select

End If
Loop Until ActiveCell.Value = txtPassword



Any suggestions will be very welcome!

Regards,
Othello