Adding to existing data

Posted by Jake Clarkson on June 01, 2000 12:36 PM

There is a spreadsheet that several of my co-workers update during the day. Is there a way to add data to a cell by way of a pop-up box. For example, if I have input "4.72" in a cell and my co-worker needs to add "3.57" to that same cell, the only way we know to do that is to hit F2, HOME, =, END, +, enter the data, ENTER. Very tedious!
Thanks,
Jake

Posted by Ryan on June 11, 0100 11:19 AM

Try this Jake, if you need a little customizing, email me.

Open the VBE
Add this to your ThisWorkBook Module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteItemFromShortcut
End Sub

Private Sub Workbook_Open()
Call AddItemToShortcut
End Sub

Add A New Module to The Workbook and put this code in there
Where it says "Your Caption here", Change it to say what you want it to say

Sub AddItemToShortcut()
Set NewItem = CommandBars("Cell").Controls.Add(, , , 1)
With NewItem
.Caption = "Your Caption Here"
.OnAction = "AddtoCell"
End With
CommandBars("Cell").Controls("Cut").BeginGroup = True
End Sub
Sub DeleteItemFromShortcut()
On Error Resume Next
CommandBars("Cell").Controls("Your Caption Here").Delete

End Sub
Sub AddtoCell()
Dim addamount As Variant

addamount = InputBox("What do you want to add?")
ActiveCell = ActiveCell + addamount

End Sub

Posted by Ryan on June 11, 0100 11:28 AM

err handling for what i gave you

Jake,
This will resolve a problem of you trying to enter in non-numerical values into the InputBox.

Sub AddtoCell()
On Error GoTo ErrHandler
Dim addamount As Variant

addamount = InputBox("What do you want to add?")
ActiveCell = ActiveCell + addamount
Exit Sub

ErrHandler:
MsgBox "Invaild entry"
Call AddtoCell

End Sub