DROP DOWN LIST WHEN SPREAD SHEET OPENS

[Dave]

Hi

I have a spreadsheet that when you open it a drop down list shows a choice
of names to choose from that each relate to an individual sheet. How do I
access this drop down list to edit the names?

Thanks

 

[JLatham]

Is this drop down a control or is it in a cell?

I'll tackle the drop-down-in-a-cell possibility, and if it turns out to be a
control, the discussion will continue <g>.

If it is in a cell, then the cell is using Data Validation. Click the cell
and go to Data --> Validation and you'll see the dialog used to set up the
list. There is an area with the "Source" label associated with it. The
names may simply be typed into that area, separated by commas. You could
edit the list directly in that list.

But the "Source" could be a formula such as =MyNamesList and MyNamesList
will be a named range in your workbook referring to a range of cells
somewhere with the list in it. That list could be on another sheet, and that
sheet could even be hidden from view. You can find out where that list is by
using Insert --> Name --> Define and picking it from the list and you'll see
it's location in the "Refers To" section.

Finally, if none of the above yield any results, or if you change the list
and it changes back to its original contents, then it is probably defined in
a macro that runs when either the workbook is opened or that worksheet is
selected. You'll have to look in the workbook's code to find where it is set
up ata.

 

[Dave]

Hi

The drop down opens in its own box when you open the spreadsheet. I rpesume
it is a control as I can not find anything in validation or a list with
reference to any cells.

Thanks

 

[JLatham]

Let's see if it's a combo box from the Forms toolbar. Try right-clicking on
the control (while the sheet is unprotected) and see if a popup list appears.
If it does, then choose [Format Control]. Then go to the [Control] tab in
the dialog. There will be an entry for "Input Range" which will be the
source of the list. There may or may not be an entry for the "Cell link"
part of things.

If nothing happened when you tried right-clicking or if there was no
[Control] tab, then it's a Combo box from the Control Toolbox. To work with
those, use View-->Toolbars and choose the Control Toolbox. Click the "Design
Mode" icon in it (upper left icon, looks like a right-triangle, ruler and
pencil). In design mode, right click it and choose [Properties]. Look for
the ListFillRange entry in the list, that's the equivalent of the "Input
Range" in the other type of combo box.

If you didn't find any entries in "Input Range" or "ListFillRange", then it
would almost have to be getting setup in code somewhere. Hopefully you found
some code some where. Try going back into the VB Editor and then use Edit
--> Find and enter
..AddItem for the search phrase to find and select the "Current Project"
option and try and find that. If you do, you've probably found where it's
being set up in code.

If all else fails, send me a copy of the book and I'll try to figure it out.
Remind me in an email of this discussion (a link to it perhaps), and I'll
see what I can see. Email is (remove spaces)
Help From @JLatham site .com

 

[Dave]

Hi

I have explored all these options. I cant locate it. It looks like some of
the projects in the Visual Basic editor are password protected. As this is an
old sheet which has been passed around and I am unable to locate the original
administrator who set the password up. I am unable to send this book to you
as it contains sensitive information. It looks like I am going to have to
recreate the book and learn how to set up a control box for this function!
Any advice?

Thanks for your help!
David

Let's see if it's a combo box from the Forms toolbar. Try right-clicking on
the control (while the sheet is unprotected) and see if a popup list appears.
If it does, then choose [Format Control]. Then go to the [Control] tab in
the dialog. There will be an entry for "Input Range" which will be the
source of the list. There may or may not be an entry for the "Cell link"
part of things.

If nothing happened when you tried right-clicking or if there was no
[Control] tab, then it's a Combo box from the Control Toolbox. To work with
those, use View-->Toolbars and choose the Control Toolbox. Click the "Design
Mode" icon in it (upper left icon, looks like a right-triangle, ruler and
pencil). In design mode, right click it and choose [Properties]. Look for
the ListFillRange entry in the list, that's the equivalent of the "Input
Range" in the other type of combo box.

If you didn't find any entries in "Input Range" or "ListFillRange", then it
would almost have to be getting setup in code somewhere. Hopefully you found
some code some where. Try going back into the VB Editor and then use Edit
--> Find and enter
.AddItem for the search phrase to find and select the "Current Project"
option and try and find that. If you do, you've probably found where it's
being set up in code.

If all else fails, send me a copy of the book and I'll try to figure it out.
Remind me in an email of this discussion (a link to it perhaps), and I'll
see what I can see. Email is (remove spaces)
Help From @JLatham site .com

Hi

The drop down opens in its own box when you open the spreadsheet. I rpesume
it is a control as I can not find anything in validation or a list with
reference to any cells.

Thanks

 

[JLatham]

Dave,
I personally prefer to use the Combo box from the Forms toolbar, but
situations vary. I like it mostly because if I have to assign a macro to it,
it can be one in a standard code module, rather than ending up as part of the
code for that worksheet.

If you're going to have a list that you'd like to be more easily managed
(rather than using code to .AddItem to it to build up the list), then I'd put
my list on a worksheet that you eventually hide from view. You can use it to
store several lists, and perhaps even other interim information you want
without cluttering up visible sheets that the end user may be using. If you
put several lists on a single sheet, put one below the other - makes
inserting/deleting items from any list easy to do without accidentally
screwing up another list that may have an entry on the same row you just
deleted.

Depending on what you need in the way of a drop down and how it is to be
used, you may even want to consider using Data Validation in a cell on a
worksheet.

Debra Dalgleish has lots of info on Combo Boxes and tons of stuff about
using data validation, even "dependent" lists in several cells (downstream
lists change in response to a choice in an upstream list).
http://www.contextures.com/tiptech.html

Hi

I have explored all these options. I cant locate it. It looks like some of
the projects in the Visual Basic editor are password protected. As this is an
old sheet which has been passed around and I am unable to locate the original
administrator who set the password up. I am unable to send this book to you
as it contains sensitive information. It looks like I am going to have to
recreate the book and learn how to set up a control box for this function!
Any advice?

Thanks for your help!
David

Let's see if it's a combo box from the Forms toolbar. Try right-clicking on
the control (while the sheet is unprotected) and see if a popup list appears.
If it does, then choose [Format Control]. Then go to the [Control] tab in
the dialog. There will be an entry for "Input Range" which will be the
source of the list. There may or may not be an entry for the "Cell link"
part of things.

If nothing happened when you tried right-clicking or if there was no
[Control] tab, then it's a Combo box from the Control Toolbox. To work with
those, use View-->Toolbars and choose the Control Toolbox. Click the "Design
Mode" icon in it (upper left icon, looks like a right-triangle, ruler and
pencil). In design mode, right click it and choose [Properties]. Look for
the ListFillRange entry in the list, that's the equivalent of the "Input
Range" in the other type of combo box.

If you didn't find any entries in "Input Range" or "ListFillRange", then it
would almost have to be getting setup in code somewhere. Hopefully you found
some code some where. Try going back into the VB Editor and then use Edit
--> Find and enter
.AddItem for the search phrase to find and select the "Current Project"
option and try and find that. If you do, you've probably found where it's
being set up in code.

If all else fails, send me a copy of the book and I'll try to figure it out.
Remind me in an email of this discussion (a link to it perhaps), and I'll
see what I can see. Email is (remove spaces)
Help From @JLatham site .com

Hi

The drop down opens in its own box when you open the spreadsheet. I rpesume
it is a control as I can not find anything in validation or a list with
reference to any cells.

Thanks

:

Is this drop down a control or is it in a cell?

I'll tackle the drop-down-in-a-cell possibility, and if it turns out to be a
control, the discussion will continue <g>.

If it is in a cell, then the cell is using Data Validation. Click the cell
and go to Data --> Validation and you'll see the dialog used to set up the
list. There is an area with the "Source" label associated with it. The
names may simply be typed into that area, separated by commas. You could
edit the list directly in that list.

But the "Source" could be a formula such as =MyNamesList and MyNamesList
will be a named range in your workbook referring to a range of cells
somewhere with the list in it. That list could be on another sheet, and that
sheet could even be hidden from view. You can find out where that list is by
using Insert --> Name --> Define and picking it from the list and you'll see
it's location in the "Refers To" section.

Finally, if none of the above yield any results, or if you change the list
and it changes back to its original contents, then it is probably defined in
a macro that runs when either the workbook is opened or that worksheet is
selected. You'll have to look in the workbook's code to find where it is set
up ata.

:

Hi

I have a spreadsheet that when you open it a drop down list shows a choice
of names to choose from that each relate to an individual sheet. How do I
access this drop down list to edit the names?

Thanks