I have a form where I want the user to select a range on which to process.
In order to check the range that the user has selected, I want to use the
RefEdit_Exit.

However, Excel keeps failing when attempting to put validation code behind
it - sadly to the extent that I get the 'Excel has encountered ....... etc.'
and Excel restarts.

I've tried a simple MsgBox in the Exit code and that seems to work fine, but
as soon as I try any other code it doesn't fire.
I've tried putting a breakpoint on the first line of code, but the operation
fails without reaching the breakpoint.

I must admit, I'm at a bit of a loss.

Hi Paul,

Without seeing your code I wonder if you are getting into an eternal loop by
trying to set the focus back to the control when there is an error. Need to
disable events.

The following little test works.

Private Sub RefEdit1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Application.EnableEvents = False
On Error GoTo ReEnableEvents

If Range(Me.RefEdit1).Column 4 Then
Cancel = True 'Cancels the Exit
MsgBox "Must Select from first 4 columns"
End If

ReEnableEvents:
Application.EnableEvents = True
End Sub


During development you can comment out the following line so that you can
easily identify errors.
On Error GoTo ReEnableEvents

However, having said that, you then need the following code to re-enable
events if they get turned off and not turned back on due to an error. You can
put the code anywhere and to run just place the cursor anywhere in the sub
and press F5. (You might have already known this but it will save you from
tearing your hair out if you didn't.)

Sub Re_EnableEvents()
'Use during development to turn events on
'if code leaves them turned off.
Application.EnableEvents = True
End Sub

If your question is still not answered then perhaps you can post the
validation code you are using.

--
Regards,

OssieMac

Hi again Paul,

I meant to include that you don't need to disable events in the example I
posted. I included them to show how to disable them if your validation code
is causing an endless loop by re-calling the Exit event.

--
Regards,

OssieMac

Hi yet again Paul,

I have been doing some testing and found a problem that can send Excel into
a Flip and it has to close. Trying to use the RefEdit value (which is
actually a string) when it will return an invalid range then Excel flips.
Biggest problem is if it is blank and you try to use it but have also found
typing invalid values can do it.

The most complete test I can think of is to attempt to use it to assign to a
range variable and if an error is returned then it is invalid. If it is a
valid range then you can use that range variable to continue your validity
testing to see if it is within the desired range selections.

The following is an example.
Private Sub RefEdit1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim rngTest As Range

'Attempt to assign to a range variable
On Error Resume Next
Set rngTest = Range(Me.RefEdit1.Value)
If Err.Number 0 Then 'Invalid range
MsgBox "Invalid range. Must select a range."
Cancel = True 'Cancels the Exit
Exit Sub
End If

On Error GoTo 0 'Reset error trapping

'Can now use the range variable to test
'if it is within the valid ranges to select.
If rngTest.Column 4 Then
Cancel = True 'Cancels the Exit
MsgBox "Must Select from first 4 columns."
End If

End Sub


As before, feel free to get back to me.

--
Regards,

OssieMac

I've tried using your code 'as is', but get exactly the same result.

I've been using the control to select a range on the screen rather than
entering text, so there's little chace that the text in the control is
invalid as a range.

Even placing a breakpoint on the very first line is either ignored or the
failure occurs before any code is run !!


--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"OssieMac" wrote:

Hi yet again Paul,

I have been doing some testing and found a problem that can send Excel into
a Flip and it has to close. Trying to use the RefEdit value (which is
actually a string) when it will return an invalid range then Excel flips.
Biggest problem is if it is blank and you try to use it but have also found
typing invalid values can do it.

The most complete test I can think of is to attempt to use it to assign to a
range variable and if an error is returned then it is invalid. If it is a
valid range then you can use that range variable to continue your validity
testing to see if it is within the desired range selections.

The following is an example.
Private Sub RefEdit1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim rngTest As Range

'Attempt to assign to a range variable
On Error Resume Next
Set rngTest = Range(Me.RefEdit1.Value)
If Err.Number 0 Then 'Invalid range
MsgBox "Invalid range. Must select a range."
Cancel = True 'Cancels the Exit
Exit Sub
End If

On Error GoTo 0 'Reset error trapping

'Can now use the range variable to test
'if it is within the valid ranges to select.
If rngTest.Column 4 Then
Cancel = True 'Cancels the Exit
MsgBox "Must Select from first 4 columns."
End If

End Sub


As before, feel free to get back to me.

--
Regards,

OssieMac

This works

Private Sub SrcRef_Exit(ByVal Cancel As MSForms.ReturnBoolean)
n = MsgBox("It works so far")
End Sub

But this doesn't

Private Sub SrcRef_Exit(ByVal Cancel As MSForms.ReturnBoolean)
n = MsgBox("It works so far")


Dim rngTest As Range

'Attempt to assign to a range variable
On Error Resume Next
Set rngTest = Range(Me.SrcRef.Value)
If Err.Number 0 Then 'Invalid range
MsgBox "Invalid range. Must select a range."
Cancel = True 'Cancels the Exit
Exit Sub
End If

On Error GoTo 0 'Reset error trapping

zUserEntry = 0
Call Valid_Range_Selection

End Sub

However, if I disable the call - it works fine again


--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"OssieMac" wrote:

Hi yet again Paul,

I have been doing some testing and found a problem that can send Excel into
a Flip and it has to close. Trying to use the RefEdit value (which is
actually a string) when it will return an invalid range then Excel flips.
Biggest problem is if it is blank and you try to use it but have also found
typing invalid values can do it.

The most complete test I can think of is to attempt to use it to assign to a
range variable and if an error is returned then it is invalid. If it is a
valid range then you can use that range variable to continue your validity
testing to see if it is within the desired range selections.

The following is an example.
Private Sub RefEdit1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim rngTest As Range

'Attempt to assign to a range variable
On Error Resume Next
Set rngTest = Range(Me.RefEdit1.Value)
If Err.Number 0 Then 'Invalid range
MsgBox "Invalid range. Must select a range."
Cancel = True 'Cancels the Exit
Exit Sub
End If

On Error GoTo 0 'Reset error trapping

'Can now use the range variable to test
'if it is within the valid ranges to select.
If rngTest.Column 4 Then
Cancel = True 'Cancels the Exit
MsgBox "Must Select from first 4 columns."
End If

End Sub


As before, feel free to get back to me.

--
Regards,

OssieMac

Hi again Paul,

I have managed to lock up the system by calling another sub. It appears to
be loosing a connection and/or reference between the objects when calling
subs.

A few questions and comments.

What version of xl are you using? (I have been testing in xl2007 but I also
have xl2002 and can get access to xl2003 at times.)

What reference style are you using? (A1:B2 etc or R1C1 style because R1C1
has problems.

Have you tried putting the validation code in the Private Sub SrcRef_Exit
instead of calling another routine? With my testing it appears to work
probably because it does not loose the connections between objects.

Modeless forms also cause lockups. Need to have showModal property = true
(or when showing the form with code it must be modal.)

Can you post the code you are using for the validation then perhaps I can do
some further testing.

Any errors in the code do not necessary cause the code to stop at the error.
It either ignores the sub and it does not run or locks up the system.
Therefore compile all code before running. (click on Debug - compile.)

--
Regards,

OssieMac

Hi OssieMac

I'm using XL2003
Reference style is A1:B2
I have tried with the validation code within the _Exit sub
Modal is set to True

I'll post the code once I've stripped out the comments (otherwise there's
pages !!)




--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"OssieMac" wrote:

Hi again Paul,

I have managed to lock up the system by calling another sub. It appears to
be loosing a connection and/or reference between the objects when calling
subs.

A few questions and comments.

What version of xl are you using? (I have been testing in xl2007 but I also
have xl2002 and can get access to xl2003 at times.)

What reference style are you using? (A1:B2 etc or R1C1 style because R1C1
has problems.

Have you tried putting the validation code in the Private Sub SrcRef_Exit
instead of calling another routine? With my testing it appears to work
probably because it does not loose the connections between objects.

Modeless forms also cause lockups. Need to have showModal property = true
(or when showing the form with code it must be modal.)

Can you post the code you are using for the validation then perhaps I can do
some further testing.

Any errors in the code do not necessary cause the code to stop at the error.
It either ignores the sub and it does not run or locks up the system.
Therefore compile all code before running. (click on Debug - compile.)

--
Regards,

OssieMac

_Exit Code :
Private Sub SrcRef_Exit(ByVal Cancel As MSForms.ReturnBoolean)
nRangeOK = Valid_Range_Selection
Select Case nRangeOK
Case 0
' This changes the visible or enabled status of other controls on the form,
but removing it has no effect on the failure or otherwise
Call Set_Control_Status
Case 1
pCancel = True
Case 2
zDoIt = False
Unload Me
End Select
End Sub



Validation Code :
Function Valid_Range_Selection(Optional pMessage, Optional pPopulate)
' Valid_Range_Selection is the value returned to the calling code to
determine the validation and selection
' 0 - Validation OK
' 1 - Validation failed - user selected to retry
' 2 - Validation failed - user selected to cancel import
Valid_Range_Selection = 0
cErrorText = ""

' If zFirst = True And nSourceColumns = 1 And nSourceRows = 1 Then
' If zFirst = True Then
' Exit Function
' End If

If IsMissing(pMessage) Then pMessage = True
If IsMissing(pPopulate) Then pPopulate = True

nSourceCol = Selection.Column
nSourceColumns = Selection.Columns.Count
nSourceRows = Selection.Rows.Count

GoSub Check_Range_Size

GoSub Check_No_Blanks

GoSub Check_No_Duplicates

If Valid_Range_Selection = 0 And pPopulate = True Then
Call Populate_Source_Data_View
End If

If Valid_Range_Selection < 0 And pMessage = True Then
n = MsgBox(cErrorText, vbstop + vbOKOnly, "Table import")
Valid_Range_Selection = 1
End If
Exit Function

Check_Range_Size:
' Check that the range specified includes at least two columns and at least
two rows
If nSourceColumns < 2 Or nSourceRows < 2 Then
If Len(Trim(cErrorText)) < 0 Then cErrorText = cErrorText + Chr(13)
cErrorText = cErrorText + "Range must include at least two columns
and at least two rows"
Valid_Range_Selection = 1
End If
Return

Check_No_Blanks:
' Check that there are no blank column headers
lBlankErr = False
For nCols = nSourceCol To nSourceCol + nSourceColumns - 1
If IsEmpty(myData(0, nCols - nSourceCol)) And lBlankErr = False Then
If Len(Trim(cErrorText)) < 0 Then cErrorText = cErrorText +
Chr(13)
cErrorText = cErrorText + "Column headers cannot be blank"
lBlankErr = True
Valid_Range_Selection = 1
End If
Next
Return

Check_No_Duplicates:
' Check that there are no duplicate column headers
lDuplicate = False
For nCols = nSourceCol To nSourceCol + nSourceColumns - 1
For nCols2 = nSourceCol To nSourceCol + nSourceColumns - 1
If myData(0, nCols - nSourceCol) = myData(0, nCols2 -
nSourceCol) _
And nCols < nCols2 And lDuplicate = False Then
If Len(Trim(cErrorText)) < 0 Then cErrorText =
cErrorText + Chr(13)
cErrorText = cErrorText + "Column headers cannot be
duplicated"
lDuplicate = True
Valid_Range_Selection = 1
End If
Next
Next
Return

End Function



--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"Paul" wrote:

Hi OssieMac

I'm using XL2003
Reference style is A1:B2
I have tried with the validation code within the _Exit sub
Modal is set to True

I'll post the code once I've stripped out the comments (otherwise there's
pages !!)




--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"OssieMac" wrote:

Hi again Paul,

I have managed to lock up the system by calling another sub. It appears to
be loosing a connection and/or reference between the objects when calling
subs.

A few questions and comments.

What version of xl are you using? (I have been testing in xl2007 but I also
have xl2002 and can get access to xl2003 at times.)

What reference style are you using? (A1:B2 etc or R1C1 style because R1C1
has problems.

Have you tried putting the validation code in the Private Sub SrcRef_Exit
instead of calling another routine? With my testing it appears to work
probably because it does not loose the connections between objects.

Modeless forms also cause lockups. Need to have showModal property = true
(or when showing the form with code it must be modal.)

Can you post the code you are using for the validation then perhaps I can do
some further testing.

Any errors in the code do not necessary cause the code to stop at the error.
It either ignores the sub and it does not run or locks up the system.
Therefore compile all code before running. (click on Debug - compile.)

--
Regards,

OssieMac