MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro question


Posted by Liliana Avancena on December 13, 2001 10:49 AM

I am trying to figure out a way to set up a macro in a spreadsheet where every time I enter the number 8 in a cell it automatically turns it into a checkmark. Can anyone help???


Posted by Dan on December 13, 2001 11:01 AM

Does it need to be a checkmark? How about highlighting the cell instead? Both could probably be down but the latter would be much easier! You wouldn't even need a macro.

You can use conditional formatting to change the background color if the cell value is 8. Highlight the cells that you want to format, click on Format, Conditional Format. Select "Cell Value Is" and "Equal to" and type in 8. Then change the format to however you want to format the cell. Let me know if you need clarification or more help.

Posted by Jacob on December 13, 2001 11:12 AM

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
userinput = Target.Value
If userinput = 8 Then
newinput = "Check Mark"
Application.EnableEvents = False
Target = newinput
Application.EnableEvents = True
End If
End Sub

This will put the word check mark whenever you type 8. I dont know how to add an actual checkmark, but you can put any text or keyboard symbols.

Hope this gets you started.

Jacob

Posted by Tom Morales on December 13, 2001 11:48 AM

Liliani - I've copied symbols from Word into Excel. Try pasting an appropriate symbol into an unused, out-of-the-way cell. Using Jacob's approach, when your macro launches to ferret out the number eight, have the macro copy the symbol's cell's formula. eg,
chequemark = Range("AG9000").formula 'presuming you've hidden the symbol in that cell
and then make the "8" cell's formula equal to checkmark.
eg, Target.Formula = chequemark

That may work.
Tom

Posted by Chris D on December 13, 2001 11:57 AM

checkmark symbol

Uppercase "P", font-formatted to Wingdings2 should give you the checkmark symbol - hopefully you can slot this into the helpful codes above

Posted by Jacob on December 13, 2001 1:10 PM

The Final Code

Hi

I just worked out the complete code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserInput
Dim CurrentRange
Dim FinalRange

Application.ScreenUpdating = False
UserInput = Target.Value
CurrentRange = Target.AddressLocal
FinalRange = ActiveCell.AddressLocal
On Error GoTo z:

If UserInput = 8 Then
Target = "P"
Application.EnableEvents = False
Range(CurrentRange).Select
With Selection.Font
.Name = "Wingdings 2"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range(FinalRange).Select
Application.EnableEvents = True
z:
End If
Application.EnableEvents = True
End Sub


Hope this is what you want

Jacob

Posted by Tom Morales on December 13, 2001 1:25 PM

Re: The Final Code

Wow. Good Work. Now that that's been thrashed out, it's probably simpler for Liliana to change the cell font format to Wingdings 2, and type in "P" instead of an "8".

Posted by Jacob on December 13, 2001 1:54 PM

Re: The Final Code

Well she wanted it to be automatic and thats what my code does. It will also work if a formula results in 8 value of 8.

Jacob