Help needed with a RefEdit control

[Dan Hatola]

I am working with a RefEdit control on a Userform and running into a problem.
I know the RefEdit control is buggy, but unfortunately it is the only
control that makes sense to use in this case. The problem occurs when a
Userform with a RefEdit control is displayed and I click on a workbook other
than the active workbook. This is a strange scenario because it is only
possible when the "show windows in taskbar" option is selected (otherwise
only one Excel window can be seen at a time). When I attempt to click on the
other workbook, Excel prevents it from activating (what I want to happen),
however a second RefEdit box appears at the top of the screen. Both the
RefEdit control on the Userform and the RefEdit at the top of the screen
display the same range. The problem arises if I actually click on (activate)
the RefEdit at the top of the screen, switch back to the Userform (activate),
and then close (cancel) the RefEdit at the top of the screen. This will
close the entire Userform and create a memory problem. The memory problem
prevents the Userform buttons from functioning properly when it is
redisplayed. Eventually I have to force quit or stop the code in the VBE,
which displays the memory error and then Excel shuts down. Is there a way to
prevent this error or even better to prevent the second RefEdit box from
appearing when a user attempts to activate a different workbook?

[Jim Cone]

Some discussion here...
http://tinyurl.com/pxwes
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Dan Hatola"
<[email protected]>
wrote in message
I am working with a RefEdit control on a Userform and running into a problem.
I know the RefEdit control is buggy, but unfortunately it is the only
control that makes sense to use in this case. The problem occurs when a
Userform with a RefEdit control is displayed and I click on a workbook other
than the active workbook. This is a strange scenario because it is only
possible when the "show windows in taskbar" option is selected (otherwise
only one Excel window can be seen at a time). When I attempt to click on the
other workbook, Excel prevents it from activating (what I want to happen),
however a second RefEdit box appears at the top of the screen. Both the
RefEdit control on the Userform and the RefEdit at the top of the screen
display the same range. The problem arises if I actually click on (activate)
the RefEdit at the top of the screen, switch back to the Userform (activate),
and then close (cancel) the RefEdit at the top of the screen. This will
close the entire Userform and create a memory problem. The memory problem
prevents the Userform buttons from functioning properly when it is
redisplayed. Eventually I have to force quit or stop the code in the VBE,
which displays the memory error and then Excel shuts down. Is there a way to
prevent this error or even better to prevent the second RefEdit box from
appearing when a user attempts to activate a different workbook?

[NickHK]

Dan,
Nothing specific to your problem, but do any of these help ?

- http://support.microsoft.com/kb/q177632/

- Don't put the RefEdit on a frame or other container, only directly on the
userform.

- Give the user an application.Inputbox with Type:=8

-
http://www.dailydoseofexcel.com/arch...edit-controls/

NickHK

"Dan Hatola" <[email protected]> wrote in message
news:[email protected]...
> I am working with a RefEdit control on a Userform and running into a
problem.
> I know the RefEdit control is buggy, but unfortunately it is the only
> control that makes sense to use in this case. The problem occurs when a
> Userform with a RefEdit control is displayed and I click on a workbook
other
> than the active workbook. This is a strange scenario because it is only
> possible when the "show windows in taskbar" option is selected (otherwise
> only one Excel window can be seen at a time). When I attempt to click on
the
> other workbook, Excel prevents it from activating (what I want to happen),
> however a second RefEdit box appears at the top of the screen. Both the
> RefEdit control on the Userform and the RefEdit at the top of the screen
> display the same range. The problem arises if I actually click on
(activate)
> the RefEdit at the top of the screen, switch back to the Userform
(activate),
> and then close (cancel) the RefEdit at the top of the screen. This will
> close the entire Userform and create a memory problem. The memory problem
> prevents the Userform buttons from functioning properly when it is
> redisplayed. Eventually I have to force quit or stop the code in the VBE,
> which displays the memory error and then Excel shuts down. Is there a way
to
> prevent this error or even better to prevent the second RefEdit box from
> appearing when a user attempts to activate a different workbook?