Prev: how to draw fibonacci numbers
Next: Need Macro to reset dependent list
From: Dave on 12 May 2010 08:46
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
From: JLatham on 12 May 2010 10:50
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" wrote:

> 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
From: Dave on 12 May 2010 11:08
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" wrote:

> 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" wrote:
>
> > 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
From: JLatham on 12 May 2010 19:28
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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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
From: Dave on 13 May 2010 06:11
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

"JLatham" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
 |  Next  |  Last
Pages: 1 2
Prev: how to draw fibonacci numbers
Next: Need Macro to reset dependent list