Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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*** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selected sheet within a Private Sub
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet selected, automatic return to top of sheet | Excel Worksheet Functions | |||
re : Possible to run private sub macros by writing another private | Excel Programming | |||
Updating excel sheet with selected data from another sheet in the same file | Excel Worksheet Functions | |||
how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet. | Excel Worksheet Functions | |||
Private Sub Running Other Private Sub Inadvertently | Excel Programming |