Setting up input box

What I want to be able to do is set up a thing that when you're in the excel sheet you click on it. Up comes a box where you can enter data and when you click submit. It puts that data back into excel spreadsheet. I have attached a spreadsheet to show what I mean. I have deleted some stuff that are unnecessary so don't worry about them.

What I need is for you to click a button that will create a new row/copy into the next row, from A9-AH9. Then the button will automatically bring up a box that will ask for Time, Timber, Clay, Iron, Warehouse, Hiding Place and then will enter that data into there respective cells which would be D20, F20, I20, L20, O20, Q20 respectively. That is the main idea. maybe it would be better to have te button come up with a box asking for the information and then will create the row and insert the data. If it's easier you can just do the box with just time or something so it's easier. I can then do the rest.

Tyvm. This to me seems really complicated so not sure how you would do this at all. i've looked at other stuff on the internet but haven't gotten anywhere.

P.S. If you have a more generic thing similar to this then show me the code for that or upload it and I can change it too suite my needs.

ty again

You can use a Button to fire the event, the event would as you say invoke various InputBoxes - if you use Application.InputBox (see: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx) you can further restrict the type of info that can be entered into the dialog so as to lessen the amount of validation you need to do with the resulting value... thereafter it's case of pasting the results to the next available row...

A very basic example as proof of concept:

Please Login or Register  to view this content.

How you invoke the routine will in part depend on the button control you opt to use... if you use a Forms button you would assign the Sub Routine to the Button (via Right Click -> Assign Macro), if you opt for an ActiveX button you could either a) change the routine to become the actual Click event for the ActiveX button itself (the code would reside in the Sheet object on which the Button exists) or b) use the Click event to Call this Routine - option b) would mean you could easily call the Routine in different ways - either manually (F8 or via VBE) or via the Button click event. Is that what you meant ?

I got the impression from the penultimate line in your original post that you were happy to nurdle the code to do what you wanted once you had a rough idea as to how to go about it... I always think that's the best approach to adopt as it generally aids the learning process (or at least that's what I find myself).

Let me/us know if there are other issues that need to be resolved.

See attached

Note in XL2007 if you have VBA in you would save as .xlsm rather than .xlsx

Re: your first question...

What you did was correct but you need to redefine the Variant Array vResponses from 1 to 2 to 1 to 3 ... ie tell the routine that the vResponses array is to contain 3 values.

The reason I opted for an Array (not normal) was simply so that you could validate all Responses in one go such that if any were "Cancelled" (thereby returning False) the routine would terminate ... else you'd need to iterate each response one by one (or set up such that hitting Cancel was not viable but then you have no exit route)

Re: your second question...

don't follow... paste the info "into" formulas ?

I'm heading offline but I suspect the below is along the lines of what you're after ?

Please Login or Register  to view this content.

1. Adding Buttons - I used Active X button from the Control Toolbox Toolbar
2. see revised example below
3. lock the template cells (row 9), protect the sheet as normal - then use VBA to unprotect / protect the sheet (change strPwd to match your password, also password protect your VBA Project in the VB Editor)

Please Login or Register  to view this content.

Nevermind. I figured out how to deal with that. I have one more question though. Is there a way to block a user from seeing a formula? In a cell that can be seen

When you Format cells there are two options on Protection tab namely:

Locked
Hidden

if you check "hidden" then when the sheet is protected the Formulae in the cells you selected to hide will be hidden from the user.

see section "how to hide formulas": http://www.online-tech-tips.com/ms-o...ulas-in-excel/

Please keep a thread to a specific question - if you have other questions (ie TextToColumns/MacroRecorder) create additional thread(s) - if relevant post a link to this thread.

Re: two buttons, without knowing what the 2nd is doing I would say it's likely you need two entirely separate routines, the names of which must be exclusive... ie

CaptureInputA / CaptureInputB

(remember to change the Click event to invoke the renamed Routine - ie instead of Call CaptureInput it should become Call CaptureInputA)