Cell onclick make cell value increase.

  • Thread starter Thread starter Cali92
  • Start date Start date
C

Cali92

Hi everyone.
I need this, but still can't figure out how to do this...

I have a spreadsheet with A and B in which I insert people name and a
number, ie Carlo Gonella (A) 4 (B).
then on the same row I have a lot of cell with an initial value of 0.
(there are 20 cells)
My need is that when I left click on the cell, its value increase by
one...

So that I will have something like

A | B | C | D | E | F | G | H | I |
Carlo Gonella | 4 | 2 | 0 | 1 | 3 | 0 | 3 | 4 | and so on ....

But I really can't figure out where and what to code ...

I was thinking about a userform in which putting many buttons as the
names in the A column, and a number of txtboxes as the columns I need
to increase.
Selecting a name, the values will be the one referring to that person,
changing name they will change.


Thanks in advance for your help...

I Attach the baseline for what I need ...

Cali92

File Attached: http://www.exceltip.com/forum/attachment.php?postid=281708 (scout1.xls)
 
I think I am understanding what you want. In your worksheet_SelectionChange
envent add this code:
If Not IsNumeric(ActiveCell.Value) Then
Exit Sub
ElseIf IsEmpty(ActiveCell) Then
Exit Sub
Else
ActiveCell.Value = ActiveCell.Value + 1
End If
The code will check the activecell, if it is text it will do nothing, if the
cell is blank, it will do nothing, else it will increase by one.

HTH
 
I agree with Cliff, but think you should use Target rather than activecell.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If target.count > 1 then exit sub

if isempty(Range("Z1").Value) then
If Target.column >=2 and Target.column <=21 then
If Not IsNumeric(Target.Value) Then
Exit Sub
ElseIf IsEmpty(Target.Value) Then
Target.Value = 0
Else
Target.Value = Target.Value + 1
End If
End If
End If
End Sub

This event fires whenever the cell is selected (either with the mouse or
manually moving into it, so I think you need a way to turn it off. I check
if Z1 is empty. If you want to turn it off, put an entry in Z1.

You would right click on the sheet tab and select view code. Then put in
code like the above.

--
Regards,
Tom Ogilvy


Cliff Myers said:
I think I am understanding what you want. In your worksheet_SelectionChange
envent add this code:
If Not IsNumeric(ActiveCell.Value) Then
Exit Sub
ElseIf IsEmpty(ActiveCell) Then
Exit Sub
Else
ActiveCell.Value = ActiveCell.Value + 1
End If
The code will check the activecell, if it is text it will do nothing, if the
cell is blank, it will do nothing, else it will increase by one.

HTH
Cali92 said:
Hi everyone.
I need this, but still can't figure out how to do this...

I have a spreadsheet with A and B in which I insert people name and a
number, ie Carlo Gonella (A) 4 (B).
then on the same row I have a lot of cell with an initial value of 0.
(there are 20 cells)
My need is that when I left click on the cell, its value increase by
one...

So that I will have something like

A | B | C | D | E | F | G | H | I |
Carlo Gonella | 4 | 2 | 0 | 1 | 3 | 0 | 3 | 4 | and so on ....

But I really can't figure out where and what to code ...

I was thinking about a userform in which putting many buttons as the
names in the A column, and a number of txtboxes as the columns I need
to increase.
Selecting a name, the values will be the one referring to that person,
changing name they will change.


Thanks in advance for your help...

I Attach the baseline for what I need ...

Cali92

File Attached:
Click to expand...
http://www.exceltip.com/forum/attachment.php?postid=281708
Click to expand...
 
Thanks a lot for your help.
Now I have found a new problem.
I cannot click 2 times on a cell, if I do this, the cell becomes
editable, so I cannot make it increase ( wooo ... xcuse me for my
English but I really find hard to explain this things ...)

I am asking myself this---

Is it possible with some code, to control, from a UserForm, the value
of the cells ?
I mean, if you took a look at the file I attached, I'd like to
replicate the same thing on a UserForm, but instead of cells I use
buttons.

In this way I have a UserForm from which, for example, the button A1
controls the cell A1 and so on ... even if I have to copy
many times ( there are 36 buttons for each player, and 12 players !!!
), I think that's what I need ...

I tried with the code I found on a book, that is :


Private Sub CmdCount_Click()
cmdCount.Caption = CStr(ValueA + 1)
Range("D57") = ValueA1
End Sub

Private Function ValueA() As Integer

With cmdCount
ValueA = Val(Right(.Caption, Len(.Caption) - InStr(15, .Caption,
" ")))
End With
End Function


But it is right only if I put the buttons on the spreadsheet, while I
need a form in which replicating, even graphically (but I can do that
... at least... :eek: ), the spreadsheet, and controlling cell values
with the buttons...

Thanks again ...

Carlo
 
Put textboxes covering each of the cells. If from the Control
toolbar, use their onclick procedure to increase your cell number.
If from the Forms toolbar, attach a macro to do the same. Set them
to move and size with cells.
 
I didn't understand your answer, sorry.
I am new to Excel programming...

You tell me to cover each of the cell with a textbox, which will
increase the with the mouse click.

How can I do it ?

And, due to the statistic possibilities Excel gives me, can't I create
a UserForm from which I'll control the cell value increasing =

In this way I should use cell values to create realtime charts
giving infos on players and team scoring.

The spreadsheet goal is an easy scouting of the volleyball team I
train... so that a friend can take datas on players and give me hints
during playing.

I think a form is the best way .... but how ?

And, to try the solution you gave me , how can I do that ?? Can you
explain it in an ... easier way ?

Thanks a lot.

Carlo
 
One way:

Create a text box from the Drawing toolbar (not the Forms toolbar as
I wrote incorrectly earlier).

Size it to cover the cell, Say A1, exactly.

Format it (right-click and choose format textbox) with no fill and
no border so that the underlying cell contents show through. Also
make sure it is set to move and size with cells.

Right-click it and choose Assign macro and enter this macro:

Public Sub Textbox1_Click()
With Range("A1")
.Value = .Value + 1
End With
End Sub

Now, whenever you "click on A1" you're really clicking on the
textbox the macro will be called, and the value in A1 will be
incremented by 1
 
Back
Top