I need a macro that fills in a cell with an "X" if I click it.

[Guest]

I'm trying to create a macro that I can apply to worksheets where I need to
classify terms into one or more categories. It would be great if I could just
click in the cells that represent the category for each term instead of
clicking and then hitting X.

Thanks,
Ivan

 

[Norman Jones]

Hi Ivan,

Try:

'=============>>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range

Set Rng = Me.Range("A1:A100") '<<=== CHANGE
Set Rng = Intersect(Rng, Target)

If Not Rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
Rng.Value = "X"
End If

XIT:
Application.EnableEvents = True
End Sub
'<<=============

This is worksheet event code and should be pasted into
the worksheets's code module (not a standard module
and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

 

[Guest]

Norman-
This worked like a charm and introduced me to a whole new world of Excel
functionality! Thanks 10^6

-Ivan

 

[Gord Dibben]

Ivan

To have the code operate on all worksheets in the workbook you could alter
Norman's code and place it in the Thisworkbook module.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As _
Range)

Dim Rng As Range

Set Rng = ActiveSheet.Range("A1:A100") '<<=== CHANGE
Set Rng = Intersect(Rng, Target)

If Not Rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
Rng.Value = "X"
End If

XIT:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP