Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Putting an X in a Clicked Cell.

Putting an X in a Clicked Cell

Written by Allen Wyatt (last updated January 2, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003


2

Wendy has a worksheet that has quite a bit of data in it, with the main data in the range C3:P312. She would like to have a macro that, if she clicks a cell in this data range, would put an "x" into the cell.

There is no event that Excel can recognize as a "click" on a cell. Perhaps the closest event is the SelectionChange event, which is triggered every time the cell selection changes. The event handler could then check to make sure that the cell selection is within the C3:P312 range, and then place an "x" in the cell if it is. The following event handler will do that:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range

    Set rInt = Intersect(Target, Range("C3:P312"))
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            rCell.Value = "x"
        Next
    End If
    Set rInt = Nothing
    Set rCell = Nothing
End Sub

There is a problem with this approach, however: Not only will the SelectionChange event trigger when you click on a different cell, it also triggers if you use the keyboard to move from one cell to another in the worksheet. This means that if you use the keyboard to move about the worksheet you will leave a trail of "x" characters in each cell you transit.

One way around this is to change the event that triggers the check and change of the cells. While Excel has no "click" event, there is a "double click" event. This means that you can change the cell on which you double click, as shown here:

Private Sub Worksheet_BeforeDoubleClick( _
            ByVal Target As Range, Cancel As Boolean)
    Dim rInt As Range
    Dim rCell As Range

    Set rInt = Intersect(Target, Range("C3:P312"))
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            rCell.Value = "x"
        Next
    End If
    Set rInt = Nothing
    Set rCell = Nothing
    Cancel = True
End Sub

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3378) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Putting an X in a Clicked Cell.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Determining If a Date and Time is within Working Hours

Excel is great at working with times and dates. Sometimes, though, it can be a bit tricky to figure out how to work with ...

Discover More

Where Do You Want Your Endnotes?

Endnotes can be placed in a couple of different places in your document, not just at the very end. Here's how you can ...

Discover More

Inserting a Dynamic Line Count

Word provides fields that allow you to insert a variety of informational items about your document into the document ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

Setting Row Height in a Macro

Macros can be used to change the formatting of your worksheet, if desired. One change you might want to make is to the ...

Discover More

Running a Macro When a Worksheet is Deactivated

You can easily configure Excel so that it runs a specific macro whenever a worksheet is deactivated. Just follow the easy ...

Discover More

Making Common Functions Available to Others

When you use macros to create functions, you might want to share those functions with others, particularly if they ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five less than 5?

2021-02-05 05:36:09

Willy Vanhaelen

In the second macro it makes no sense to use a loop to cope with multiple cell ranges. Try it, select a range and when you double click on any cell in the range, the range disappears and the clicked cell becomes the active cell. So the macro can be drastically simplified from 11 to 3 lines code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("C3:P312")) Is Nothing Then Exit Sub
Target.Value = "x"
Cancel = True
End Sub


2019-11-26 12:03:26

Mark Ellis

On the DoubleClick code, I would like for the Not rInt cells to still allow me to select the text instead of Nothing. Is there a way oto code this?


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.