Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to starting point
I use this piece of code to call my UserForm:
If Not Application.Intersect(Target, Range("C9:V700")) Is Nothing Then UserForm4.Show My problem is that in the course of of UserForm initialization I use a search to pull data into the UserForm so my question is, how can I return to the point where the UserForm was called when I get to the end of UserForm initialization? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to starting point
Create a UserForm-wide "global" Range variable (declare it in the
General..Declarations section of the UserForm's code window), then Set the ActiveCell to this variable in the UserForm's Initialize event and then this variable's Select property (remember, it was declared as a Range) in the UserForm's Terminate event. -- Rick (MVP - Excel) "Patrick C. Simonds" wrote in message ... I use this piece of code to call my UserForm: If Not Application.Intersect(Target, Range("C9:V700")) Is Nothing Then UserForm4.Show My problem is that in the course of of UserForm initialization I use a search to pull data into the UserForm so my question is, how can I return to the point where the UserForm was called when I get to the end of UserForm initialization? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to starting point
You could keep track of where you started, but better would be not to
select/activate anything in your code. Instead of cells.find(...).activate with activecell ... dim FoundCell as range set foundcell = cells.find(...) 'and you can check to see if it was found! if foundcell is nothing then msgbox "??? else with foundcell ... "Patrick C. Simonds" wrote: I use this piece of code to call my UserForm: If Not Application.Intersect(Target, Range("C9:V700")) Is Nothing Then UserForm4.Show My problem is that in the course of of UserForm initialization I use a search to pull data into the UserForm so my question is, how can I return to the point where the UserForm was called when I get to the end of UserForm initialization? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to starting point
I am afraid you lost me on that one.
"Rick Rothstein" wrote in message ... Create a UserForm-wide "global" Range variable (declare it in the General..Declarations section of the UserForm's code window), then Set the ActiveCell to this variable in the UserForm's Initialize event and then this variable's Select property (remember, it was declared as a Range) in the UserForm's Terminate event. -- Rick (MVP - Excel) "Patrick C. Simonds" wrote in message ... I use this piece of code to call my UserForm: If Not Application.Intersect(Target, Range("C9:V700")) Is Nothing Then UserForm4.Show My problem is that in the course of of UserForm initialization I use a search to pull data into the UserForm so my question is, how can I return to the point where the UserForm was called when I get to the end of UserForm initialization? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to starting point
Thanks, I figured it out. This is what I have, just incase I missed
anything. My general declaration = Dim rMyCell As Range Start Of Initialization = Set rMyCell = rng(1, 2) Return to rMyCell = rng = rMyCell.Activate "Patrick C. Simonds" wrote in message ... I am afraid you lost me on that one. "Rick Rothstein" wrote in message ... Create a UserForm-wide "global" Range variable (declare it in the General..Declarations section of the UserForm's code window), then Set the ActiveCell to this variable in the UserForm's Initialize event and then this variable's Select property (remember, it was declared as a Range) in the UserForm's Terminate event. -- Rick (MVP - Excel) "Patrick C. Simonds" wrote in message ... I use this piece of code to call my UserForm: If Not Application.Intersect(Target, Range("C9:V700")) Is Nothing Then UserForm4.Show My problem is that in the course of of UserForm initialization I use a search to pull data into the UserForm so my question is, how can I return to the point where the UserForm was called when I get to the end of UserForm initialization? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to starting point
This is very simple. Put a hide as the last instruction of you
initialization code. Then perform the show at the beginning of you main routine. The first call will initialize the userform put won't display the userform. Because the form is initialized the initialization code will never be call again. "Patrick C. Simonds" wrote: I use this piece of code to call my UserForm: If Not Application.Intersect(Target, Range("C9:V700")) Is Nothing Then UserForm4.Show My problem is that in the course of of UserForm initialization I use a search to pull data into the UserForm so my question is, how can I return to the point where the UserForm was called when I get to the end of UserForm initialization? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to starting point
The Return to MyCell should not be part of an assignment; just use this...
rMyCell.Select (you can use Activate if you want as well) -- Rick (MVP - Excel) "Patrick C. Simonds" wrote in message ... Thanks, I figured it out. This is what I have, just incase I missed anything. My general declaration = Dim rMyCell As Range Start Of Initialization = Set rMyCell = rng(1, 2) Return to rMyCell = rng = rMyCell.Activate "Patrick C. Simonds" wrote in message ... I am afraid you lost me on that one. "Rick Rothstein" wrote in message ... Create a UserForm-wide "global" Range variable (declare it in the General..Declarations section of the UserForm's code window), then Set the ActiveCell to this variable in the UserForm's Initialize event and then this variable's Select property (remember, it was declared as a Range) in the UserForm's Terminate event. -- Rick (MVP - Excel) "Patrick C. Simonds" wrote in message ... I use this piece of code to call my UserForm: If Not Application.Intersect(Target, Range("C9:V700")) Is Nothing Then UserForm4.Show My problem is that in the course of of UserForm initialization I use a search to pull data into the UserForm so my question is, how can I return to the point where the UserForm was called when I get to the end of UserForm initialization? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return to starting point | Excel Programming | |||
Enter doesnt return to the cell below the starting point anymore | Excel Discussion (Misc queries) | |||
Indexing Values to Same Starting Point for Relative Return Chart | Charts and Charting in Excel | |||
bar chart starting point | Charts and Charting in Excel | |||
Starting Point of macro | Excel Programming |