Transforming the case of text using Excel VBA

Question

I created a simple user form for my VBA course.

enter image description here

I then created the following VBA code:

Private Sub btnModify_Click()
Dim modRange As Range
Set modRange = Selection
Dim modString As String
modString = modRange.Value
 If obUpperCase.Value = True Then
 modRange.Value = UCase(modString)
 btnModify.Value = False
 ElseIf obLowerCase.Value = True Then
 modRange.Value = LCase(modString)
 btnModify.Value = False
 ElseIf obProperCase.Value = True Then
 modRange.Value = Application.Proper(modString)
 btnModify.Value = False
 End If
End Sub

Is it possible to split the btnModify_Click into two procedures?

The first procedure would check which OptionButton is clicked and return a "formatting" type.

The second would be the actual btnModify_Click. It would take the active selection and apply the formatting type returned from the first procedure.

My apologies for not posting any code along these lines but I don't have a grasp of how procedures work yet. I have an idea but this seems like a simple enough example to get me started (perhaps too simple?).

On a minor note, I don't like the style of the buttons. They have a classic look, and even though there is a time an place for this specific style, is there any way to obtain "better" looking buttons?

Answer 1

First, I'd get rid of the modify button. You can use option button Click events to handle the change. Something like this:

Private Sub opbLower_Click()
 ActiveCell = LCase(ActiveCell)
End Sub
Private Sub opbProper_Click()
 ActiveCell = Application.Proper(ActiveCell)
End Sub
Private Sub opbUpper_Click()
 ActiveCell = UCase(ActiveCell)
End Sub

To answer your question, you could also use the click events to return a format type for your btnmodify_click method to handle by creating a non local variable above all the code like this:

Dim format As String
Private Sub opbLower_Click()
 format = "lcase"
End Sub
Private Sub opbProper_Click()
 format = "ucase"
End Sub
Private Sub opbUpper_Click()
 format = "pcase"
End Sub

Then apply the condition in the btnmodify_click method based on the value of format. The first example is better though, it involves less work.

• Nahuatl: Oh man, your suggested method is just beautiful :'). Thank you for taking the time to help me out.