Selected sheet within a Private Sub

D

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

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
 
P

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
 
D

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

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
 
C

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top