1
\$\begingroup\$

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?

\$\endgroup\$
1
\$\begingroup\$

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.