Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefEdit control bug
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefEdit control bug
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 "Dag Johansen" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RefEdit question | Excel Discussion (Misc queries) | |||
RefEdit does not behave like Excel's built-in reference edit boxes | Excel Programming | |||
RefEdit does not behave like Excel's built-in reference edit boxes | Excel Programming | |||
UserForm with RefEdit Modality Issue | Excel Programming |