VBA Code to change drop down list text

EMc209

New Member
Joined
Mar 17, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Screenshot 2021-03-17 170954.jpg
    Screenshot 2021-03-17 170954.jpg
    100.2 KB · Views: 5

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,584
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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

New Member
Joined
Mar 17, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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!
 

Forum statistics

Threads
1,141,400
Messages
5,706,212
Members
421,432
Latest member
yash0468

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top