Drop down list - just a quicky

[thomasleitner]

i know how to create drop down lists and use them a lot.. one thing i havnt gotten to work til now is to have a standard value in the list. meaning. i got a list of say 5 names. in the drop down list i would like it to say - "select a name" so the user is drwn to the field

 

[Hui]

Do you mean Drop Down as in Validation or Forms ?

 

[thomasleitner]

i mean validation.. i want to use it for a kind of dashboard ..


i want the users to be able to select various variables . which connect to a sumifs function.


the listvalues could be

jan

feb

mar

apr

mai

...


i name the list months and refer to it ( use another sheet)


the cell i have the data validation drop down list in is blank as standard.


what i like is that that cell has a standrd value written in it. saying for example... selecet a month


then i click on the cell and get the drop down list.


off course i can write that text next to the cell but i have limited space. so i want the text in the field where the drop down list is located.

 

[xld]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const WS_RANGE As String = "D3" '<<<< change to suit


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then


With Target


.Validation.Delete

.Validation.Add Type:=xlValidateList, Formula1:="=months"

.Value = ""

End With

End If

End Sub


'This is worksheet event code, which means that it needs to be

'placed in the appropriate worksheet code module, not a standard

'code module. To do this, right-click on the sheet tab, select

'the View Code option from the menu, and paste the code in.

 

[kchiba]

Hi


You can enter the names in the space for list separated by a comma


kanti

 

[thomasleitner]

@ xld... works perfect.. even though its a script and i was teh first time i used scripts


@kchiba.. i know thx..


i found a third way.. very basic.. well thats me:.-) i just can write it in the box as a text.. off course meaning that i cant put a error message in if someone types something wrong in

 

[xld]

Get used to scripts Thomas, it extends the power of Excel geometrically.