Changing cell value by clicking on the cell

[dlondono]

Hello,

I would like to know if you hava a macro to change a predefined value when you click on a cell.

I have three alternatives for the active cell: +, - and ?. And I want that when I click on the cell for first time, it shows me +. If I click the same cell for second time, it shows me -. Later ?, +, -, ?, +, etc.

Thank you,

David Londoño

 

[LTunnicliffe]

Try something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Application.EnableEvents = False
  If Target.Address = Range("A1").Address Then
    Select Case Target.Value
      Case "?"
        Target.Value = "+"
      Case "+"
        Target.Value = "-"
      Case "-"
        Target.Value = "?"
      Case Else
        Target.Value = "+"
    End Select
  End If
  Range("A2").Select
  Application.EnableEvents = True
End Sub

Put this code in the worksheet section of the VBE (ie. right click on the tab name in question and select view code then paste my code in the blank area to the right). Hope this helps!

 

[dlondono]

Thank you Tunnic,

I would like to add the following rules:
1. I need to apply this code to a range of cells... how can I change the target address to multiple cells?
2. I would like to activate the event when I click the active cell (the selection wouldn't be changing).

Thank you,

David

 

[Juan Pablo González]

David.

1. Put this instead of the current check

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

2. There's no event associated with that...the closest one that you have is the SelectionChange event OR the BeforeDoubleClick event...

 

[dlondono]

Thanks a lot.

 

[lacroixca]

This will do the job, without locking the on A1.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Application.EnableEvents = False
  If Target.Cells.Count = 1 Then
    If Not Intersect(Target, Range("I5:J30")) Is Nothing Then
      Select Case Target.Value
      Case ""
        Target.Value = "+"
      Case "+"
        Target.Value = "-"
      Case "-"
        Target.Value = ""
      Case Else
        Target.Value = "+"
      End Select
      Range("A1").Select
    End If
  End If
  Application.EnableEvents = True
End Sub

 

[CritterLes]

Update: I know this is a very old thread but it answered my original question and I would like to expand on it.
I see how to click on a cell and rotate through the options. What do I have to change so I can put multiple occurrences of this process on the same sheet.
I.e. Click through N,W.E.S. for direction column and then Click through Y & N for a Yes/No column.
Thank you for any assistance.

 

[jwktse]

Hi Tunnic,

I was wondering if you would know how I can click on a cell with list of values (just listed out, not pulldown) and then change a value in a target cell.

Please kindly advise.

Thanks,
Jeffrey

 

[My Aswer Is This]

You said:
can click on a cell with list of values

Your saying the cell has more then one value and you want to add to the value?

show me a example

 

[jwktse]

Dear Sir,

For example target cell to change the value is A2.

I have a list of cells with values:
Paris
Amsterdam
London
Hong Kong.

I click on Amsterdam and Amsterdam appear in the target cell A2.

Please kindly advise.

Thanks and regards,
Jeffrey