Capitalizing a Variable from an Input Box
Posted by David Holstein on April 09, 2000 7:12 AM
Hi,
I am using an INPUT BOX to extract a value from the user to rename a Worksheet, input box syntax used is as follows:
VARIABLE=Val(Input("Prompt","Title"))
What I would like to do is CAPITILIZE each word that is entered into the input box (could be 2 to 3 words used) eg. Maintenance Info
Any help would be must appreciated.
Regard
David
Posted by Ivan Moala on April 09, 2000 8:44 PM
David
I think your syntax should be;
Sub tester()
Dim VARIABLE
VARIABLE = (InputBox("Prompt", "Title"))
VARIABLE = Format(VARIABLE, ">")
End Sub
Ivan
Posted by David Holstein on April 10, 2000 5:38 AM
Re: Thanks for Your Help
Thankyou, this worked
Regards
David
Posted by David Holstein on April 10, 2000 7:12 AM
Re: Thanks for Your Help => is there a way to do this only to 1st Letter of each word
Hi,
Sorry i thought I was finished but I need a way to only captilize the first letter of each word.
Posted by Ivan Moala on April 13, 2000 1:58 AM
Re: Thanks for Your Help => is there a way to do this only to 1st Letter of each word
Hi David try this
Sub Tester()
Dim VARIABLE As String
Dim TempText As String
Dim x As Integer
Dim Holder() As String
Dim CapNext As Boolean
'\\\\\\\\\\\\ ROUTINE TO CAPITILISE 1ST LETTER OF INPUT \\\\\\\\\\\
' \
' DONE FOR DAVID 12/04/2000 \
' \
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
VARIABLE = (InputBox("Prompt", "Title"))
ReDim Holder(Len(VARIABLE))
For x = 1 To Len(VARIABLE)
'Check 1st letter
TempText = Mid(VARIABLE, x, 1)
'Test 1st position of string
If x = 1 And TempText <> " " Then TempText = Format(Mid(VARIABLE, x, 1), ">")
'Anything after a space should be Capitilised
If TempText = " " Then
CapNext = True
ElseIf CapNext Then TempText = Format(Mid(VARIABLE, x, 1), ">"): CapNext = False
End If
'Assign to seperate Holding variable
Holder(x) = TempText
Holder(0) = Holder(0) & Holder(x)
Next
VARIABLE = Holder(0)
'msg to test
MsgBox VARIABLE
End Sub
Ivan
Posted by Ivan Moala on April 13, 2000 3:16 PM
Try this instead
David
As you are renaming your worksheets you should
have (if you don't already have it) an error checking routine. Try this one.
Sub Tester()
Dim VARIABLE As String
Dim TempText As String
Dim x As Integer
Dim Holder() As String
Dim CapNext As Boolean
Dim TryAgain As Integer
'\\\\\\\\\\\\ ROUTINE TO CAPITILISE 1ST LETTER OF INPUT \\\\\\\\\\\
' \
' DONE FOR DAVID 12/04/2000 \
' \
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Again: VARIABLE = (InputBox("Prompt", "Title"))
If VARIABLE = "" Then End
ReDim Holder(Len(VARIABLE))
For x = 1 To Len(VARIABLE)
'Check 1st letter
TempText = Mid(VARIABLE, x, 1)
'Test 1st position of string
If x = 1 And TempText <> " " Then TempText = Format(Mid(VARIABLE, x, 1), ">")
'Anything after a space should be Capitilised
If TempText = " " Then
CapNext = True
ElseIf CapNext Then TempText = Format(Mid(VARIABLE, x, 1), ">"): CapNext = False
End If
'Assign to seperate Holding variable
Holder(x) = TempText
Holder(0) = Holder(0) & Holder(x)
Next
VARIABLE = Holder(0)
'msg to test
MsgBox VARIABLE
On Error GoTo ErrH
ActiveSheet.Name = VARIABLE
Exit Sub
ErrH:
TryAgain = MsgBox("Error#: " & Err.Number & Chr(13) & "Error msg: " & Err.Description & Chr(13) _
& Chr(13) & Chr(13) & "Do you want to try again?", vbCritical + vbYesNo, "Rename sheet Error")
If TryAgain <> vbYes Then End
GoTo Again
End Sub
Ivan
