Populate a cell with a value simply by clicking on it

[jvp123]

Good afternoon.

I am developing an evaluation tool that has a 4 point scale (Highly Effective (4) to Ineffective (1)).

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Mr. Smith[/TD]
[TD]Highly Effective[/TD]
[TD]Effective[/TD]
[TD]Developing[/TD]
[TD]Ineffective[/TD]
[/TR]
[TR]
[TD]Trait 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trait 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trait 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trait 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










I would like to click on B2 and a "4" would show up. If I clicked on C2, a "3" would show up in C2 and the "4" from B2 would go away.
Additionally, there are two evaluations, so F - I has the same Highly Effective to Ineffective setup. I would like those cells to do the same. Lastly, there are 4 sets of "Traits", so rows 2-5 have one set, rows 12 - 16 may have another set of traits, etc. I would like those to do the same as well.

Let me know if you need more details. Just trying to keep the project as confidential as possible.

I am using Excel 2007.

 

[Kenneth Hobson]

Right click the sheet's tab, View Code, and paste:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim r As Long, c As Integer, x As Range
  Set x = Intersect(Target, Columns("B:E"))
  With Target
    c = .Column
    r = .Row
    If x Is Nothing Or Not (c >= 2 And c <= 5) Or r = 1 Then Exit Sub
    Application.EnableEvents = False
    Range("B" & r & ":E" & r).Clear
    Select Case True
      Case c = 2  'Column B
        .Value2 = 4
      Case c = 3
        .Value2 = 3
      Case c = 4
        .Value2 = 2
      Case c = 5
        .Value2 = 1
      Case Else
    End Select
    Application.EnableEvents = True
  End With
End Sub

 

[jvp123]

This is a great start, Kenneth. I ran it on the sample sheet and it worked perfectly.

Here are the actual cells that I need this to affect:

Columns G(4), H(3), I(2) and J(1) and K(4), L(3), M(2) and N(1)
Rows: 12-17, 23-27, 33-37, and 43-48

Also, will I have to run the macro every time I open the file or will it automatically run after I run it the first time and save the file?

 

[Kenneth Hobson]

The code runs each time a cell is selected. Intersect() determines if the cell selected is in expected range.

It may need another intersection to make it a bit easier to maintain and read for the extra needs. Another approach is to use Ifs or Select Cases. I will think on the best method.

 

[Kenneth Hobson]

I don't know what you need for the "columns". G4, H3, etc. are cells. So what should happen if one of those are selected? Maybe if you made an example and posted it to a shared site like box.net I could see what you need.

 

[jvp123]

The numbers in parentheses are the values that I would like to populate in those columns and rows. They weren't references to cells.

In cells G12 - G17, G23 - G27, G33 - G37, and G43 - G48, I would like to click on a cell and have a "4" appear
In cells H12 - H17, H23 H27, H33 - H37, and G43 - G48,I would like to click on a cell and have a "3" appear
In cells I12 - I17, I23 - I27, I33 - I37, and I43 - I48, I would like to click on a cell and have a "2" appear
In cells J12 - J17, J23 - J27, J33 - J37, and J43 - J48, I would like to click on a cell and have a "1" appear

I would like this same thing to occur for the same rows in Columns K-N