OK. I'm trying to do a database in excel. In one of the worksheets, I
have several dependent controls (or so I want them to be) the tricky
part is, I want to be able to have a "control panel" or something of
the sort where I can edit the choices in the mentioned controls. Is
there a way to do this? and then how do I like them together?

OK, an example:

I have a workbook which contains three different
worksheets(DueToDueFrom, Cash and Inventory). Each one of these
worksheets contains three different controls. These controls are to be
dependent one one another (within the worksheet, not other controls in
other worksheets). They are arranged as follows - "Project name,"
"Account Number" and "Account description." "Account Description" is
to be populated by the chosen option in "Account Number," but these
choices in "Account Number" depend on what was picked in "Project
Name." And all these choices and values from each of the three
worksheets should be edited in this "Control Panel."

I'm really bad about pricing, but I'm looking for specific things,
like if it can be done in Visual Basic, I need the code. So the better
the answer, the more the tip.

---

Clarification of Question by boclegant-ga on 31 May 2005 08:44 PDT

thanks manuka-ga for the clarification.

regarding your questions:
-the controls need to be visible all the time in each of the
spreadsheets, but the controls on the three worksheets are not
related, because I have "Due to, due from," "cash" and "inventory",
and all three have different project names, Account Numbers and
Account descriptions. (However, as I explained, they are connected if
they are in the same spreadheet)

-Since all three workheets are totally unrelated, it will be better
for the controls to stand alone per worksheet, thus even though all
three of these sheets have the same control names, they are not
related, unless they are in the same worksheet.

this is a breakdown of what is in my workbook.

worksheet: "DTDF"- Controls: Project Name, DR Code, Account description
Worksheet: "Cash"- Controls: Project Name, Account Number, Account description
Worksheet: "Inventory" - Controls: Project Name, Account Number,
Account description

each of these have different project Names, Account Numbers (or DR
Code), and Account descriptions.

so what I want is a "control panel" to manage these values for each of
the controls in each of the worksheets.

They were created using Forms toolbox.

Hi boclegant,

You can assign code to any of your controls. So you can add code to
your "Project Name" control so that when the value is changed, the
other two controls are cleared and the appropriate set of values is
loaded.

The precise method of doing this depends on how you've created your
controls.  It sounds as if you've put them in from the Forms toolbar,
in which case if you select the control (right-click it), the "Edit
Code" icon on the Forms toolbar will be enabled. If you click on this
VBA will open with a blank Sub (piece of code) to be run when the
value of the control changes.

If instead you've put them in from the Control Toolbox, make sure
you're in Design Mode, right-click the control and select "View Code"
from the pop-up menu.

If you don't need the controls to be displayed all the time, or if you
want to re-use the same set of controls on each worksheet, you may be
better off putting them into a VBA UserForm. This is basically a
customisable dialog box. Whether this is an advantage probably depends
mostly on what behaviour you want when the user switches sheets. If
you want settings retained separately for each sheet, given that you
only have a few sheets you're probably better off with separate
controls. If the user is likely to want to use the same project and
account information on multiple sheets, a UserForm will be better. You
can also hide the UserForm if you don't want it always displayed
(though the downside is that you have to make sure you show it when
you do want it visible). Controls in the same UserForm can also access
each other without being visible to the rest of the application.

On another note, depending on how many project / account combinations
you have, it will likely be best to have this information stored in a
separate part of the workbook (e.g. in a hidden sheet, so that it
doesn't get inthe user's way). That way we can load the account
information from the worksheet when the project changes, and when the
list of allowed combinations changes we only have to make appropriate
entries in the spreadsheet rather than editing the code.

If you can post a comment clarifying the following items, I can
respond in more detail about how exactly the code should go:

- Do the controls need to be visible all the time?
- Do you want the separate worksheets to maintain their own controls,
or will it be better for the same settigns to be carried over when the
worksheet changes?
- Are the same project / account combinations to be available for all
three worksheets?
- How were your controls created (Forms, Toolbox, VBA)?

Cheers, manuka-ga

Maybe I am oversimplifying, but this looks like a job for lookups. It
would be easy to make the following flow:
- user chooses an account description, perhaps from a drop-down list
- account number is looked up and put in a cell
- project name is looked up and put in a cell

This would be done with simple cell formulas (not controls), using the
VLOOKUP function.

I am probably oversimplifying, but I do always try to bring Excel
problems down to a simple solution (usually successfully).

Cheers,
-Dave

Hi Dave,

That makes the job easier for the computer, but it's not a good flow
from a human-factors perspective. It's much better for the user to be
able to select the project and then choose from a list of account
descriptions, rather than having to pick among several account
descriptions which might be virtually or even exactly identical and
hope they get the one associated with the correct project. (Of course
if different projects do sometimes use the same account description it
makes it significantly harder to do using lookups.)

Boclegant-ga, I've been thinking about this (even if I haven't posted
anything - sorry! been busy) and despite what you've said above I'm
not convinced that separate controls is the best way to go. It depends
whether it is more useful for you to have controls in a fixed cell on
the worksheet (in which case separate controls is fine) or floating
over whatever area of the worksheet you want to work in. In the latter
case I'd recommend using a single VBA modeless form, but making it
smart enough to update the available options whenever you change
worksheets. This will prevent having to make multiple code changes any
time it needs updating. I'm also thinking along the lines of
maintaining the available choices in a separate, normally-hidden
worksheet. Let me know your thoughts.

I will try to post some more detailed comments and hopefully a simple
prototype in the near future. Unfortunately I'm pretty much flat out
at the moment.

Cheers, manuka-ga