Search code examples
excelvbauserform

Glitch when using RefEdit_Change Event in a VBA UserForm

The following should happen:

1. UserForm with 2 RefEdit controls is shown

2. The first RefEdit is used to select a range

3. The RefEdit_Change event adjusts the second RefEdit control to .offset(0,1) of the range

Here my code until now:

Module1:

Dim frmSelectXY As New frmSelectImportData

With frmSelectXY
    .Show
    .DoStuffWithTheSelectedRanges
End With

UserForm: frmSelectImportData

Option Explicit

Private Type TView
    IsCancelled As Boolean
    xrng As Range
    yrng As Range
End Type

Private this As TView
Public Property Get IsCancelled() As Boolean
    IsCancelled = this.IsCancelled
End Property
Public Property Get yrng() As Range
    Set yrng = this.yrng
End Property
Public Property Get xrng() As Range
    Set xrng = this.xrng
End Property

'Here is where the fun happens
Private Sub RefEdit1_Change()
'RefEdit2.Value = RefEdit1.Value
If InStr(1, RefEdit1.Value, "[") <> 0 And InStr(1, RefEdit1.Value, "!") <> 0 Then
    RefEdit2.Value = Range(RefEdit1.Value).offset(0, 1).Address(External:=True)
ElseIf InStr(1, RefEdit1.Value, "!") <> 0 Then
    RefEdit2.Value = Range(RefEdit1.Value).offset(0, 1).Parent.Name & "!" & Range(RefEdit1.Value).offset(0, 1).Address(External:=False)
Else
    RefEdit2.Value = Range(RefEdit1.Value).offset(0, 1).Address(External:=False)
End If

End Sub


Private Sub SaveBTN_Click()
Set this.xrng = Range(RefEdit1.Value)
Set this.yrng = Range(RefEdit2.Value)

If Not validate Then
MsgBox "x-values and y-values need to have the same size."
Else
Me.Hide
End If

End Sub

Function validate() As Boolean
validate = False
If this.xrng.count = this.yrng.count Then validate = True
End Function

RefEdit1_Change should adjust the value of RefEdit2 such that it will show the reference to the column just next to it or better .offest(0,1) to it.

But that isn't what happens.. the value doesn't get changed. As soon as the User clicks into RefEdit2 if RefEdit1 has already been changed, the program aborts without error message. If you Cancle the UserForm I have also experienced hard crashes of excel. I have temporarily fixed the problem by rebuilding the UserForm from scratch and renaming the RefEdits. But at some point it reapeared. It seems as if it is an Excel/VBA inherent problem.

Does anybody know how to fix this?

Ugly hacks and workarounds are welcome, anything is better than, abort without error message.

Solution

  • you need to enclose Range(RefEdit1.Value).offset(0, 1).Parent.Name in ' so

    ="'" & Range(RefEdit1.Value).offset(0, 1).Parent.Name & "'!"