VBA Code to change drop down list text

[EMc209]

Hey all! Hopefully I will find some help here.
My excel sheet tracks different task items in rows and one column is to "Mark complete" as this is a shared file.
I already created the dropdown using Data Validation with the Wingding 2 checkmark but I'd like the text in the dropdown list to read "complete" instead of "P"

I tried inserting the checkbox from the developer tab but I also couldn't get it in a fixed position within each cell in the column.

I've attached a mock task list spreadsheet if it helps. Fingers crossed!
Marked complete is column J
Task items are rows 6-70

 

[Zot]

This is how to can achieve that

How to create drop down list but show different values in Excel?

www.extendoffice.com

 

[Zot]

Here is what I would do in your case.

In a cell, create a Data Validation. Select List. In Source just type your list like this Complete,Not Complete, whatever separated just by comma.

Right click the sheet tab and select View Code like the tutorial said and put this macro

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngSelection As Range

Set rngSelection = Range("J6", "J70")

If Not Intersect(Target, rngSelection) Is Nothing Then
    If Target = "Complete" Then
        Target = ChrW(&H2713)
    End If
End If

End Sub

Then you are done. The drop down will show all the option you type in Source and if you choose Complete the Tick will show up.

 

[EMc209]

Here is what I would do in your case.

In a cell, create a Data Validation. Select List. In Source just type your list like this Complete,Not Complete, whatever separated just by comma.

Right click the sheet tab and select View Code like the tutorial said and put this macro

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngSelection As Range

Set rngSelection = Range("J6", "J70")

If Not Intersect(Target, rngSelection) Is Nothing Then
    If Target = "Complete" Then
        Target = ChrW(&H2713)
    End If
End If

End Sub

Then you are done. The drop down will show all the option you type in Source and if you choose Complete the Tick will show up.

It worked! Thank you, Zot!

 

[Zot]

It worked! Thank you, Zot!

You're welcomed