VBA simple drop-down menu

[Dimitris254]

i'd like a simple "status" drop-down menu to appear in the selected cell.

the options are pre-defined:

"Not started" (appears by default)
"Work in progress"
"Finished"
"Not applicable"

I would like to define a name for the cells containing the status, so i don't know if a commobox can work.

something like:

With ActiveCell

' drop-down menu
create drop-down list
option 1= "Not started" --> set default
option 2= "Work in progress"
option 3= "Finished"
option 4= "Not applicable"

' define name
.Name = "status"

End with

 

[gallen]

Highlight the drop down and go to Data Validation. Check what's in there. It should start with =

 

[Dimitris254]

it's the named range, "_100Status", no options appear

edit: the named range appears in the Name Manager correctly
Refers to=IDs!$A$102:$A$105
Value ={"Not started", ...}

 

[Dimitris254]

looks like i fixed it:

        .Offset(12, 0).Select        
With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=IDs!A102:A105"
            .InCellDropdown = True
        End With
        Selection.Name = "_" & q_id & "Status"

last thing left (if doable): how to i set a default value to appear?