Data Validation + macro to hide sheets

Both problems concern Excel files that someone other than me has programed. As a relative beginner i try to study what other people have done and learn from that. There are however two things that I dont understand how they work. Please note that English is not my native language.

The first thing is a file with 2 sheets. One cell on the first sheet has a drop down menu which picks its "choices/names" from a column in the other sheet. For instance if i write AAA in row 1 in that column, BBB in row 2 and CCC in row 3 the meny shows "AAA", "BBB", "CCC" as well as a lot of "0" for all the rows that i havent written anything in.

My question is how to program Excel to "pick up" and "put into drop down meny" what someone writes in a certain column. As far as I can see there is not VBA code in this file.


The second thing concerns another file which contains alot of VBA programing. On the main sheet there is a box called "listbox8" which calculates and shows several things. When i rightclick on it anc choose "properties" there is a row called "ListFillRange" that shows "Calculation!G85:I92".

I have two questions about this. First of all does this refer to cells on a sheet called "Callculation" ? If so how do I find this sheet. I cant find any sheet with this name when i go to sheet-unhide. Second of all, how do I make a listbox calculate from a certain area on a specific sheet?

Hi

1. Look at Data>Validation>Settings tab and Allow:List

You can input a range to fill the drop down list.

2. It is possible to hide a sheet and not have it show up when you go Format>Sheet>Unhide. Check in VBA help for the Visible property of worksheets and the xlSheetVeryHidden enumerated constant.

3.

Second of all, how do I make a listbox calculate from a certain area on a specific sheet?

I am afraid I don't understand what it is you want to do - can you be a bit more descriptive?

Richard

Hi,

Re: your last question.

Do you really mean 'how do I fill a list box with values from a range of cells?'

If so it depends on whether the listbox is a simple Excel listbox added from the Forms toolbar (and which is permanently displayed on the sheet), or is it a listbox from a Visual Basic User Forms Activex control? I'll assume the former.

The easiest way is to first name the range of cells you wish to see in the listbox, then right click the listbox, choose the 'format control' option and enter the name of the 'Input Range' box.

HTH

Thank you for your answers.

I still wonder how I get the "Calculation" worksheet to be visible. When I look in the VBA code I dont see anything about it. Where should I look? What should I do?

Hi,

Go to the Project Explorer window in the VBE and click on the worksheet name that's hidden. Now look in the Properties window that shows all the various properties for that particular sheet and find the 'Visible' property which is currently set to '2 - xlSheetVeryHidden' and set it to '1 - xlSheetVisible'

Alternatively you could type in the VBE 'Immediate' window...

Please Login or Register  to view this content.

Rgds

When i right click on the sheets in the VBA editor I can only access something called "VBA project properties" not the properties for a particular sheet.

Also when I enter Sheet1.Visible=True nothing happends. I still cant access the hidden sheets.

Does anyone know what Im doing wrong?