Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set Charactor (2003)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Charactor (2003)

    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") = "

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Charactor (2003)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Charactor (2003)

    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 = "

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Charactor (2003)

    You've done it again Hans - Thanks

  6. #6
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Charactor (2003)

    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?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Charactor (2003)

    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).

  8. #8
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Charactor (2003)

    Hans, That seems to have fixed it - Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •