Return to starting point

[Patrick C. Simonds]

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?

 

[Rick Rothstein]

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.

 

[Dave Peterson]

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]

I am afraid you lost me on that one.

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.

 

[Patrick C. Simonds]

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

 

[Joel]

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.

 

[Rick Rothstein]

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)