Three things come to mind. First, is it possible that in the code you
<sniped you have some error handling code that is causing the execution to
jump to another location within the sub (or to a parent sub which calls the
problematic code)? Second, are you ever setting the Value property of the
cmdLoadData button? Doing so will cause the Click event to run. Finally, it
could be that VBA's own internal code storage has become corrupted or
otherwise damaged. The solution to this problem is to export all code out of
VBA to plain text files, remove the code modules from the project, which
causes VBA to purge its storage areas, and then import the text files into
VBA, starting VBA with a clean slate. Rob Bovey (
www.appspro.com) has
created an Add-In called Code Cleaner 5.0 that automates the entire
export/remove/import process down to a few mouse clicks. It also makes a
backup copy of the workbook prior to removing the code so that in the very
rare chance that something goes wrong during the export/import process.
Cleaning out VBA's code storage can cure a wide range of very strange
problems.
See Rob's page at
http://www.appspro.com/Utilities/CodeCleaner.htm for a
free copy of the Code Cleaner. This add-in is a must-have for serious VBA
developers.
On a final note, when debugging your code, you should set error trapping to
"Break In Class Module" on the General tab of the Options dialog in the VBA
Editor. If an error occurs, this will break at the actual line of code that
caused the problem. If the problem lies in an object module (userform,
class, etc), and error trapping is set to "Break On Unhandled Errors", the
code will break on the line of code that refers to the object module rather
than on the actual break. As an example, suppose you have in a user form the
line
Debug.Print 1/0 ' obvious error
And in a standard module you have UserForm1.Show. If error handling is set
to "Break On Unhandled Errors", to code will break on UserForm1.Show, even
though nothing is wrong with the Show method itself, rather than on the real
error within the form Debug.Print 1/0.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"JHop" wrote in message
...
I have a very strange problem. When the following code executes and the
condition is not met, it goes to LoadDataExit as expected, but then
instead
of stopping when it gets to the end, it goes back to cmdLoadData and
executes
repeatedly. This happened once before in another workbook, and at the
time
(being more of an Access programmer) I decided it was some kind of
corruption
and went back to an earlier version and applied all the code changes,
which
got it working again. Since it's happened again in a totally different
workbook, and since that kind of fix is very tedious and not necessarily
guaranteed to work, I'm wondering what could be happening and how best to
fix
it now and prevent it in the future. I'd appreciate any help.
Private Sub cmdLoadData_Click()
<snip
If oSheet.Range(conCellOrderToVerify) < "" And _
InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0
Then
MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify
& _
" on Sheet1 (Summary Sheet) " & _
" should contain the text 'Order - Verify'", _
vbCritical
oInputBook.Close
GoTo LoadDataExit
End If
<snip
LoadDataExit:
Sheets("Start").Activate
Sheets("Data").Visible = False
Sheets("Lookups").Visible = False
ActiveSheet.Range("A11").Select
Application.ScreenUpdating = True
Exit Sub
<snip
End Sub
--
Judy Hopkins