Use excel to store and retrieve data to set Userform controls

  • Hi, I'm working with VBA6 that should eventually run on office applications 2007 and 2013.

    Some background:
    I have written and dedigend for each (Word, Excel and PowerPoint) a VBA Userform. Each Userform is different (suited for the needs in each application). Each Userform also has a bunch of controls on it to set stuff before running.

    What I want is to have a preset option where the user can save the current settings of all of the Userform controls so that it is readily available to load their settings again instead of setting stuff every time. The settings may vary, so hard-coding presets isn't really effective.

    I'm asking your opinion for how it can be achieved, in theory (before I start getting my hands dirty with this) . I was thinking about using a standard excel file* (3 files in total. One for each Userform) as a place to store and load a preset. Does this sound like a good way to do that? I don't wanna overcomplicate things, just looking for an easy or at least the easiest way to make it happen.
    [SIZE=12px]*I'm already fairly familiar with how to 'talk' to excel sheets/cells and modify Userform controls accordingly depending on cells' values and such.[/SIZE]

    I'm interested to know what you guys think. Feasible (for an amateur-level-VBA-coder as myself..)? Too complicated (any easier/better way to approach this -- levelwise) (and why is it bad.. If you think it's a really bad way to go)?

    Thank you so much for any input!

  • Apps I write are typically connected to a database, so I use a table in the database for this. I suppose you could have an Excel workbook act as the database, and that workbook need not be open.

  • Hi thanks, yes I basically need a database I can work with. Excel is what I know, and is familiar to me to interact with almost right away. I don't have any experience with real database interaction (read/write, save, delete..), bur excel has very similar features that a basic database would hold.. Right?

  • It's not a database system, but should be sufficient for this. You can use a lookup range even when the worksheet is closed. Personally, I would connect to the relevant sheet using ADO (which is probably an advanced topic).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!