Drop Lst - Defined Range 'Select Value' message in formula and not in defined list

[mattbarb]

Hi,

I have a defined list as a drop down. Is there a way to add to the top of the list something like 'Select Value' in the formula and not in the defined list?

Many thanks

 

[ranman256]

load the list , then set the 'select' text:

Me.ComboBox1.List = Sheets("data").Range("A2:A" & ActiveSheet.UsedRange.Rows.Count).Value
Me.ComboBox1 = "Select 1"

 

[mattbarb]

THANKS! Will give it a go.

 

[mattbarb]

Sorry how would that look with a defined list. Couldnt get it to work.

 

[mattbarb]

Hi, Can anyone help with this please? Many thanks

 

[MickG]

Based on your Named Range being called "myList" and you wanting to add to that list a Header like "Select value" and you validation cell being "C1" then try this, "Double Click event" in your sheet module.

" loading code "
Right click sheet tab, select View Code, vb window appears,Paste code into Code window.

"Run Code"
Double click "C1" to see modified List"
After selecting value. To reset "Select Value", Double click "C1" again.

Private [COLOR=navy]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR=navy]As[/COLOR] Range, Cancel [COLOR=navy]As[/COLOR] Boolean)
[COLOR=navy]Dim[/COLOR] Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]

Txt = "Select Value" & "," & Join(Application.Transpose(Range("MyList")), ",")
[COLOR=navy]With[/COLOR] [c1].Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Txt
    .ShowError = False
[COLOR=navy]End[/COLOR] With
[c1] = "Select Value"
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[mattbarb]

Thanks will give that a go!! Regards