How to ensure correct text formatting in VBA?

[Johnagall]

Hi There,

My first post on Mr Excel and I'm very very new to VBA so please be gentle

I would like to know how to use VBA to ensure that data is entered in the correct format in a user form.

For example I would like to make sure that names are entered in title case as opposed to block capitals or small text. Is there a way that I can do this?

To explain a little better I would like ensure that if a name was entered into a userform like this: "JOHN smith"; that it actually is entered into the spreadsheet like this: "John Smith".

Likewise I would like to ensure that dates are converted into my chosen format of 27th July 2011 as opposed to any other format.

Can this be done? Any advice would be greatly appreciated.

 

[Tom Urtis]

You can tell users until you are blue in the face about how you want them to enter data and they will enter it every way imaginable other than the way you tell them.

Regarding your first question, if the name is entered into TextBox1 and you want it to be in Proper case in cell A1, then this is one way to accomplish that, presumably with the Click event of some Command Button:

Range("A1").Value = WorksheetFunction.Proper(TextBox1.Value)

As to the second question, along those lines, send the date entered from (I am assuming again) TextBox2 to cell A2, and format it as you want in the code, like this for example:

With Range("A2")
.Value = TextBox2.Value
.NumberFormat = "DD MMM YYYY"
End With

 

[Johnagall]

Thanks for such a quick response.

Totally agree about trying to tell people how to enter text!

So the 'proper' command tells excel that the text should be in title case then?

If so is there another command for BLOCK Capitals?

Thanks again

 

[Rick Rothstein]

Just to follow up on Tom's posting and your additional question. Instead of calling out to the worksheet for its PROPER function, you can use VB's built-in function...

Range("A1").Value = StrConv(TextBox1.Value, vbProperCase)

If you want upper case lettering, you can use VB's UCase function...

Range("A1").Value = UCase(TextBox1.Value)

As for the number format you requested, I noticed you asked for an ordinal suffix on the day number. Try using this NumberFormat assignment instead of the one Tome suggested...

DayNumber = Day(TextBox1.Value)
.NumberFormat = "DD""" &  Mid$("thstndrdthththththth", 1 - 2 * ((DayNumber) Mod 10) * (Abs((DayNumber) Mod 100 - 12) > 1), 2) & """ MMMM YYYY"

 

[Tom Urtis]

If so is there another command for BLOCK Capitals?

Yes, you can use Ucase, example:

Range("A1").Value = UCase(TextBox1.Text)

 

[Johnagall]

Gentleman; FANTASTIC.

Thanks