TextBox - Upper to Lower Case

[grady121]

I have a project that works well with a borrowed code from another file. It creates Text Boxes with their basic formatting settings set up by another UserForm containing several OptionButtons options to control various Default settings.

Using an InputBox to enter the initial text, one of the grouped OptionButtons is to offer the chance to change the Default Upper Case Text, to Lower Case. This is done by setting a DATA sheet value of “2” into cell “S47”. Unfortunately I can’t seem to get it work, or know how to modify the existing code to change it.

Can anyone help Newbie?

My abbreviated code as it stands:-

'Get Users input
MyText = UCase(InputBox("Enter TextBox text here.", "Textbox"))

If Len(MyText) = 0 Then MakeMyTB = False: Exit Sub 'allows cancellation

Application.ScreenUpdating = False
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 336#, 230, 142#, 70#).Select
Selection.ShapeRange.Name = "AATB"

If Application.Version < 12 Then 'Excel versions before 2007
Selection.Characters.Text = MyText
Else '2007
ActiveSheet.Shapes("GBTB").DrawingObject.Text = MyText
End If

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Orientation = xlHorizontal 'This line added to try and stop XL2007 placing text vertically
.Font.Size = 10
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.ForeColor.SchemeColor = 1
.ShapeRange.Line.Weight = 3
If Sheets("DATA").Range("S47").Value = "2" Then ' What do I do here? #####
End With

It hoped that the code will work with Excel 2003 to 2010 versions.

Any help appreciated.

 

[pboltonchina]

This will change everything to upper case

Sub UpperCase()
    Dim i As Variant
    For Each i In Selection
         i.Value = UCase(i.Value)
    Next i
End Sub

 

[Scott Huish]

i should be dimmed as Range though, not Variant