Excel - using Userform to collect variable amount of data

[EdwardMartinIII]

I have a project I'm working on.

At the moment, I have to use Excel, so I recognize that I might have to use a Stupid Solution. I'll say that up front.

I have a big page full of controls. One of them is "How many widgets?"

This is the requested action when that control is clicked (gotfocus):

1. Open a Userform which queries for "How many widgets (1-50):"
2. Using that number, then activates another set of controls on the same form that allows you to page through all x widgets. For each widget, we want you to specify certain things about it. Some is wild text, others are from dropdown.
3. All of this information must be saved somewhere in the speadsheet such that it can be programmatically retrieved by other parts of the spreadsheet/the internal VBA app, for other calculations.

I'll say up front it's not actually physical widgets, so that's just an example.

Example: I pick 5 widgets. I'm then faced with an interface that for each of the 5 widgets, I'm asked:
1. How much does it cost?" free text/integer
2. What department does it belong in? (dropdown1)
3. Which manager is in charge of its inventory (dropdown2)

I already have a secondary sheet with lookup tables, so the various dropdowns can be populated from those.

A requested action is "I want the program to be able to determine if, for example, ANY of the widgets are in Department x or if any of the widgets are overseen by Supervisor y and so forth."

Now me, I'm thinking "Could you come up with a BETTER use for a database?!" but the whole thing is already a big Excel spreadsheet and I am being STRONGLY encouraged to keep it there. If I want to move it to a database with a pretty face, I'm going to have to be REALLY persuasive, because that'll take the project down for a while during the conversion.

So, is this a thing that is reasonable to ask Excel to do?


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

One of my dum-dum solutions is to set up another sheet in the workbook that is 50 widgets and their associated values.

When I open the widget data collection userform, it builds a big-ass array from all fifty widgets (initial values are blanked out).

Then it lets me page through each one, making whatever changes I want, and when I click [save] it spools through the whole array, transferring the revised values (if any) back to the spreadsheet.

The thing that would make this work, I think, is to have a VBA command that lets me build commands on the fly, such as EVAL.

So (in pseudo-code), I could do this:

Create big 2D array
For WidgetCount = 1 to 50
populate array row[WidgetCount] from sheet3.Widget[WidgetCount].text
end WidgetCount

(then reverse if they push [accept])

Is that do-able in VBA?

Or is there a way in VBA to build an array directly from a 2D range in Excel? That would be awesome.



Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

The actual display of the controls will pretty much simulate a simple DB input control, with arrows letting you page across data in the array.



Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

I am thinking of using an array instead of hotwiring directly to the sheet because if they click [Cancel], I want nothing on the sheet to change.


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

Oooooh, this looks promising:

http://scriptorium.serve-it.nl/view.php?sid=46

Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door