Set Charactor (2003)

[jeffersdj]

Loungers, I need some expert assistance. I am developing an audit spreadsheet. I want to set the character in say cells c,d & e to a 'tick' and in d to a 'cross' regardless of what key is pressed on the keyboard. Is this possible?

If so, can anyone let me know how to do it?

[HansV]

Your description is contradictory (cell "d" should contain a tick and a cross at the same time).

It would imply that the user can never clear a tick or cross once it has been set. If that is what you want, you can use the Worksheet_Change event.
- Select the cells you want to force to be a tick or cross.
- Set the font to Wingdings (this ought to be available on all Windows PCs)
- Right click the sheet tab of the worksheet, and select View Code.
- Type or copy the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C1")) Is Nothing Then
Range("C1") = "

[jeffersdj]

Hans,

Thanks for the quick reply - sorry about the contradiction - should be D is a cross. Thinking about your comment regarding never being able to clear a tick or a cross, I think that I will need to be able to clear/delete as required. The range is fairly large i.e. c7:c500 - is the first range in your code the start cell and the second range the 'to' cell?

[HansV]

Here is a modified version that acts on C7:C500 (tick) and D7500 (cross). You can add other ranges yourself.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("C7:C500")) Is Nothing Then
For Each oCell In Intersect(Target, Range("C7:C500"))
If Not oCell = "" Then
oCell = "

[jeffersdj]

You've done it again Hans - Thanks

[jeffersdj]

Hans - I notice when I enter a character in a cell that it takes around 10 - 15 seconds before I can enter another character in another cell - is that because of the large range or is it just the way it is?

[HansV]

It depends on the complexity of your spreadsheet - in my test spreadsheet there is no delay at all. Perhaps you can speed things up by inserting a line

Application.EnableEvents = False

at the beginning of the event procedure (below Private Sub ...), and

Application.EnableEvents = True

at the end (above End Sub).

[jeffersdj]

Hans, That seems to have fixed it - Thanks