VBA Option / Command Button Problem

[Southanesq]

I’m a n00b with VBA, so please bear with me.<o></o>
<o> </o>
I’m running three separate macro(s) that change a selected range of text to either lower, upper or proper (capital letter followed by lower) case.<o></o>
<o> </o>
The code I’m using x3 is as follows: -<o></o>
<o> </o>
Sub ChangeCaseLower()<o></o>
Dim Rng As Range<o></o>
On Error Resume Next<o></o>
Err.Clear<o></o>
Application.EnableEvents = False<o></o>
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _<o></o>
xlTextValues).Cells<o></o>
If Err.Number = 0 Then<o></o>
' Rng.Value = StrConv(Rng.Text, vbUpperCase)<o></o>
' Rng.Value = StrConv(Rng.Text, vbLowerCase)<o></o>
' Rng.Value = StrConv(Rng.Text, vbProperCase)<o></o>
End If<o></o>
Next Rng<o></o>
Application.EnableEvents = True<o></o>
End Sub<o></o>
<o> </o>
… With the appropriate Rng.Value for each action.<o></o>
<o> </o>
I’ve created a UserForm with OptionButton(s) for each change of case.<o></o>
<o> </o>
If I paste the above code into the OptionButton, the text will change as soon as I run the form and make the selection. However, I’ve created a CommandButton that I want to use to change the text.<o></o>
<o> </o>
What I want to do is select some cells with text; run the UserForm; select the appropriate option and then press the CommandButton to run the selection I’ve made.<o></o>
<o> </o>
Any help would be appreciated. <o></o>

 

[Andrew Poulsom]

Test which OptionButton is checked in the code for your CommandButton, eg:

If OptUpper.Value = True Then
    Rng.Value = StrConv(Rng.Text, vbUpperCase)
ElseIf OptLower.Value = True Then
    Rng.Value = StrConv(Rng.Text, vbLowerCase)
Else
    Rng.Value = StrConv(Rng.Text, vbProperCase)
End If

Change the OptionButton references to suit.

 

[Southanesq]

Andrew,

Thank you very much for your reply. ray:

With your help, I managed to get the behaviour I wanted. I've learned a lot.

I have a few follow-up questions, though.

Once I click CommandButton1 to run my choice I would like to unload the UserForm.

I've come up with this code: -

If CommandButton1.Value = True Then
Unload UserForm1

End If

... to no avail. Do you have an alternative suggestion?

If so, where would it go?

My new code looks like this: -

Private Sub CommandButton1_Click()
If OptionButton1.Value = True Then
Dim Rng As Range
On Error Resume Next
Err.Clear
Application.EnableEvents = False
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
If Err.Number = 0 Then
Rng.Value = StrConv(Rng.Text, vbUpperCase)
End If

Next Rng
Application.EnableEvents = True
End If

If OptionButton2.Value = True Then
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
If Err.Number = 0 Then
Rng.Value = StrConv(Rng.Text, vbLowerCase)
End If

Next Rng
Application.EnableEvents = True

End If
If OptionButton3.Value = True Then
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
If Err.Number = 0 Then
Rng.Value = StrConv(Rng.Text, vbProperCase)
End If

Next Rng
Application.EnableEvents = True
End If

End Sub

Finally, I've noticed that if I run these scripts with only one cell selected it changes the case of the entire work sheet!

I've created another UserForm as an error message (selectRange), but I am not sure how to change my existing code to link to it if only one cell is selected.

Thanks in advance.

 

[John_w]

You've come across a couple of Excel VBA gotchas!

Once I click CommandButton1 to run my choice I would like to unload the UserForm.

I've come up with this code: -

If CommandButton1.Value = True Then
Unload UserForm1

End If

... to no avail. Do you have an alternative suggestion?

If so, where would it go?

The Value property of a CommandButton is always False, so just use:

Private Sub CommandButton1_Click()
    Unload UserForm1
End Sub

Finally, I've noticed that if I run these scripts with only one cell selected it changes the case of the entire work sheet!

Yes, if you select only a single cell Excel assumes you want to work with the entire worksheet. The following code checks for Selection.Count being 1 (i.e. 1 cell selected) and converts that cell, otherwise it converts all the selected cells.

Private Sub Convert()
    Dim rng As Range
    
    If OptionButton1.Value Then
        If Selection.Count = 1 Then
            Selection.Value = StrConv(Selection.Text, vbUpperCase)
        Else
            Application.EnableEvents = False
            On Error Resume Next
            For Each rng In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
                rng.Value = StrConv(rng.Text, vbUpperCase)
            Next rng
            On Error GoTo 0
            Application.EnableEvents = True
        End If
    End If
    
    If OptionButton2.Value Then
        If Selection.Count = 1 Then
            Selection.Value = StrConv(Selection.Text, vbLowerCase)
        Else
            On Error Resume Next
            For Each rng In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
                rng.Value = StrConv(rng.Text, vbLowerCase)
            Next rng
            On Error GoTo 0
            Application.EnableEvents = True
        End If
    End If
    
    If OptionButton3.Value Then
        If Selection.Count = 1 Then
            Selection.Value = StrConv(Selection.Text, vbProperCase)
        Else
            On Error Resume Next
            For Each rng In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
                rng.Value = StrConv(rng.Text, vbProperCase)
            Next rng
            On Error GoTo 0
            Application.EnableEvents = True
        End If
    End If

End Sub

 

[Southanesq]

Hi John

Thanks for your help.

I knew I was missing the obvious with the CommandButton

Your code for selecting a single cell worked a treat, and I've spend this morning using it on other scripts I'm developing.

Man, I've learnt more in two days here than in the last couple of months!

Thanks again!