# Transforming the case of text using Excel VBA

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?

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.

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