Selected sheet within a Private Sub
 

Hello all!

I have simple VB (See below) that calls another macro every time a user
"leaves" a specific sheet. The Macro runs fine, but when finished
running takes the user to the sheet where the macro last did its thing,
as opposed to where the user clicked. Thus for eg, if the private sub
was within sheet1, and user clicked sheet2 - macro below would run, but
take the user to sheet called by the data resolve macro, instead of
sheet2, which is users choice.

Ideas...?

Thanks and Kind Regards

Darin

Private Sub Worksheet_Deactivate()
Application.CommandBars("Data").Visible = False
Call Data_resolve
End Sub




*** Sent via Developersdex http://www.developersdex.com ***

Selected sheet within a Private Sub
 

Darin,
Depends what your "Data_resolve" routine does ? If it involves any .Select
(like the example below), then remove them and just reference the cell/range
directly.

'Worksheets(1) Code
Private Sub Worksheet_Deactivate()
Call SomeCode
End Sub

Private Sub SomeCode()
Worksheets(1).Select
End Sub

NickHK

"Darin Kramer" wrote in message
...
Hello all!

I have simple VB (See below) that calls another macro every time a user
"leaves" a specific sheet. The Macro runs fine, but when finished
running takes the user to the sheet where the macro last did its thing,
as opposed to where the user clicked. Thus for eg, if the private sub
was within sheet1, and user clicked sheet2 - macro below would run, but
take the user to sheet called by the data resolve macro, instead of
sheet2, which is users choice.

Ideas...?

Thanks and Kind Regards

Darin

Private Sub Worksheet_Deactivate()
Application.CommandBars("Data").Visible = False
Call Data_resolve
End Sub




*** Sent via Developersdex http://www.developersdex.com ***

Selected sheet within a Private Sub
 

Hi
Will this do it

Private Sub Worksheet_Deactivate()
Dim ws As Worksheet
Set ws = ActiveWorkbook.ActiveSheet
Call Data_resolve
ws.Activate
Set ws = Nothing
End Sub

regards
Paul

so I'd guess the problem is with the called macro
On Mar 9, 9:12 am, Darin Kramer wrote:
Hello all!

I have simple VB (See below) that calls another macro every time a user
"leaves" a specific sheet. The Macro runs fine, but when finished
running takes the user to the sheet where the macro last did its thing,
as opposed to where the user clicked. Thus for eg, if the private sub
was within sheet1, and user clicked sheet2 - macro below would run, but
take the user to sheet called by the data resolve macro, instead of
sheet2, which is users choice.

Ideas...?

Thanks and Kind Regards

Darin

Private Sub Worksheet_Deactivate()
Application.CommandBars("Data").Visible = False
Call Data_resolve
End Sub

*** Sent via Developersdexhttp://www.developersdex.com***

Selected sheet within a Private Sub
 

Hi Nick,

The macro involves many selects, and many tasks, not something that I
can reference into the private sub. Also the same macro can be run
elsewhere. Im looking to see if there is a VB that rembers users choice
before running the macro and takes him back there when finished, kinda
like activesheet, but activechoice...

REgards

D


*** Sent via Developersdex http://www.developersdex.com ***

Selected sheet within a Private Sub
 

Now it didnt run the macro....


*** Sent via Developersdex http://www.developersdex.com ***

Selected sheet within a Private Sub
 

On Mar 9, 10:27 am, Darin Kramer wrote:
Now it didnt run the macro....

*** Sent via Developersdexhttp://www.developersdex.com***

Hi
works fine for me when I put a simple macro in to call. Your called
macro is probably doing something, but we'd need to see it.
regards
Paul

Selected sheet within a Private Sub
 

I have simple VB (See below) that calls another macro every time a user
"leaves" a specific sheet

How does a user leave a specific sheet ? via sheet tabs ?
If you really want to have full control of your code then place all
your relevant code behind a command button.
I personally do not use any activate or deactivate events as your code
may cause them not to fire at all when so many lines of complex codes
are involved.
So I'd suggest a command button.

private yourcommandbutton_click()

Application.CommandBars("Data").Visible = False
Call Data_resolve
youruserssheet.select

end sub

Selected sheet within a Private Sub
 

Im looking to see if there is a VB that rembers users choice
before running the macro and takes him back there when finished, kinda
like activesheet, but activechoice...

VBA does no such thing. It will leave the user at the sheet and cell that
was last selected by your VBA code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Darin Kramer" wrote in message
...

Hi Nick,

The macro involves many selects, and many tasks, not something that I
can reference into the private sub. Also the same macro can be run
elsewhere. Im looking to see if there is a VB that rembers users choice
before running the macro and takes him back there when finished, kinda
like activesheet, but activechoice...

REgards

D


*** Sent via Developersdex http://www.developersdex.com ***

Selected sheet within a Private Sub
 

Chip answered your question, but I bet he would suggest that you don't swap
sheets. There's not that require you to select different sheets. You can
usually work with ranges/objects without selecting.

But if you really wanted, you could keep track.

dim ActCell as range
Dim CurSel as range

set curSel = selection
set actcell = activecell

'do a bunch of stuff....

application.goto cursel
actcell.activate

=======
But this isn't usually necessary.




Darin Kramer wrote:

Hi Nick,

The macro involves many selects, and many tasks, not something that I
can reference into the private sub. Also the same macro can be run
elsewhere. Im looking to see if there is a VB that rembers users choice
before running the macro and takes him back there when finished, kinda
like activesheet, but activechoice...

REgards

D

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson