Wingdings 2 Ticks and crosses

[Jack]

Hi everyone. An issue that has bugged me for a long time is for a simple checklist situation, I like to use say Wingdings 2 format and enter "R" (for a tick) and "S" (for a cross) and set up a simple validation drop down to select a tick or a cross in a checklist type situation. But if the spreadsheet is for non proficient Excel users it's weird to ask them to select "R" or "S" to get a tick or cross. It would be much better that they see say Y in the validation dropdown but then R is substituted and entered to the cell which is formatted to Wingdings 2 and shows a tick. Can anyone think of a way?


I know you could use 2 cells side by side and in one you pick Y and a formula puts R in the 2nd Wingdings 2 formatted cell. You could also use a form control tick box but I find these cumbersome as they don't copy well as the linked cell is always absolute and doesn't change relative to it's new cell address.

thanks

 

[Jordan]

Well, I would still suggest the side-by-side solution. However, instead of using a control, you can use Data Validation (under the Data tab, in Data Tools). You could create a "Yes/No" drop down like the ones described here: http://www.contextures.com/xldataval01.html. Then, in the cells next to drop down, use some conditional to return an R for Yes or S for No.

 

[Luke M]

You could also use a worksheet change event (assuming you use Data Validation drop down). To install, right click on sheet tab, view code, paste this in. You would only need to modify the MyCell callout as needed. Code will automatically change user's choice to either an R or an S

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyCell As Range

'Which cell is user changing?
Set MyCell = Range("A2")

If Intersect(Target, MyCell) Is Nothing Then Exit Sub

If MyCell = "Y" Then
MyCell = "R"
ElseIf MyCell = "N" Then
MyCell = "S"
End If
End Sub

[/pre]

 

[vijaySharma]

@John,


For this example I have selected Column C only.


1. Select Cells C2:C20 and then setup Data Validation--->List---> Y,N


Now bring up the VB Editor (ALT+F11)


Double click on the sheet where you have setup the data validation and paste the below code...

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then
Exit Sub
ElseIf Target.Value = "Y" Then
Target.Font.Name = "Wingdings 2"
Target.Value = "P"
ElseIf Target.Value = "N" Then
Target.Font.Name = "Wingdings 2"
Target.Value = "O"
End If
End Sub

[/pre]

Now everytimg you select a Y or N from the dropdown the cell will be automatically adjusted to display the tick or cross.


Just a quick note on what not to do..... Do not copy the validation setting from one cell and paste to a group of cells, you will get a run time error, which you can safely click on End. After this the functionality will resume to normal.


~VijaySharma

 

[Jack]

Hello Luke M and Vijay thanks to both of you for those range or column specific solutions which make sense and will work well. I will just have to accept there is no non VBA solution. I tried to put REPLACE or SUBSTITUTE formulas in the validation but it doesn't like it.

 

[fred]

Question:


Is a tick box and cross box the deciding factor in the worksheet that no other alternative can work just the same, or better?

 

[Jack]

@Fred Hay Fred not sure what you mean? Of course a better working alternative can be used if you have one? I am in a workplace that needs a lot of checklists for non proficient Excel users and we use these drop down lists a lot using validation and if the result can be a graphic rather than a word or letter than I thought all the better. Other methods such as form controls are problematic as they are not easily moved or copied because of the way the address is linked. Cheers

 

[xld]

Have the Y and N as the choice, and a formula that says =IF(cell="Y","R","S") formatted as Windings somewhere, then use the camera to take a picture which you overlay onto the Y/N cell.

 

[Luke M]

+1 to xld's suggestion. =)

 

[Jack]

Very creative xld ! I think too time consuming if you had to do lots and so I would use a form control but a great idea.

thanks all for the posts

 

[fred]

Hi Xld,


Could you demonstrate how it is done per your suggestion? How the overlay would appear as a ticker or a cross within the pulldown menu? Thanks.

 

[xld]

Here you are, a very simple example http://cjoint.com/?AJhkZ0socOK

 

[Jack]

Well all, I didn't want to open this topic up again but I came across some great code credit to AllExperts.com and spent hours adapting it to enter 3D checkbox form controls in a range of cells and it picks up the activecell address as the linked cell.


Hope you like it - I didn't intend this to be a VBA post but it evolved into one.


Also note the range selection method used below sometimes fails in 2003 if the worksheet has conditional formatting already - I read this in Ron DeBruin's site.


Sub AddCheckBox()

Dim Cell As Range

Dim CBWidth As Integer

Application.DisplayAlerts = False

On Error Resume Next

Set rRange = Application.InputBox(Prompt:="Choose the range for checkboxes. Press cancel to opt out or select the range", Title:="Specify Range now...", Type:=8)


Application.DisplayAlerts = True

If rRange Is Nothing Then

Exit Sub

Else

On Error GoTo 0

DelCheckBoxesB4NewOnes 'Do the delete macro or to delete all checkboxes in the worksheet modify to ActiveSheet.CheckBoxes.Delete


CBWidth = 0.85 ' this is the smallest width I could get but if you want wider just increase it


For Each Cell In rRange

With ActiveSheet.CheckBoxes.Add(Cell.Left, _

Cell.Top, CBWidth, Cell.Height) 'note the variable CBWidth

.Display3DShading = True

.LinkedCell = Cell.Offset(, 0).Address(External:=True)

.Interior.ColorIndex = xlNone 'or say 27 for yellow or xlAutomatic

.Caption = "" 'or what ever you want

End With

Next


With rRange

.Rows.RowHeight = 15

'.Columns.ColumnWidth = 13 ' turn on if you need this but with the checkbox width set to it's narrowest, probably not needed

.Font.ColorIndex = 2 'this is white so TRUE/FALSE disappears

End With

ActiveCell.Select

End If

End Sub

 

[Jack]

Oops, sorry this is the little macro called in the above. Also very importantly I declared


Public rRange As Range


at the top of the module which is used in this and the above macro. Apologies for not putting this all in one post.

Cheers


Sub DelCheckBoxesB4NewOnes()

Dim c As Range

rRange.Select

On Error Resume Next

For Each c In rRange

c.CheckBoxes.Delete

Next

On Error GoTo 0

End Sub