How to Pass Control from VBA to user and return
 

I would like to pass program control from a VBA macro to a user on a
particular sheet, let the user do whatever, then return control to VBA and
pick up where I left off with all my VBA variables/pointers intact.

I think the answer is "Design it a different way", but I thought I'd ask
since I would prefer to the let the user process the data in the already
complex spreadsheet, instead of having to dupe all the formulas in vba.


thanks,

tim

How to Pass Control from VBA to user and return
 

Hi Tim,

You were not very specific in what you want the VBA code to do, but I think
you should look into putting the code in the Worksheet_Change Event
This is trigerred every time a user makes a Change to the Worksheet, so you
dont have to launch a Macro & then worry about having to have the focus go
back to the user. You should be able to accomplish what you want using this
event. You can even track what cell the user is in.

Also see SelectionChange Event & other Events for Worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Set the row containing the active cell to bold.
ActiveCell.EntireRow.Font.Bold = True
End Sub


Jeff

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "C"
End Sub

"needyourhelp" wrote:

I would like to pass program control from a VBA macro to a user on a
particular sheet, let the user do whatever, then return control to VBA and
pick up where I left off with all my VBA variables/pointers intact.

I think the answer is "Design it a different way", but I thought I'd ask
since I would prefer to the let the user process the data in the already
complex spreadsheet, instead of having to dupe all the formulas in vba.


thanks,

tim

How to Pass Control from VBA to user and return
 

The foregoing may be preferred, but another method might be to launch a
non-modal userform which loads up form level variables, then gets hidden,
then might get "show"n again on some sort of workbook_change event (if a
test of whether it is loaded is true). Sounds complicated, maybe I am crazy,
it's a shot in the dark.

"Jeff" wrote in message
...
Hi Tim,

You were not very specific in what you want the VBA code to do, but I
think
you should look into putting the code in the Worksheet_Change Event
This is trigerred every time a user makes a Change to the Worksheet, so
you
dont have to launch a Macro & then worry about having to have the focus go
back to the user. You should be able to accomplish what you want using
this
event. You can even track what cell the user is in.

Also see SelectionChange Event & other Events for Worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Set the row containing the active cell to bold.
ActiveCell.EntireRow.Font.Bold = True
End Sub


Jeff

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "C"
End Sub

"needyourhelp" wrote:

I would like to pass program control from a VBA macro to a user on a
particular sheet, let the user do whatever, then return control to VBA
and
pick up where I left off with all my VBA variables/pointers intact.

I think the answer is "Design it a different way", but I thought I'd ask
since I would prefer to the let the user process the data in the already
complex spreadsheet, instead of having to dupe all the formulas in vba.


thanks,

tim

How to Pass Control from VBA to user and return
 

Can you just break your code into 2 subs; the "before" and "after", and use
Public variables to store the necessary values?

"needyourhelp" wrote:

I would like to pass program control from a VBA macro to a user on a
particular sheet, let the user do whatever, then return control to VBA and
pick up where I left off with all my VBA variables/pointers intact.

I think the answer is "Design it a different way", but I thought I'd ask
since I would prefer to the let the user process the data in the already
complex spreadsheet, instead of having to dupe all the formulas in vba.


thanks,

tim