PDA

View Full Version : Userform/interface question



BENatUSGS
10-05-2010, 11:02 AM
Hello All,
I am attempting to create a new user form for a data entry project at my job.I would post a picture but it seems to not like the way I do it :dunno .The userform itself has a number of different ways to input data. It has some combo boxes, list boxes, input boxes, ect.The reason I chose to it this way, is so that the user can see all their entered data compiled before the macro takes over and puts it into a spread sheet. What I would like to do, is create some form of a listbox, msgbox, ect that will post what the user inputs. I would like for the msgbox, listbox ect… to show what data has been entered as soon as they enter it somewhere on the same user form. If it is unclear what I am asking just say so and I will re word and re post.
Thanks guys,
Ben

RonMcK
10-05-2010, 11:29 AM
Ben,

How about using Data > Form (alt-D-O)? This will show the cells you want entered in a row on a dialog form that the user can tab (or click) between fields to enter all the data. It's not committed to the spreadsheet until they hit <enter> or click <next>, as I recall.

Thanks,

RonMcK
10-06-2010, 12:09 PM
Ben,

How many columns (or cells) of data does your user need to enter?

Thanks,

BENatUSGS
10-07-2010, 08:33 AM
They will only be entering a max of 6 inputs. The spread sheet itslef will have a number of default inputs already in the sheet.

Thanks
Ben

RonMcK
10-07-2010, 08:37 AM
Ben,

Are their inputs (cells) adjacent to each other, in a group, at the left hand edge of the worksheet? Or, can they be put there? Or, by the nature of the worksheet, they can not be contiguous?

Thanks,

BENatUSGS
10-07-2010, 08:51 AM
Ron,
If you look at my attached xls file you can see the layout of the spreadsheet. Maybe it will give you a better idea of what I am trying to do.Let me know if this helps.

Thanks,
Ben

RonMcK
10-07-2010, 09:19 AM
Ben,

Try this. Click on Cell A1. Press Alt-D-O (or in 2003/4 and earlier use Data > Form... This isn't automatically on the 2007/10 menu but search Help for Data Form and follow the instructions for adding it to your Quick Access Menu if one of those is the version Excel that you're using.

This will open a input dialog, click New button to fill in a new record (row). Warning tab between fields as you enter or edit data in boxes on the form; pressing <enter> accepts the data (commits it to the worksheet) and sets you up for entering the next new record.

You can walk through your existing records using Find Next/Find Previous or by moving the slider.

If you need to enter the 4 hyphens for blank fields, enter '---- in an empty cell and press ctl (or cmd on Mac)-C (or alt-edit-copy) to place the "value" in your clipboard. Then, fill-in applicable blank text boxes by pasting.

When you start a new worksheet from scratch where you want to use this technique, fill-in your headings on row 1 and format the row 2 cells as needed (date/time, numeric values, etc). The dialog will copy the formats to each new row. Similarly, formulas entered in cells on the first row will be replicated to each new row. And the user will be unable to touch that cell (field) so the can't mung that up for you.



I hope this helps! Have fun!

Thanks,

BENatUSGS
10-07-2010, 11:03 AM
Thanks Ron, I'll give that a shot.

Ben

BENatUSGS
10-12-2010, 08:43 AM
Ron,
I have another question. Is it possible to pull variable names from previous private subs?

Like,

Private sub ABC ()
Dim ABC As Integer
ABC = 123
End sub

Private Sub CheckABC ()
MsgBox ABC
End sub

Let me know,
Ben