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?

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?

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

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?

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?

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?

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?