PDA

View Full Version : Solved: Excel VBA changing case



sethu29
05-14-2012, 08:14 AM
hi

i tried a macros for changing case in excel through userform. when im pressing alt+f8 and running the macros it showing the error. Can any one rectify the error.

Bob Phillips
05-14-2012, 08:33 AM
You need to use the correct quote characters

Private Sub CancelButton_Click()
Unload UserForm1
End Sub

Private Sub OKButton_Click()

Application.ScreenUpdating = False

' Exit if a range is not selected
If TypeName(Selection) <> "Range" Then Exit Sub

' Upper case
If OptionUpper Then
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = StrConv(cell.Value, vbUpperCase)
End If
Next cell
End If

' Lower case
If OptionLower Then
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = StrConv(cell.Value, vbLowerCase)
End If
Next cell
End If

'Proper case
If OptionProper Then
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = StrConv(cell.Value, bProperCase)
End If
Next cell
End If

Unload UserForm1
End Sub

snb
05-14-2012, 09:14 AM
or

Private Sub OKButton_Click()
For Each cl In Selection.SpecialCells(2)
cl.Value = StrConv(cl, IIf(OptionUpper, 1, IIf(OptionLower, 2, 3)))
Next

Hide
End Sub

sethu29
05-14-2012, 10:49 PM
thank you . Its working fine