VBA Code required to Force Formatting of a worksheet.

[robertguy]

Hi,

can any advise of the VBA Code I need to Force Formatting of
worksheet.

e.g. I require Column 'B' to only accept a date format in the format
DD/MM/YY

and say Column 'C' to have a currency format of 2 Decimal places wit
the '£' sign.

This is required to ensure the user cannot alter the format of thes
columns - via the Format command

Would the VBA code be entered via the worksheet tab (right click - vie
code)

Any help would be greatly appreciated


Many thanks


Ro

 

[Ron de Bruin]

Hi

Why do you not protect you sheet ???

 

[JE McGimpsey]

First, the way a date is entered has *nothing* to do with how it's
displayed, as long as it's a valid date. Only the cell numberformat
determines the display.

Second, you can't force a user to enter a value into a cell in a
particular way using VBA - macros don't operate while in Edit mode.

So your user can enter "1/2/3" and XL's parser will convert it to a date
(either 2 January 2003 or 1 February 2003, depending on the OS date
settings). Then the display engine will display it in the format set,
then VBA will be able to look at it.

You could use an inputbox or userform to have the user enter the data.
Then you could parse the string entered yourself.

If you protect your worksheet (Tools/Protection/Protect Worksheet...)
the user will not be able to change the format via the Format menu.
Unless he or she really wants to... worksheet protection is perfect for
this application, but is very easy to break.