When I insert a new sheet in a workbook, there are some things I do to it via
VBA code before it finishes activating. However, I would like to restore the
sheet that was active PRIOR to the insert as the active one after the new
sheet finishes it's setup. The events available in Excel don't seem amenable
to this. How can I do this with VBA code?
Thanks in advance,
facmanboss

Set prior = ActiveSheet
With Worksheets.Add
'run your code on added sheet
End With
prior.Activate


Gord Dibben MS Excel MVP

On Tue, 29 Jul 2008 16:33:01 -0700, facmanboss
wrote:

When I insert a new sheet in a workbook, there are some things I do to it via
VBA code before it finishes activating. However, I would like to restore the
sheet that was active PRIOR to the insert as the active one after the new
sheet finishes it's setup. The events available in Excel don't seem amenable
to this. How can I do this with VBA code?
Thanks in advance,
facmanboss

Thanks. I wasn't clear in my description. My bad. This looks like it works
fine from code being run to add a new sheet. Here is my scenario. The user
selects the Excel Menu Insert-Worksheet. No code involved, Excel does it.
Excel changes the activesheet to the new one.

I can't figure out how to make the prior sheet active again since there is
no code running to test with and the user needs to be able to use the Insert
menu only to add a new sheet.

"Gord Dibben" wrote:

Set prior = ActiveSheet
With Worksheets.Add
'run your code on added sheet
End With
prior.Activate


Gord Dibben MS Excel MVP

On Tue, 29 Jul 2008 16:33:01 -0700, facmanboss
wrote:

When I insert a new sheet in a workbook, there are some things I do to it via
VBA code before it finishes activating. However, I would like to restore the
sheet that was active PRIOR to the insert as the active one after the new
sheet finishes it's setup. The events available in Excel don't seem amenable
to this. How can I do this with VBA code?
Thanks in advance,
facmanboss

You wrote of using code to do something to the new sheet after it is
inserted/added.

When/how do you activate that code?

Why not just add the code I posted at the top of your code and assign it all
to a button?

Tell users to hit the button when inserting a new sheet.


Gord

On Wed, 30 Jul 2008 08:49:02 -0700, facmanboss
wrote:

Thanks. I wasn't clear in my description. My bad. This looks like it works
fine from code being run to add a new sheet. Here is my scenario. The user
selects the Excel Menu Insert-Worksheet. No code involved, Excel does it.
Excel changes the activesheet to the new one.

I can't figure out how to make the prior sheet active again since there is
no code running to test with and the user needs to be able to use the Insert
menu only to add a new sheet.

"Gord Dibben" wrote:

Set prior = ActiveSheet
With Worksheets.Add
'run your code on added sheet
End With
prior.Activate


Gord Dibben MS Excel MVP

On Tue, 29 Jul 2008 16:33:01 -0700, facmanboss
wrote:

When I insert a new sheet in a workbook, there are some things I do to it via
VBA code before it finishes activating. However, I would like to restore the
sheet that was active PRIOR to the insert as the active one after the new
sheet finishes it's setup. The events available in Excel don't seem amenable
to this. How can I do this with VBA code?
Thanks in advance,
facmanboss

Unfortunately, that's the obvious way. We don't want end users having to
perform an action for this to occur. I was hoping there was a way to
intercept the Menu Insert-Worksheet such that it would invoke some of my
code where I could add the new sheet per your suggestion rather than letting
Excel do it. If there isn't an easy way, then it isn't worth pursuing.
Thanks.

"Gord Dibben" wrote:

You wrote of using code to do something to the new sheet after it is
inserted/added.

When/how do you activate that code?

Why not just add the code I posted at the top of your code and assign it all
to a button?

Tell users to hit the button when inserting a new sheet.


Gord

Place in Thisworkbook module

Private Sub Workbook_NewSheet(ByVal Sh As Object)
'run the code to do whatever on the new sheet
're-activate the original sheet
End Sub


Gord

On Thu, 31 Jul 2008 08:51:05 -0700, facmanboss
wrote:

Unfortunately, that's the obvious way. We don't want end users having to
perform an action for this to occur. I was hoping there was a way to
intercept the Menu Insert-Worksheet such that it would invoke some of my
code where I could add the new sheet per your suggestion rather than letting
Excel do it. If there isn't an easy way, then it isn't worth pursuing.
Thanks.

"Gord Dibben" wrote:

You wrote of using code to do something to the new sheet after it is
inserted/added.

When/how do you activate that code?

Why not just add the code I posted at the top of your code and assign it all
to a button?

Tell users to hit the button when inserting a new sheet.


Gord