lists boxes and more

[rcbricker]

hi all got a whole new question for a new spreadsheet i am developing. No example yet so no attachement.

Problem 1:

Simply put I want to make a list box. So i need code for how to set that up. Have about 70 account entries i want entered into this box.

Problem 2:

I have a set of check boxes that have 4 books of buisness listed one per box.
Lets call them Book A, Book B, etc.

Each of the account names (70 off them) are grouped based on the book of business. There are times when the same account appears in two or more books of business. So what I want to happen is as I am typing the name of "Account Name A" into the list box it auto fills before i finish typing hopefully. once i have selected the "Account name A" the list box greys out all the books of business except the ones associated with "Account Name A".

Basically i want autopopulation but since there are more than one book of business that can apply i figured the check boxes would be better.

anyone got and suggestions

[iwrk4dedpr]

rbricker,

Where is the data that will populate your listbox? Is it on a sheet or does it need to be figured programatically? If the data for the list box is on a worksheet have you thought of using the data validation??? Will the user need to select more than one item in the list box? If not then a combo box would be better suited. Will you be using the Control Toolbox or Forms control????


As far as the problem 2 you lost me.


If the user selects an account from the combo/listbox what data is contained or displayed in the checkboxes. Or does the check box determine the choices available in the combo/listbox?


Regards,
Barry:cool

[rcbricker]

ok i threw together the basic form. It is attached. The paper form has been duplicate on the first sheet and the changes I want to make to help facilitate the excel version are shown on the second sheet.


iwrk4dedpr,

the names are not on the sheet anywhere so they would need to be programed. This is also the best way as the sheet will not be mandatory but any who want to use it I will add their accounts to the list and their name to the requestor list (as you can see by the attachement i added the requestor line as a auto fill while typing request).

Any more questions please let me know.

[Zack Barresse]

Hey,

What values do you want on the ListBox? And it almost sounds like you are going to want a UserForm for this, is that what you're thinking?

The one thing I'm confused on is the flow of this workbook. From a User's standpoint, can you walk us through a typical entry?

[rcbricker]

A user form might work which ever is easier doesn't matter i will have to look into it.

The info in the list box will be the account names (some are pretty long so trying to save myself and others some typing).

Walk through:

Ok requestor puts in their name

Then the date

They then Type in the account name

And then select the book of business

Select the instructions choice they need

and then the file type

then type any notes they need the file team to have.

[Zack Barresse]

And this form is going to be printed out then? Or email? Are you planning on putting the Requestor names and/or Account names as a drop down box then?

I'm not sure ListBox's are the way for you to go here.

[rcbricker]

Drop downs would be fine and yes both the requestor's and the accounts will both need to be done. as well as the other changes if possible shown on sheet two of workbook.


Thanks Fire appreciate the help

[Zack Barresse]

Well, maybe you can check out the following attachment. Just added a few data validation cells, some forms checkbox's and some forms option buttons in a group box. Is that along the lines of what you're thinking?

And if you want checkbox's for you Books instead of a drop down menu, we can do that also.

[rcbricker]

hehe thanks fire that worked so well only a couple small changes need to be added. Thanks for the help.

[Zack Barresse]

Sure.

As much as I love VBA, if you can do it with native Excel functions, that is almost always the better route to take. :yes

If you need anything else with this, just post back details. Glad I could be of help. :) (And if this does solve it, you can mark it as such from the Thread Tools menu at the top of the thread.)

[rcbricker]

Actually I was just testing it and was wondering is there a way to get the drop downs to auto fill as you type?

There are some 500+ accounts and it is faster having it auto fill.

[Zack Barresse]

Not that I know of. The native Autofill should work after you've typed it (or selected it) at least once prior in the same session.

[rcbricker]

ok thanks