[Beginner's] Adding a letter to a cell by clicking the cell

[pnumminen]

Could somebody give full code for a macro that would do this:

- By clicking a cell in a range of certain defined cells the cell
would be emptied and then a certain letter, e.g. the letter 'A', would
be defined as the value of the cell if the cell did not already have
the letter as its value;

- By clicking a cell in a range of the certain defined cells the cell
would be emptied if the cell already had the letter as its value.

Thanks!

 

[CurlyDave]

So if the cell is "A" then delete it, If it does not have "A" then
make it an "A"...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
If Target = "A" Then
Target = ""
Exit Sub
ElseIf Target <> "A" Then Target = "A"
End If
End If
End Sub

This goes into the worksheet Module, and only works when you select
the range from A1:A10
http://www.contextures.com/xlvba01.html#Worksheet

 

[Tom Hutchins]

Try this:

Dim MyRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Define the range the macro should impact.
Set MyRng = ActiveSheet.Range("A1:B10")
'If more than one cell was selected, do nothing.
If Target.Cells.Count > 1 Then Exit Sub
'If the selected cell is in MyRng...
If Not Intersect(Target, MyRng) Is Nothing Then
'If the cell's value is A, clear it.
If Target.Value = "A" Then
Target.Clear
'Otherwise, set its value to A.
Else
Target.Value = "A"
End If
End If
'Free the object variable.
Set MyRng = Nothing
End Sub

Right-click on the tab of the sheet where this macro should work, then
select 'View code'. The Visual Basic Editor opens. Paste the code above in
the big empty window. Edit the Set MyRng statement to have the correct range.
Select File >> Close to return to regular Excel.

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[pnumminen]

Thanks, CurlyDave and Tom Hutchins.

Two problems:

- The letter is inserted/removed also when the cell is selected in a
manner other than clicking.
- You cannot run the action in the macro by clicking the cell another
time without selecting another cell in between.

 

[CurlyDave]

Thanks, CurlyDave and Tom Hutchins.

Two problems:

- The letter is inserted/removed also when the cell is selected in a
manner other than clicking.
- You cannot run the action in the macro by clicking the cell another
time without selecting another cell in between.

This is not a problem, it is a fact.....
You asked to change the cell when you clicked on it....

You can use right click event so it will only change when you right
click the cell, not when you select it.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Cancel = True
If Target = "A" Then
Target = ""
Exit Sub
ElseIf Target <> "A" Then Target = "A"
End If
End If
End Sub