Set up UserForm with 54 columns

[meridius10]

I have an existing spreadsheet with 54 columns and 6200 rows and would like to setup a userform based on this data.

Am not exactly sure how to so this with the UserForm option in Excel VBA.

Some of the columns have normal text/numerical data, but others are list boxes (with warning messages if an incorrect entry is added) and there are formulas in some cells as well, so am not sure how this will impact on a new userform.

I'd probably be looking for functionality which includes creating and finding new records.

 

[Norie]

You could perhaps use a userform for something like navigating through the records/data and perhaps editing individual records.

However I don't think it would be a good idea to use it to try and show all that data if that's what you wanted to do.

Also it would be pretty hard to replicate the data validation and formulas but you could allow the user to enter data via the userform.

Would either of those ideas be of interest?

PS I'm not saying that it would be impossible to show all the data, do all the validation etc but it'd be quite a lot of work.

 

[meridius10]

Thanks for the honest answer, especially letting me know that it's quite a lot of work to set this up...

I've tweaked the spreadsheet in the best possible way, although I feel sorry for all those volunteers who will be entering data in all these columns, so was trying to make things easier for them.

The GUI in Access is better, but I would rather keep things as they are as the spreadsheet has data validation, locked cells, vlookups and message boxes appearing if anyone does the wrong thing.

I was thinking of linking the data to VB.NET 2008, but it would take me time to learn this process and what's more important here is to have (as much as is possible) an error-free set up that doesn't break down...

 

[Norie]

You could still use a userform for some of the data entry if not all of it.

It would be possible to do the validation - I might have been overthinking things, perhaps if you gave some simple examples?

When I think more about it some of the validation might actually be easier to do on a userform.

I think the main thing that threw me was the amount of columns.

Displaying all those columns on a userform in a columnar format would definitely be impractical.

If you can post more details...

 

[meridius10]

I definitely can't use the Data > Form option as there are two many columns.

There are 54 columns of which:

30 are data validation lists

5 are vlookups & 1 ID (locked cells)

Rest - normal cells

The Data Validation is simply a choice of variables (which feed off another worksheet). I have additionally placed two vlookup tables in different worksheets. As far as the user is concerned, they only need to see one worksheet, which is the main table.

In Access, there are field list options and combo boxes, text boxes etc. are used making the whole design process much easier (very little VBA). Is there a similar way to do this in Excel?