Selected sheet within a Private Sub

[Darin Kramer]

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

 

[NickHK]

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

 

[paul.robinson]

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

 

[Darin Kramer]

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

 

[Darin Kramer]

Now it didnt run the macro....


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

 

[paul.robinson]

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

 

[kemal]

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

 

[Chip Pearson]

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)

 

[Dave Peterson]

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.