Click a cell to make a number appear.

February 13, 2011 at 11:22:50
Specs: Windows Vista
Evaluation page. Left to right, boxes with the numbers 1-5 in a box.
I would like to just click on a cell and have it equal one of the boxes above.
Click on first cell, a 1 appears...next line go to fifth box, click and have a 5 appear.

See More: Click a cell to make a number appear.

Report •


#1
February 13, 2011 at 19:45:17
It would help if you provided a little more information, such as what you mean by "boxes with the numbers 1-5 in a box."

What kind of "boxes"? Text boxes? Cells with borders? Something else?

If they are simply cells with borders, what columns are they in?

How many rows do you want this to happen in?

i.e. A2:E10, etc.

If the user clicks a cell to choose a e.g. "1" do you want to give them the opportunity it change it? (I assuming you only want one number per row)

If there is anything else you think we might need to know to "customize" a solution for you, please share it.

Keep in mind that we can't see your spreadsheet from where we're sitting.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 14, 2011 at 05:33:59
E:1 has a 1 in the cell by itself
F:1 has a 2 in the cell by itself
G:1 has a 3 in the cell by itself
H:1 has a 4 in the cell by itself
I:1 has a 5 in the cell by itself
These are numbers that depict how well a person is doing in a particular part of their job.
The above numbers are only a reference of number needs to be placed in the boxes beneath them where the questions are.
Each job description has a grading scale like the one above. So, under E, F, G, H and I, will be the same 1-5 cells to pick from. E will only have the number 1 in the column to choose from, F will only have the number 2 to choose...etc. Instead of putting in the number (i.e. the number 1 in the box where a 1 goes ect...I want to just click on the box for the 1 or the next box for the 2 and so on.

I am articulate and intelligent...however, when it comes down to explaining excel to those who know all of the "terms", I am an idiot. My apologies if my description does not help. I tried my best to explain it this time.
God bless and have a nice day. Proper


Report •

#3
February 14, 2011 at 07:33:08
re: "E:1 has a 1 in the cell by itself"

When posting in a forum, the proper syntax for a single cell is E1 (no colon).

For a range of cells, use E1:I1, with a colon separating the starting and ending cells of the range, just like Excel would do in a formula.

By using those syntaxes, we'll all understand what we're talking about.

That said, you didn't answer this question:

"How many rows do you want this to happen in?"

Is there some set range that you want this to happen in or are you willing to settle for it happening in all rows in Columns E:I?

For now, Right-Click the sheet tab for the sheet where you want this happen, choose View Code and paste this into the pane that opens.

If you click in any cell in Columns E through I except for Row 1 (E1:I1), the cell will display the value from Row 1 of that column. If you want to exclude certain rows, you'll need to be specific as to the ranges (in other words, answer the question I posed above.)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If more than one cell was selected, don't do anything
  If Selection.Cells.Count > 1 Then Exit Sub
'Check to if the selected cell is in Columns E:I
  If Not Intersect(Target, Range("E2:I" & Rows.Count)) Is Nothing Then
'Clear E:I of the row in which the cell was selected
'so a different selection can be made
    Range("E" & Target.Row & ":I" & Target.Row).ClearContents
'Get the number from Row one the selected Column
    Target = Cells(1, Target.Column)
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 14, 2011 at 10:56:31
(in other words, answer the question I posed above.) Again, my deepest apologies, kind sir.

1 - 5 so, only 5 particular columns. In particular: (E, G, I, K & M)

E20,E30,E39,E48,E57,E66,E75,E84,E93,E102 (Click any of these to enter a "1")
G20,G30,G39,G48,G57,G66,G75,G84,G93,G102 (Ditto "2")
I20,I30,I39,I48,I57,I66,I75,I84,I93,I102 (Ditto "3")
K20,K30,K39,K48,K57,K66,K75,K84,K93,K102 (Ditto "4")
M20,M30,M39,M48,M57,M66,M75,M84,M93,M102 (Ditto "5")

Proper


Report •

#5
February 14, 2011 at 12:17:05
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If more than one cell was selected, don't do anything
  If Selection.Cells.Count > 1 Then Exit Sub
'Check to if the selected cell is in the desired range
  If Not Intersect(Target, Range("E2:I102")) Is Nothing Then
   Select Case Target.Row
    Case 20, 30, 39, 48, 57, 66, 75, 84, 93, 102
'Clear E:I of the row in which the cell was selected
'so a different selection can be made
     Range("E" & Target.Row & ":I" & Target.Row).ClearContents
'Get the number from Row one the selected Column
     Target = Cells(1, Target.Column)
   End Select
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 19, 2011 at 09:59:50
I understand that you are having problems with the code I offered.

As I re-read some of your earlier posts, there appears to be some inconsistencies that I need clarification on.

In Response #2 you said:

E:1 has a 1 in the cell by itself
F:1 has a 2 in the cell by itself
G:1 has a 3 in the cell by itself
H:1 has a 4 in the cell by itself
I:1 has a 5 in the cell by itself

In other words, I assume it looks like this:


    E    F    G    H    I   
1   1    2    3    4    5

Also in Response #2, you said:

So, under E, F, G, H and I, will be the same 1-5 cells to pick from. E will only have the number 1 in the column to choose from, F will only have the number 2 to choose...etc.

However, in Response #4 I now see this:

...only 5 particular columns. In particular: (E, G, I, K & M)

What happened to wanting the numbers under E, F, G, H and I as asked for in Response #2?

The code I wrote was based on the columns you asked for in Response #2 (E:I)and the Rows you used in Response #4.

It will place the numbers from E1:I1 into Columns E:I, when you select (no need to double click) a cell in the rows you asked for, specifically rows 20, 30, 39, 48, 57, 66, 75, 84, 93 & 102. Nothing will happen if you select any other cell in the worksheet. Only Column E:I and only in the rows specified.

In other words, if you select E20 a 1 will appear. If you select G39 a 3 will appear. In addition, if you select a cell in Ex:Ix that already has a number in it, the original number will be deleted and the corresponding number for the currently selected cell will appear. That gives the user the ability to change their mind or make a correction. Once again, these numbers will only appear in the specific rows mentioned.

So, perhaps we have one or more of these 3 issues:

1 - You have not properly placed the code I offered into the VBA editor.
2 - You have modified the code such that it doesn't work as originally intended.
3 - The code was written for the columns you asked for in Response #2, but you really want it to work on the columns you used in Response #4.

If you'll clarify which of these you think is the problem we can take it from there.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
February 19, 2011 at 10:17:19
Response #2 was a general question assuming simplicity in the formula.
Huge mistake. So, in response #4, I gave you the exact cells.

Basic look of page...


C15 E15 G15 I15 K15 M15

Details 1 2 3 4 5
____________________________________________________________________

C20 Box Box Box Box Box
Attendance Dependability
E30 ETC ETC ETC ETC ETC
Punctuality
_____________________________________________________________________
I want to choose between one and five for the grade they get on each requirement.
All the boxes (cells) in comment #4 are the cells where the boxes are. Comment #4 tells a little more in detail each and every cell and what number I am looking for in each.
So, if I click on E20, I want a 1 to go in...likewise, M30 would be a 5.

I hope this answers your question. Again, thank you.

Proper


Report •

#8
February 19, 2011 at 10:24:42
Sorry, the thread didn't post the way I typed it.
Don't worry about anything in the C column...that is where the requirements are listed.
I think you understand though.

Report •

#9
February 19, 2011 at 10:41:51
I will work on your project as time allows.

re: "Sorry, the thread didn't post the way I typed it."

Perhaps you have not had chance to read the How To referenced in my signature line.

Why not take few minutes to read it before posting any more data? It will explain how to use the features of this web site to get your data to hold it's position when posted.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
February 19, 2011 at 10:54:51
In the rows where you want the numbers to appear, will there be anything in columns F, H, J, K or L that we need to be concerned with?

The reason I ask is that for the portion of the code that will clear the previous entries, it is much easier to clear the entire range (E:M) than to skip every other column.

e.g. Row 20 is one of your required rows. If F20, H20, etc. get cleared by the code when you select E20, will you lose any data or will those cells always be empty?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
February 19, 2011 at 13:02:41
DerbyDad03

Go to gmail
xxxxxxxxxxxx

This is the absolute best I can do...
I have emailed it to this account. I opened this account just for this situation.
Proper

edited by moderator: email address removed


Report •

#12
February 19, 2011 at 13:57:41
Please do not post email addresses in plain text in this forum. Use the Private Messaging feature to do that.

Even if you don't care about it being spammed, we don't want to become known as a site where email addresses can be harvested. Once the bad guys started hanging around, the site is open to all sorts of security issues.

I have removed the email address from your post.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
February 19, 2011 at 14:19:38
That's fine. It was only for this application.
After, it would have been deleted.
Thanks for watching out though.

Report •

#14
February 19, 2011 at 14:40:08
You appear to have missed my point. We don't care what you do with the email address, we care that a plain text email address was posted in this forum.

If spammers know that people are posting email addresses in a forum, they will begin to hang around. Once they begin to hang around, they begin to snoop around. Once they begin to snoop around, the life of the site administrators can get very busy, and not in a good way.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
February 19, 2011 at 17:14:56
Another reason the original code didn't work was because of the merged cells in Row 20. Macros are very, very, specific in what they do. When you select a merged cell you are actually selecting more than one cell at a time and the code didn't know what to do with that.

Try this version, it worked for me in your workbook.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Check to if the Selected cell is in a designated column
   Select Case Target.Column
    Case 5, 7, 9, 11, 13
'Check to see if the Selected cell is in a designated row
'Row 20 must be dealt with separately because it contains merged cells
'Clear Columns E:M then place number from Row 15
      Select Case Target.Row
       Case 20
         If Target.Cells.Count <> 2 Then Exit Sub
          Range("E" & Target.Row & ":M" & Target.Row + 1).ClearContents
          Target = Cells(15, Target.Column)
       Case 30, 39, 48, 57, 66, 75, 84, 93, 102
         If Target.Cells.Count > 1 Then Exit Sub
          Range("E" & Target.Row & ":M" & Target.Row).ClearContents
          Target = Cells(15, Target.Column)
      End Select
   End Select
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question