I need a POP-UP calculated to edit balance field

[John19]

I want to be able to add or subtract my BALANCE figures, using a POP-UP calculator - that appears when I select an icon next to any balance cell.

I'm looking for something similar to the pop-up calendar by "XLTools" Excel Add On, but with a numeric keyboard to add or subtract to the existing. A small icon appears to the right of the cell, which you select if the pop calculator is needed.

If I need to, I want to be able to edit the balance with the computer keyboard as normal without any hindrance from this pop-up function. So If I don't select the function icon, the balance cell is as normal.

NOTE: on the pop-up keyboard, if I do not select ADD or MINUS first, the number entered should replace the existing. If ADD or MINUS is selected first then calculation (+ -) on the existing balance should be done. Selecting any other cell finalises the calculation and closes the pop-up.

PLEASE SEE ATTACHED EXCEL SHEET FOR MY BALANCE TABLE

Thank you

Also posted in:
http://www.mrexcel.com/forum/excel-q...nce-field.html
http://www.ozgrid.com/forum/showthre...431#post781431

[avk]

plz attach image & confirm.
If yes, > Go to "office Button" > Click "Excel Option" > Select "Customize" > select "all command" > select "Calculator" > click on "Add" button > click ok

[John19]

sorry don't understand your solution - I know how to launch the calculator? it wasn't what I described in my post.

thanks anyway for the suggestion

[cytop]

As well as the included link to MrExcel, also posted on OzGrid.

Please update all your threads to include all the links.

[DBY]

Hello
Not sure if you've had any success with replies elsewhere but I've had a stab at creating a userform, which may be something you can use, if I've understood you correctly.

For demonstration, I've placed a few icons on the sheet next to the balance column, these do need to be postioned accurately in the cells. When clicked, the adjacent balance cell is selected and the user form is opened.

There's a toggle button on the form that moves from "Replace"; "Add" and "Subtract". Depending on the selction that's how the selected cell is affected.

After entering the numbers in the text box click submit to place the new value into the selected cell. Click any cell within the borders of the table to close the form.

The form is not showing modal, so the worksheet can be accessed as normal.

Hope this helps.
DBY

[John19]

Very good! just as I was going up hope, exactly what I'm after thank you.

Any chance you could make changes to it? Let me play around with it to see.

again thank you very much

[DBY]

Hello John
Glad you found it useful. It passed away a few hours on a dull Sunday afternoon. As with all things VBA a bit of testing is required to see if it breaks. I have found a bug in that if the text box is empty and add or subtract is applied it produces an error.

Accordingly, I've amended the following sub to:

Please Login or Register  to view this content.

This stops the command if the text box is empty. Is that ok?
DBY

[John19]

Can you change the following:

1) make the edit box smaller - covers up too much of the screen - can't see data behind it
2) make the edit box appear on the right side of the Balance column - covers up my data on the left
3) make the "0" digit after the "9" - or make the number layout same as on MS calculator
4) make the icons only appear when a balance cell is selected - I have data on those cells
5) on the edit box, don't need replace option (can type directly into the cells to replace)
6) when the box opens up, by default it should be on ADD - and the toggle button is changed to "-/+" (you have it as "+/-")
7) make the edit box disappear if any cells outside the balance column is selected - at the moment it only disappears if any cell within the table is selected - should be anywhere in the sheet
8) there appears to be a delay when pressing numbers repeatedly? try typing 100

thanks

[DBY]

Hi
Wow! you set me some homework there! I've been a little busy today but I've made some amendments as you asked. Not sure if it's everything you want but it works as following:

1. The form is now smaller and more compact.
2. It now appears on the right side of the balance column.
3. The numerical layout is now similar to the MS calculator.
4. There's now only a single icon that appears when any "Balance" cell is selected (L40:L91).
5. "Replace" has been removed.
6. Default is now "Add".
7. Form and icon disappear when any cell is selected, apart form "Balance" range.
8. Changed command button event for numerical input to "MouseUp" rather than "Click", this seems smoother.

Test this out and see how you go.

DBY

[John19]

just seen it now, flabbergasted -very very good.

let me use it for a couple of days just to make sure, maybe one or two last fine tuning to it.

you're amazing thank you.