Setting cell format with VBA

[Les Isaacs]

Hi All

I have inherited a complex Access2010 app that includes a facility to create an also-complex Excel file which has a lot of very specific attributes. The Excel file is based on an Excel 'template', which has a lot of VBA procedures to test data entered, and obviously the 'template' specifies the format of the cells (i.e. text, number, etc.).

Some of the cells have an associated dropdown, with value lists, and these are specified within the Access VBA that creates the Excel file - e.g.

With range("A" & lngRowCount).Validation
.Add type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Mr,Mrs,Miss,Ms,Dr"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Title"
.ErrorTitle = "Invalid Title"
.ErrorMessage = "You must enter a valid title (see dropdown list)"
End With

This all works fine ..... EXCEPT:
In the above example, if the user tries to enter "mr", this is rejected by the validation (because it's not "Mr"). I know I could add "mr" to the dropdown list, but it would obviously be preferable to amend "mr" to "Mr" (using Propercase?) before the validation kicks in. Can this be done?

I have considered trying to do this as part of the Format within the Excel 'template' file, or within the VBA of the Excel 'template' file, or within the VBA in Access that creates the file (e.g. alongside the validation code above, etc.), but I can't seem to find a way of achieving what I need.

Hope someone can help.
Thanks in advance
Les

 

[clive2002]

I don't think it will be possible to do this on the fly, eg replace a mr with Mr as its typed by the user, certainly not using validation.

Choices would be.

1 - Add mr to validation

then either live with have both mr and Mr or loop through after entry and replace mr with Mr or triger an on event piece of code to replace mr with Mr each time a cell is updated.

2 - Force user to enter Mr to start with, which is what you have now.