MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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