Excel drop down list choices 1

[spiritwater2004]

In an Excel spreadsheet I wish to have each field in a column display as a drop down list that accepts either two items or any number. In other words the list will be "choice a", "choice b", or IsNum() where the user can enter any number. So far I have not been able to accomplish this. Can anyone help?

 

[KenWright]

Put your two choices in two cells eg K1:K2. Select those two cells AND a blank cell eg K1:K3 and name it eg

mylist

On your cell sto have the dropdown, choose data / validation / list and put

=mylist

in there.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[spiritwater2004]

I've tried that and just receive an error message. "=mylist" is not accepted. Any other suggestions?

 

[KenWright]

it's the only way to do it. You named it correctly yes?
What version Excel? You put =mylist in where it said 'source' yes?

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[spiritwater2004]

I'm sorry,I tried it again and it worked. I must have missed something the first time. Thanks ever so much!

 

[KenWright]

No problem - Glad it worked for you

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[spiritwater2004]

It allows me to select from the list or put in a number but also allows me to enter non-numeric characters. How do I allow only the items from the list or numeric characters only?

 

[01Patrik]

Use this in a worksheet.change event

Private Sub Worksheet_Change(ByVal Target As Range)

Dim aa As Boolean, bb As String

'
' Enter the range of your validation below instead of
' "D1:D5"
'

If Application.Intersect(Target, Range("D1:D5")) Is Nothing Then Exit Sub


'
' Enter the ranges that contain your dropdownlist in the
' validation below instead of "A1", "A2", "A3"
'
If Target.Value = Range("A1").Value Then Exit Sub
If Target.Value = Range("A2").Value Then Exit Sub
If Target.Value = Range("A3").Value Then Exit Sub

aa = Application.WorksheetFunction.IsNumber(Target)

bb = "The value entered is not a number. Please try again"

If aa = False Then
MsgBox bb

With Target
    .Value = ""
    .Select
End With

End If

Note htat you have to change the ranges according to the comments within the code.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[01Patrik]

Use this in a worksheet.change event

Private Sub Worksheet_Change(ByVal Target As Range)

Dim aa As Boolean, bb As String

'
' Enter the range of your validation below instead of
' "D1:D5"
'

If Application.Intersect(Target, Range("D1:D5")) Is Nothing Then Exit Sub


'
' Enter the ranges that contain your dropdownlist in the
' validation below instead of "A1", "A2", "A3"
'
If Target.Value = Range("A1").Value Then Exit Sub
If Target.Value = Range("A2").Value Then Exit Sub
If Target.Value = Range("A3").Value Then Exit Sub

aa = Application.WorksheetFunction.IsNumber(Target)

bb = "The value entered is not a number. Please try again"

If aa = False Then

MsgBox bb
With Target
    .Value = ""
    .Select
End With

End If

Note htat you have to change the ranges according to the comments within the code.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[spiritwater2004]

Worked great! I am new to Excel and didn't have a clue, Thank you very much.

 

[01Patrik]

No problem, the possibilities are endless as long as people are open for suggestions... and you won't get any if you won't ask for them!


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.