# Populate a cell with a value simply by clicking on it

#### jvp123

##### New Member
Good afternoon.

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

 Mr. Smith Highly Effective Effective Developing Ineffective Trait 1 Trait 2 Trait 3 Trait 4

<tbody>
</tbody>

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

##### Well-known Member
Right click the sheet's tab, View Code, and paste:
Code:
``````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

##### New Member
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

##### Well-known Member
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

##### Well-known Member
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

##### New Member
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

1,082,131
Messages
5,363,337
Members
400,726
Latest member

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...