RefEdit control bug

[Dag Johansen]

Hi,

I have a very boring problem with the RefEdit control;
there seems to be some bug that leads to the control's
state being altered when it's value is read...

To reproduce the behavior, create a userform and add a
label, a refedit and a checkbox. Then paste the following
code into the module:

Private Sub CheckBox1_Change()
Label1.Enabled = CheckBox1.Value
End Sub

Private Sub RefEdit1_Change()
On Error GoTo InvalidRef
Dim val As String
If CheckBox1.Value Then
val = RefEdit1.Value
Label1.Caption = " = " & Evaluate(val)
End If
Exit Sub
InvalidRef:
Err.Clear
Label1.Caption = " #VALUE"
End Sub


Now, when the checkbox is checked RefEdit1_Change will
update a label to reflect the evaluation of the reference.
When the checkbox isn't checked the event handler will
simply exit without doing anything.

Notice how the RefEdit handles switching to other sheets
than the one that was active when showing the modal form.
All is fine if it's value isn't evaluated, but it starts
selecting cells on the wrong worksheet seemingly becuase I
read and evaluate it's Value property...!

I need to evaluate this reference for a function assistant
specifically made for a custom function I provide. Please
help me figure out a way to work around this misbehavior,
it is ruining an otherwise pretty sweet user experience.

Happy monday, if that's possible

Dag

 

[keepitcool]

Dag,

RefEdits DONT work with modeless userforms...

That will lead to you losing the focus..
and the need for stopping excel from the taskmanager...

See MSDN if you dont believe me..
it's not a bug.. it's "by design"
NOT!

Here's a workaround:
Private Sub CheckBox1_Change()
Me.Hide
RefEdit1.Enabled = CheckBox1.Value
Me.Show IIf(CheckBox1, vbModal, vbModeless)
End Sub

Private Sub RefEdit1_Change()
Dim ref As Range
On Error Resume Next
Set ref = Range(Me.RefEdit1)
If ref Is Nothing Then
Label1.Caption = "#VALUE"
Else
Label1.Caption = ref.Address(external:=True)
End If
End Sub


Private Sub UserForm_Activate()
If Me.RefEdit1.Enabled Then Me.RefEdit1.SetFocus
End Sub

Private Sub UserForm_Initialize()
Me.RefEdit1.Enabled = False
Me.CheckBox1 = False
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >