Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 20th 03, 10:54 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 4
Default 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   Report Post  
Old October 20th 03, 12:32 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,253
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
RefEdit question tt Excel Discussion (Misc queries) 2 February 8th 06 03:42 AM
RefEdit does not behave like Excel's built-in reference edit boxes Tom Ogilvy Excel Programming 0 September 11th 03 06:43 PM
RefEdit does not behave like Excel's built-in reference edit boxes Dave Ramage[_2_] Excel Programming 0 September 11th 03 04:55 PM
UserForm with RefEdit Modality Issue Tom Ogilvy Excel Programming 0 August 20th 03 12:32 AM


All times are GMT +1. The time now is 05:27 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017