Private Sub Running Other Private Sub Inadvertently

[Ross Culver]

I have an object-heavy spreadsheet with alot of code supporting those objects on a single sheet module. For some reason when on of the routines is executed, it jumps to the first routine in the module, runs it and then returns back to continue the first. The point at which it jumps is shown below. The sub it jumps to is "


' Save the request with a new name.
ActiveWorkbook.SaveAs Filename:= _
"C:\temp\PTR" & piRequestNo & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'The jump occurs here.
ActiveWorkbook.SendMail Recipients:[email protected], Subject:="Pass Thru Request", ReturnReceipt:=True


Jumps to here
Private Sub cbCat1_Change()

Is it possible to have too much code on a sheet module? I'm thinking I need to move some of this code to a separate, non-sheet module.

Any help would be tremendously appreciated.

Ross

 

[Dave Peterson]

I think I've read posts that describe this.

I think that this is the situation (untested, though).

cbCat1 is a combobox from the Control toolbox toolbar on the worksheet?

And cbCat1 uses a .listfillrange?

And that .listfillrange is on the same worksheet as the combobox?

If yes, either fill the combobox via .additem or move the list to a different
sheet.

Post back with your results, please.

 

[Ross Culver]

Dave, thanks for the info; however, it doesn't look like this is the answer.

The problem lies with the file save as code:

ActiveWorkbook.SaveAs Filename:="C:\temp\PTR" & piRequestNo & ".xls",
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

When I comment out this, the execution does not jump to the other routines.
Also, it doesn't jump to a specific sub, it runs through all of them!

I tried moving it to a public sub in a separate module, but that made no
difference.

Ross

 

[Ross Culver]

Could it be that the "Change Event" is occurring on all sheet objects when a
file is saved?

Dave, thanks for the info; however, it doesn't look like this is the
answer.

The problem lies with the file save as code:

ActiveWorkbook.SaveAs Filename:="C:\temp\PTR" & piRequestNo & ".xls",
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

When I comment out this, the execution does not jump to the other
routines. Also, it doesn't jump to a specific sub, it runs through all of
them!

I tried moving it to a public sub in a separate module, but that made no
difference.

Ross

 

[Ross Culver]

Be warned:

I don't know why this was a problem, but when I changed all of my "Change" events to "LostFocus" events (which was ok for my purposes) the problem went away.

Ross
I have an object-heavy spreadsheet with alot of code supporting those objects on a single sheet module. For some reason when on of the routines is executed, it jumps to the first routine in the module, runs it and then returns back to continue the first. The point at which it jumps is shown below. The sub it jumps to is "


' Save the request with a new name.
ActiveWorkbook.SaveAs Filename:= _
"C:\temp\PTR" & piRequestNo & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'The jump occurs here.
ActiveWorkbook.SendMail Recipients:[email protected], Subject:="Pass Thru Request", ReturnReceipt:=True


Jumps to here
Private Sub cbCat1_Change()

Is it possible to have too much code on a sheet module? I'm thinking I need to move some of this code to a separate, non-sheet module.

Any help would be tremendously appreciated.

Ross

 

[Dave Peterson]

Glad you found a workaround. (I didn't have any other guesses.)