Solved Flexible input value in Excel sheet (using the slider)

Acer Aspire timelinex as1830t-68u118 not...
March 5, 2020 at 23:45:14
Specs: Windows 10, 8GB
Hi everyone,
In an Excel file that I'm using for some calculations I sometimes need to change the values around a certain central value in order to see what would be if the value would be a little higher or a little lower.
I'm wondering if it could be possibe to place in the cell where I usually input the values an additional optional slider with two options: "+" and "-" so that after I input a "central" value I could observe how the output varies if I increase/decrease a little the input by pressing "+" or "-" button aside.
Could anybody tell me how to make this trick in Excel 2016 (if possible at all)?
:-)
Thanks a lot in advance!

Zenon


See More: Flexible input value in Excel sheet (using the slider)

Reply ↓  Report •

#1
March 6, 2020 at 03:49:51
Would something like this work for you?

https://m.youtube.com/watch?v=yx2Bo...

Update:

I just tried this in Excel 2016 and it worked fine for values 0+. Negative values, not so much.

I did some searching and it's a known issue. If you want to use negative values, you have to use a formula in another cell such that the formula based cell shows the values that you are using in your final calculations.

This, of course, comes with the issue of not being able to manually enter values in the cell with the formula without losing the formula. If that is not an issue for you, then this method should work. If it is, then perhaps some VBA would suit you better.

See my following posts for a couple of VBA options.

message edited by DerbyDad03


Reply ↓  Report •

#2
March 6, 2020 at 07:10:02
I threw together some VBA code just as an example.

Set Up:

1 - Put a Plus sign (+) in one cell and a Negative sign (-) in another.
2 - Right click on the sheet tab for the sheet in which you want this to work.
3 - Paste the following code into the pane that opens.
4 - Save the file with a .xlsm or .xlsb extension.

Note: In this example, the code refers to cell A5. Change all 4 occurrences of A5 to fit your needs.

In the worksheet, double-clicking the Plus or Minus cells will run the code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Increment cell if "+"
    If Target = "+" Then
      Range("A5") = Range("A5") + 1
      Cancel = True 'Cancel edit mode, stay in cell
    End If
'Decrement cell if "-"
    If Target = "-" Then
       Range("A5") = Range("A5") - 1
       Cancel = True 'Cancel edit mode, stay in cell
    End If
End Sub

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


Reply ↓  Report •

#3
March 6, 2020 at 08:17:51
✔ Best Answer
Here's another option...

With this code, you can right-click instead of double-clicking. As long as the cell doesn't contain + or - you'll get the standard right-click context menu, otherwise A5 will increment/decrement.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'Increment cell if "+"
    If Target = "+" Then
      Range("A5") = Range("A5") + 1
      Cancel = True 'Suppress Context Menu
    End If
'Decrement cell if "-"
    If Target = "-" Then
       Range("A5") = Range("A5") - 1
       Cancel = True 'Suppress Context Menu
    End If
End Sub

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

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
March 9, 2020 at 01:57:26
Hi DerbyDad03,
That's it - THANKS A LOT !!!
:-)

Best Regards
Zenon


Reply ↓  Report •

#5
March 9, 2020 at 03:39:04
re: "That's it"

What's "it"? I offered 3 different options.


message edited by DerbyDad03


Reply ↓  Report •

Ask Question