Private Sub Running Other Private Sub Inadvertently
 

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 , 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

Private Sub Running Other Private Sub Inadvertently
 

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 wrote:

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 ,
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

Private Sub Running Other Private Sub Inadvertently
 

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


"Dave Peterson" wrote in message
...
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 wrote:

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
,
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