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]

Put the pre-defined values in a list, name the list then use data validation for the cell to only show that list.

 

[Dimitris254]

how's that vba???????

 

[gallen]

how's that vba???????

    With ActiveCell.Validation        
        .Delete 'clear any validation
        .Add xlValidateList, , xlBetween, "= status"
    End With

You can use VBA to set the dropdown validation of the cell

 

[Dimitris254]

i see, thx

but is there a way to define the options in the vba code?

i wouldn't like to create the list somewhere and reference it (most cells will be locked)

 

[gallen]

Yes, you could use something like:

Sub SetOptionRange()
    'Write the values to the desired cells
    Range("A1").Value = "Not started"
    Range("A2").Value = "Work in progress"
    Range("A3").Value = "Finished"
    Range("A4").Value = "Not applicable"
    
    'name the range of cells as 'status'
    Range("A1:A4").Name = "status"
    
End Sub

Then use the previous code to dynamically set the validation of whichever cell you want to display these options

 

[gallen]

Ahh sorry just saw your point about not setting it elsewhere. You'd have to use an active x drop down then add the items in code.

You could always use a hidden sheet and the code I've shown. Hiding will prevent users from altering it

Adding a simple Combo Box is straight forward using the Active X controls, if you need a hand let me know.

 

[Dimitris254]

welp, i did find a place to put the options (in the "IDs" worksheet), but the trick is that for every selected cell, i use different names.

Currently i have difficulty in pointing to the range:

Sub define_status_names()

Dim q_id As String

Application.EnableEvents = False            ' disable warning (due to protected cells format)


    With ActiveCell
                              
        ' write "status" above drop-down list
        .Offset(11, 0).Value = "Status"
        .Offset(11, 0).HorizontalAlignment = xlLeft
        
        ' define name for drop-down lists according to id
        q_id = .Offset(-1, -10).Value
        ThisWorkbook.Worksheets("IDs").Range("A102:A105").Name = "_" & q_id & "Status"
        
        ' drop-down list
        .Offset(12, 0).Validation
            .Delete
            .Add xlValidateList, xlValidAlertStop, xlBetween, "=$A$1:$A$10"
            .InCellDropdown = True
                        
    End With
    
Application.EnableEvents = True

 

[gallen]

In this line:

[COLOR=#574123][I]ThisWorkbook.Worksheets("IDs").Range("A102:A105").Name = [/I][/COLOR][B]"_" & q_id & "Status"[/B]

You name range A102:A105 using the variable q_id with the suffix "Status"

Yet in this line:

[COLOR=#574123].Add xlValidateList, xlValidAlertStop, xlBetween, [/COLOR][COLOR=#ff0000]"=$A$1:$A$10"[/COLOR]

You set the list to cell A1:A10.

I'm guessing it should read

[COLOR=#574123].Add xlValidateList, xlValidAlertStop, xlBetween, [/COLOR][B]"=_" & q_id & "Status"[/B]

 

[Dimitris254]

Sub define_status_names()

Dim q_id As String


Application.EnableEvents = False            ' disable warning (due to protected cells format)


    With ActiveCell
                
        ' write "status" above drop-down list
        .Offset(11, 0).Value = "Status"
        .Offset(11, 0).HorizontalAlignment = xlLeft
        
        ' define name for drop-down lists according to id
        q_id = .Offset(-1, -10).Value
        ThisWorkbook.Worksheets("IDs").Range("A102:A105").Name = "_" & q_id & "Status"
        
        ' drop-down list
        .Offset(12, 0).Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="_" & q_id & "Status"
            .InCellDropdown = True
        End With
        
    End With




Application.EnableEvents = True
    
End Sub

hmm, now i get a drop-down menu with only the option (for example) "_100Status"