Transforming the case of text using Excel VBA

Question

I created a simple user form for my VBA course.

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.