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?
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