Search code examples
excelvbauserform

How to handle blank/Invalid data for RefEdit control in VBA userform


I am using a Excel VBA userform to change the font case as below picture. When RefEdit control have correct Range, then it is working fine. But if I click "Apply" button keeping RefEdit blank/ only Space/ any word(invalid Range), Userform disappear without showing any error notification.

For Uppercase code:-

Sub UpperCaseFont()
        For Each x In Range(CaseRefEdit.Value)
        If Not IsEmpty(x.Value) Then
            x.Value = UCase(x.Value)
        End If
        Next
MsgBox "Done"
End Sub

LowerCase code:-

Sub LowerCaseFont()
    For Each x In Range(CaseRefEdit.Value)
    If Not IsEmpty(x.Value) Then
        x.Value = LCase(x.Value)
    End If
    Next
MsgBox "Done"
End Sub

Propercase code:-

Sub ProperCaseFont()
    For Each x In Range(CaseRefEdit.Value)
    If Not IsEmpty(x.Value) Then
        x.Value = WorksheetFunction.Proper(x.Value)
    End If
    Next
End Sub

CommandButton code:-

Private Sub CaseApplyCommandButton_Click()
    If UpperCase = True Then Call UpperCaseFont
    If LowerCase = True Then Call LowerCaseFont
    If ProperCase = True Then Call ProperCaseFont

For that reason, I have tried to modified as below, But still i am facing the problem that if RefEdit is blank and I click on "Apply" button then userform disappear and also found that other all userform start unknown problem to initialize.

Private Sub CaseApplyCommandButton_Click()
'Font Case
Dim Rng As Range
On Error Resume Next
Set Rng = Range(Me.CaseRefEdit.Value)
MsgBox Rng
On Error GoTo 0
If Rng Is Nothing Then
    MsgBox "Select the Cells to change the case"
Else
    If UpperCase = True Then Call UpperCaseFont
    If LowerCase = True Then Call LowerCaseFont
    If ProperCase = True Then Call ProperCaseFont
End If

End Sub

I have found that problem is started when I add below code:-

Private Sub CaseRefEdit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Range(CaseRefEdit.Value).Select
End Sub

As per my understanding when RefEdit is not giving any range for input any number or word or keeping blank, then userform disappear. Any solution for this?

enter image description here


Solution

  • On the second question. The [if IsError (range ("Hello") then ...] expression first evaluates the range ("Hello"), and if it is invalid, an error occurs before calling the function. Therefore, it is better to pass the address of the range to the IsError function, and calculate the range inside the function and determine its correctness.

    Function IsError(addr As String) As Boolean
        Dim rng As Range
        On Error Resume Next
        Set rng = Range(addr)
        IsError = rng Is Nothing
    End Function
    
    Sub test1()
        If IsError("%$%W34") Then
            Debug.Print "Range is invalid"
        Else
            Debug.Print "Range is correct"
        End If
    End Sub