Drop down list for excel97 
Author Message
 Drop down list for excel97

I am basicly new to VBA except for the fact that I have done a few control
buttons using macros of my regular movements in a spreadsheet. I would like
to have a drop down box that will do the following:

            1    Select a cell
            2    Go to drop down list
            3    Select a name from the list
            4    Name will enter into the selected cell

I had this set up once in excel version 5, but I can't get it to work in
excel 97.

Can anyone help me with what the macro could be...?

TIA

Colin Mawdsley



Wed, 18 Jun 1902 08:00:00 GMT  
 Drop down list for excel97
Colin,

Perhaps a simple Data Validation dropdown list might serve your
purpose.  You will need a list of names say in A1:A9  Then select a
cell or range of cells where you want to use the list.  Then on Menu
go to Data>Validation  Select "list" from Allow box.  Enter your range
A1:A9 and go from there.  When a user selects the cell or a cell in
the range you picked, they will get a dropdown list of the names.

HTH   Gord

On Thu, 03 Feb 2000 21:36:19 GMT, "Colin Mawdsley"

Quote:

>I am basicly new to VBA except for the fact that I have done a few control
>buttons using macros of my regular movements in a spreadsheet. I would like
>to have a drop down box that will do the following:

>            1    Select a cell
>            2    Go to drop down list
>            3    Select a name from the list
>            4    Name will enter into the selected cell

>I had this set up once in excel version 5, but I can't get it to work in
>excel 97.

>Can anyone help me with what the macro could be...?

>TIA

>Colin Mawdsley



Wed, 18 Jun 1902 08:00:00 GMT  
 Drop down list for excel97


Quote:
> I had this set up once in excel version 5, but I can't get it to work in
> excel 97.

The same code should work (using the Forms toolbar dropdown).

Sub DropDownName_Change()
  With ActiveSheet.DropDowns("DropDownName")
    ActiveCell.Value = .List(.ListIndex)
  End With
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup



Wed, 18 Jun 1902 08:00:00 GMT  
 Drop down list for excel97
Perfect solution, Thank you very much

Colin Mawdsley

Quote:

>Colin,

>Perhaps a simple Data Validation dropdown list might serve your
>purpose.  You will need a list of names say in A1:A9  Then select a
>cell or range of cells where you want to use the list.  Then on Menu
>go to Data>Validation  Select "list" from Allow box.  Enter your range
>A1:A9 and go from there.  When a user selects the cell or a cell in
>the range you picked, they will get a dropdown list of the names.

>HTH   Gord

>On Thu, 03 Feb 2000 21:36:19 GMT, "Colin Mawdsley"

>>I am basicly new to VBA except for the fact that I have done a few control
>>buttons using macros of my regular movements in a spreadsheet. I would
like
>>to have a drop down box that will do the following:

>>            1    Select a cell
>>            2    Go to drop down list
>>            3    Select a name from the list
>>            4    Name will enter into the selected cell

>>I had this set up once in excel version 5, but I can't get it to work in
>>excel 97.

>>Can anyone help me with what the macro could be...?

>>TIA

>>Colin Mawdsley



Wed, 18 Jun 1902 08:00:00 GMT  
 Drop down list for excel97
Thank you Bill, (I was using the term Combo Box instead of DropDowns)

Colin Mawdsley

Quote:



>> I had this set up once in excel version 5, but I can't get it to work in
>> excel 97.

>The same code should work (using the Forms toolbar dropdown).

>Sub DropDownName_Change()
>  With ActiveSheet.DropDowns("DropDownName")
>    ActiveCell.Value = .List(.ListIndex)
>  End With
>End Sub

>Bill Manville
>MVP - Microsoft Excel, Oxford, England
>No email replies please - reply in newsgroup



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. unbound drop-down list box to limit the values in another drop-down list box

2. drop down list w/out drop down box

3. Data Validation Drop down list ( make longer drop box)

4. Data Validation: Width of In-Cell Drop Down Boxes Excel97

5. Excel97: Accessing information from a drop down in VBA

6. Excel97: Accessing information from a drop down in VBA

7. drop down list to outlook contact list

8. Add list to the Dropped Down List (Combo Box)

9. Drop down list based on another list

10. scroll list in cell or drop-down list


 
Powered by phpBB® Forum Software © phpBB Group