Text largely or small!

Over a selection (UserForm) it is selected whether with an existing text all characters are represented largely or small. Or only the first character are largely represented. A further possibility offers the selection to write each individual alphabetic character after a dot largely. The files at the end of the article are Excelfiles of the version 2003 and 2007.


The following code belonged In "UserForm1"

Option Explicit
Private Sub Image1_Click()
Me.OptionButton1.Value = False
Me.OptionButton2.Value = False
Me.OptionButton3.Value = False
Unload UserForm1
End Sub
Private Sub Image2_Click()
If Me.OptionButton1.Value = True Then
Me.OptionButton1.Tag = "Go"
ElseIf Me.OptionButton2.Value = True Then
Me.OptionButton2.Tag = "Go"
ElseIf Me.OptionButton3.Value = True Then
Me.OptionButton3.Tag = "Go"
ElseIf Me.OptionButton4.Value = True Then
Me.OptionButton4.Tag = "Go"
End If
Me.Hide
End Sub
Private Sub UserForm_Activate()
Me.OptionButton1.Value = True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
MsgBox "No way!", vbCritical
Cancel = 1
End If
End Sub

The following code belonged In "Module1"

Option Explicit
Public Sub UF_Show()
Dim rngRange As Range
Dim varTMP As Variant
Dim rngCell As Range
Dim lngTMP As Long
On Error GoTo Fin
Set rngRange = Application.InputBox _
("Mark a range!", , , , , , , 8)
UserForm1.Show
With UserForm1
If .OptionButton1.Tag = "Go" Then
rngRange = UCase(rngRange)
ElseIf .OptionButton2.Tag = "Go" Then
rngRange = LCase(rngRange)
ElseIf .OptionButton3.Tag = "Go" Then
rngRange = StrConv(rngRange, vbProperCase)
ElseIf .OptionButton4.Tag = "Go" Then
For Each rngCell In rngRange
varTMP = Split(rngCell.Text, ".")
For lngTMP = 0 To Ubound(varTMP)
If varTMP(lngTMP) <> "" And _
Left(varTMP(lngTMP), 1) <> " " Then
varTMP(lngTMP) = StrConv(Left(varTMP(0), 1), _
vbProperCase) & Right(varTMP(0), _
Len(varTMP(0)) - 1) & "."
ElseIf varTMP(lngTMP) <> "" Then
varTMP(lngTMP) = StrConv _
(Left(varTMP(lngTMP), 2), _
vbProperCase) & Right(varTMP(lngTMP), _
Len(varTMP(lngTMP)) - 2) & "."
End If
Next lngTMP
rngCell.Value = Join(varTMP)
Next rngCell
End If
End With
Fin:
Unload UserForm1
Set rngRange = Nothing
End Sub


Sample 2003

Sample 2007

Kommentare

Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...