Strange RefEdit Problem

[Pflugs]

I have been having issues using RefEdit controls and events, and I have
narrowed the problem down to how the user uses the control. That is, if
RefEdit1 has focus and the user clicks in the text box area of the control
and then selects cells in the background, everything is fine. All events
work correctly, and the userform works as planned.

However, if the user clicks on the RefEdit's button (the one with a
horizontal line that when clicked collapses the form), the user is allowed to
select cells and return to the form, but when the events attempt to run, the
userform crashes. Excel crashes soon thereafter.

I am using enter, exit, and change events with the RefEdit control. I have
tried cleaning the code with Rob Bovey's cleaner. Removing all RefEdit
events seemed to work, but the userform doesn't function as I want it to.

Does anyone have an explanation for this? Is there a way to limit the
RefEdit control to not allow users to click on the collapse button?

Thanks,
Pflugs

 

[Bob Phillips]

Is it in a frame? I have had odd problems with RefEdit in a frame, but
removing them usually gets rid of the problem.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Cone]

Jon Peltier's advice/answer to your almost identical post of 06/27/2007
would be worth reading again.
I would add - never set the focus to a RefEdit control and
do not add a RefEdit control to a Frame.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Pflugs" <[email protected]>
wrote in message
I have been having issues using RefEdit controls and events, and I have
narrowed the problem down to how the user uses the control. That is, if
RefEdit1 has focus and the user clicks in the text box area of the control
and then selects cells in the background, everything is fine. All events
work correctly, and the userform works as planned.

However, if the user clicks on the RefEdit's button (the one with a
horizontal line that when clicked collapses the form), the user is allowed to
select cells and return to the form, but when the events attempt to run, the
userform crashes. Excel crashes soon thereafter.

I am using enter, exit, and change events with the RefEdit control. I have
tried cleaning the code with Rob Bovey's cleaner. Removing all RefEdit
events seemed to work, but the userform doesn't function as I want it to.

Does anyone have an explanation for this? Is there a way to limit the
RefEdit control to not allow users to click on the collapse button?

Thanks,
Pflugs

 

[Pflugs]

I had not seen Jon's post since 6/30 was my wedding day. I apologize.

The two RefEdit controls were in frames. I have since removed the frames,
but I still had problems with the RefEdit events. I removed all set focus
commands relating to the RefEdits, and I'm trying to write other events
instead of a RefEdit_Change event.

Are there any problems with using Enter/Exit/Change events with other
controls, especially textboxes?

Thanks,
Pflugs

 

[Jim Cone]

Well, getting married does seem to rid ones' mind of other issues. <g>
Best of luck to you.

I know of no other Office controls that create problems similar to the
RefEdit control.
I have found that the interaction between code and controls is sometimes
not as I visualize it. It can be revealing to step thru the code one line at a time.

Oh, and it is best to avoid the marriage exit event. <g>
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Pflugs" <[email protected]>
wrote in message
I had not seen Jon's post since 6/30 was my wedding day. I apologize.

The two RefEdit controls were in frames. I have since removed the frames,
but I still had problems with the RefEdit events. I removed all set focus
commands relating to the RefEdits, and I'm trying to write other events
instead of a RefEdit_Change event.

Are there any problems with using Enter/Exit/Change events with other
controls, especially textboxes?
Thanks,
Pflugs

 

[Pflugs]

Thanks for the well wishes and the advice to avoid the marriage_Exit event!
That's pretty clever...

Anyway, the problem I've been having is that RefEdit code refuses to let me
step through it. It will crash even with breakpoints and stop commands. Any
event that even references the RefEdit seems to be susceptible to this
behavior, even Userform events. That makes it awfully hard to code and
debug!

I guess the best thing to do would be to write procedures called by command
buttons instead of events, but that assumes extra intelligence by the user.
Stupid controls...

Thanks,
Pflugs

 

[Tushar Mehta]

The RefEdit is a very important control but has all sorts of
limitations.

Also, I avoid using events with various text/combobox/refedit-type
controls preferring to wait until the user clicks the userform's OK
button before validating various user choices. You might want to check
Userform design
http://www.tushar-mehta.com/publish_train/book_vba/09_userinterface.htm

For my take on the RefEdit control see the sections starting with 'Range
references in a userform'

 

[Jim Cone]

I've had that refuse to cooperate and crash problem.
You may have an extra control or two on the form you don't know about.
Check the Form properties window and see if there are any controls
listed you aren't using. Also, select any remaining frames and check for
duplicate controls on them. (or duplicate frames)
The RefEdit control can duplicate itself gratuitously and copies of other controls
can show up just by touching that Ctrl key sometimes.

If all fails...
(1) You could start over with a new form
(2) Use a work around by replacing the RefEdit with an InputBox.
My "Shade Data Rows" add-in on the Products page at my website
uses that technique. It has a picture of a RefEdit control that when clicked
shows an InputBox. Not for all occasions, but if you are really pissed...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Pflugs" <[email protected]>
wrote in message
Thanks for the well wishes and the advice to avoid the marriage_Exit event!
That's pretty clever...

Anyway, the problem I've been having is that RefEdit code refuses to let me
step through it. It will crash even with breakpoints and stop commands. Any
event that even references the RefEdit seems to be susceptible to this
behavior, even Userform events. That makes it awfully hard to code and
debug!

I guess the best thing to do would be to write procedures called by command
buttons instead of events, but that assumes extra intelligence by the user.
Stupid controls...

Thanks,
Pflugs

 

[Pflugs]

Thanks for the reference. I appreciate all the ideas I can get.

By the way, what are the four additional controls you show in your UserForm
toolbox? I recognize the Date and Time Picker, but the three in the bottom
right are unfamiliar to me. Could you tell me what they are and what they
do?

Thanks,
Pflugs

 

[Tushar Mehta]

Don't know what the 1st one is; the last 2 are web browser and treeview
respectively. I often add controls on a temporary / experimental basis
and then remove them when I no longer need them.

 

[NickHK]

As an alternative, you could use Application.InputBox, with Type=8, in a
suitable event.
Add error handling to the code below:

Private Sub CommandButton1_Click()
Dim RetVal As Variant

Const RANGE_ONLY As Long = 8

Set RetVal = Application.InputBox("Select a range", , , , , , , RANGE_ONLY)

TextBox1.Text = RetVal.Address

End Sub

NickHK

 

[Pflugs]

Now, that is a great solution. I really like that method. I will probably
use that all the time now. I like to use pages and frames to organize my
userforms, but there are often cases that I need range references. This will
work well. Thank you for showing it to me.

 

[Pflugs]

Thanks. The treeview control is very interesting; I'll have to do some more
research on that one.