Question re Form option/UserForm

[007juk]

I have created a workbook that contains vlookup formulas. I also have another sheet (sheet2) that contains my data for the vlookup to work. The problem I have is that the sheet2 is quite big, it ranges from A1 to AL1 and will, once complete contain data covering A1:AL200.

I have 6 columns that contain the following heading menu 1 to menu 6 and each have ten rows (options). I have sheet2 set up as follows -

e.g. menu1 (B1) option1 (C1) option2 (D1) and so on till I reach ten (K1)

every time I go to enter data for a new row I have to tab along to nine spaces to enter data and so on. Is there any way I could enter data more quickly. Cell A1 contains a list of names and the cell has been data validated, this cell is also my 'lookup Value' and that name will always remain on that row its just then menu items I will be updating.

Thanks.

[mikerickson]

Freeze Panes or Split Window?

[007juk]

Thanks for advice. Was thinking more like a userform or something similar. Maybe a command button that sits on top of sheet2 and once selected generates a userform. The userform would have items that I could select that would then populate corresponding cell(s). Then next time I enter data for that person I would select their name add data to row 2 etc.

Many thanks

[mikerickson]

Have you looked at Form in the Data menu? (I don't know where they hid it in 2007). It generates a form like what you want.

Yes, a userform could be created and set-up the way you describe.

[007juk]

Yeah had a look at form, unfortunately too much data for it to work. Unfortunately don't know how to set up userform. Something I would like to learn though, wpould use that a lot I think.

Thanks.

[lucas]

So you basically want to set up a userform for entering data in the next available row on sheet 2....

Attached is a simple data entry userform that now works for 3 columns....you can adjust it in the code. If you want to learn about userforms and how to use it to enter data then this is a good example.

post back here if you have any questions.

[007juk]

Thanks for the advice/attachment Lucas.

At the moment my workbbok is set-up to enter data from left to right. The first column contains employee names and I have data validated this cell so that the correct name is always inserted and to enable the vlookup to work.

In your example you have provivded three boxes and the option to insert text. Is it possible to have a mixture e.g. text boxes and drop-down boxes or combo boxes. At the moment my data sheet (no2) has a mixture.

In order for me to populate the correct cells entering data from left to right seemed the only available option. Is it possible for the data to be populated per column rather than row.

I have a copy of the workbook if it helps explain any, many thanks again for your time.

[lucas]

Sure, It always helps to see how your spreadsheet is set up. I altered the sample by adding a combobox....names are on sheet 2.

[007juk]

Thanks for that, here is the workbook that I have been working on, many thanks.

FYI - sheet2 contains 'disciplinary' data, sheet4 contains 'attendance' data and sheet3 contains sorce data for data validation which I have defined names for.

[lucas]

every time I go to enter data for a new row I have to tab along to nine spaces to enter data and so on. Is there any way I could enter data more quickly.

Your original question from post #1....so does the question for this thread apply to sheet #2 in your example file? Do you want to develop a userform with a combo box to select the name, ect. then put in in the next row of sheet 2?

The bottom line is that there probably isn't a faster way to do it but with a userform it would look a little cleaner.

About sheet #1.....I hope you don't plan on printing this or you will have to change the layout...at least on my printer.

[007juk]

Yes, I was refering to sheet2 in relation to post #1. I never mentioned sheet #4 because it was set-up the same, but because I posted sample file I mentioned it.

I was trying to put dat in to next column rather than row.

As you cam see sheet2 (E2 to N2) populates sheet1 B10 to B19. Was wanting to know if a userform can be created for that and other sections, thanks

[lucas]

See if we are even on the same page....add a few names and data to sheet 2 using the userform...click the button on sheet 2.

I only did 2 of the many Ln1, etc......but you get the idea.

you can add names, etc. to the sheet 3 where the combo boxes get their data as the rowsourse for each is a dynamic named range.

[007juk]

Lucas,

Thanks for file. In relation to sheet1, once I select name via the combo box then that name will always remain in cell A2 of sheet2. Is it possible that one I press button I can select employee and the update the various lines on data. At the moment when I add another record for John it populates A3 and therefore the vlookup does not function.

Basically the only cells That I will be updating on sheet1 are B10:R19. Do you think I would be better changing the layout on sheet2 e.g. description of offence/date/action/officer rather than description of offence/Ln2/Ln3/Ln4 etc.

Is it possible to create a userform that contains a combobox titled name, so that I can select the correct row to update then the option to select the next available row to update and once selected can enter data.

Your option is great, it would build a good database, but unfortunately the vlookup won't work. Is it possible to create a sub menu and select per Ln#

Thanks.