Populate a cell with a value simply by clicking on it

jvp123

New Member
Joined
May 5, 2012
Messages
9
Good afternoon.

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

Mr. SmithHighly EffectiveEffectiveDevelopingIneffective
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
Joined
Feb 6, 2007
Messages
3,092
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
Joined
May 5, 2012
Messages
9
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
Joined
Feb 6, 2007
Messages
3,092
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
Joined
Feb 6, 2007
Messages
3,092
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
Joined
May 5, 2012
Messages
9
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top