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

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

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" <[email protected]> wrote in message
news:[email protected]...
>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
>> 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

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


"Ross Culver" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>> 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
>
>

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
"Ross Culver" <[email protected]> wrote in message news:[email protected]...
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

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


--

Dave Peterson