Mouse click to increase/decrease a cell value?

[Guest]

I need to incrementally increase or decrease cell values until I get an exact
result in a dependant cell:- 19.9, 19.8, 19.7...19.5...bingo!
Is there a way that I can achieve this with mouse clicks...each click
subtracts/adds 0.1 - or any other integer that I specify - to the cell value?

 

[Guest]

With a double click,


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = "$A$1" Then 'Change to suit
If IsNumeric(Target) Then
On Error Resume Next
Target = Target - 0.1
ActiveCell.Offset(0, 1).Select
On Error GoTo 0
End If
End If
End Sub

Mike

Mike

 

[Guest]

Al,

I missed you wanted to add or subtract so try this. A plus sign in B1 makes
it add. B1 blank makes ot subtract.

Mike

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
On Error Resume Next
If ActiveCell.Offset(0, 1).Value = "+" Then
Target = Target + 0.1
Else
Target = Target - 0.1
End If
ActiveCell.Offset(0, 1).Select
On Error GoTo 0
End If
End If
End Sub

 

[Guest]

Wow, instant response...thanks v much. Afraid you are talking a language I
do not understand, so I'm going to take your routine and ask someone in my
office if they can "translate" for me!

Again, thanks for your help!

 

[Guest]

Al,

To use it right click the sheet tab you want to use it on and then click
view code and paste the code in there.

As to how it works it uses an Excel event (before double click) and if the
cell double clicked is A1 it checks what is in the cell 1 cell to the right
- offset(0,1) - if it finds a + sign it adds .1 to the target cell and if it
doesn't it subtracts .1

Mike

Mike

 

[Tushar Mehta]

I need to incrementally increase or decrease cell values until I get an exact
result in a dependant cell:- 19.9, 19.8, 19.7...19.5...bingo!
Is there a way that I can achieve this with mouse clicks...each click
subtracts/adds 0.1 - or any other integer that I specify - to the cell value?

To nitpick, 0.1 is not an integer.

Here are a bunch of options that do not require any VBA code.

If you want a cell to be an exact number, why not just enter that number
into the cell?

If you are changing one cell so as to achieve a desired result in
another cell, check out Excel's Goal Seek capability. See Tools | Goal
Seek... Or, check out it's "big brother" Solver (Tools | Solver...)

Finally, if you still want to click and change a cell value, check out
the Spinner button from the Forms toolbar. If the toolbar is not
visible use View | Toolbars > Forms. Now, click and add the Spinner
control to the worksheet. Right click the control and from the Control
tab set the appropriate values including the Cell Link field.

To achieve increments less than whole numbers (say 0.1), link the
control to an intermediate cell and in the cell where you want the
actual value, enter ={intermediate cell}/10

 

[Guest]

Just testing re the integer...but your pointing me towards the Goal Seek
capability is perfect. Thanks!

 

[Tushar Mehta]

Just testing re the integer...but your pointing me towards the Goal Seek
capability is perfect. Thanks!

{snip}

You are welcome.