Input Box

h0l1yw0od

New Member
Joined
Oct 6, 2005
Messages
21
Hi everyone,

Could someone help me with the code for an input box please.

I've got it to display the box but i need to put in the code that will send the input data to a cell on another page and add it to the figure already there.

The code i have so far is:

---
Dim NumberOfItems As String
NumberOfItems = InputBox("How many items were in the batch?", "Enter Amount.")
---

The sheet the figure needs to go to is Staff, cell A57.

Thanks,
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,430
Office Version
  1. 365
Platform
  1. Windows
Try this:
Code:
    Dim NumberOfItems As Double
    NumberOfItems = InputBox("How many items were in the batch?", "Enter Amount.")
    Sheets("Staff").Range("A57") = Sheets("Staff").Range("A57") + NumberOfItems
Note: I changed your field to a numeric entry.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Sub getData()
'Sheet module code, like: Sheet1
Dim NumOfItems&

NumOfItems = InputBox("How many items were in the batch?", "Enter Amount.")
Sheets("Staff").Range("A57").Value = Sheets("Staff").Range("A57").Value + NumOfItems

End Sub
 

h0l1yw0od

New Member
Joined
Oct 6, 2005
Messages
21
Great, stuff.

Thanks guys.

Edit:

Ah, hang on a mo. I forgot that there are a range of sheets to choose from.

Basically when the macro is run it has to look at cell C4 to see what name is in the box and then apply the amount from the input box to the appropriate sheet.

Sorry. :rolleyes:
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

Try making the change, post your code if you get stuck.

To help: Record a macro for the basic steps and then modify the recorded code to work with general selections rather than the recorded specific selections.
 

h0l1yw0od

New Member
Joined
Oct 6, 2005
Messages
21
I've got the code i was after as far as moving the data from one sheet to another based on using the employee name in C4 and sending the info from the input box to the appropriate worksheet. The code being:

Worksheets(Range("C4").Value).Range("A57").Value = NumberOfItems

The one thing I'm now having trouble figuring out is how to make the number from the input box add itself to the contents of A57 as opposed to overwriting whats already there.

I know this sounds daft but i've tried several combinations but just cannot find the correct phrase to use or the place where to put it. I assume it should be something like:

Worksheets(Range("C4").Value).Range("A57").Value = Range("A57") + NumberOfItems

If someone can help with how to make that work then that would be great.

Thanks again.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,430
Office Version
  1. 365
Platform
  1. Windows
Did you try:

Worksheets(Range("C4").Value).Range("A57").Value = Worksheets(Range("C4").Value).Range("A57").Value + NumberOfItems
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,665
Members
412,481
Latest member
nhantam
Top