Making a cell only ecept an X and protecting code

[Kelly***********]

OK here what I got I need F22 and F23 to only
except an X. At this point it only triggers the red if you
enter an X but the cells does let you type in anything and
that will be a problem. Then Id like to protect or hide
the code some how so people don't mess with it. So if you
have a minute or two I would be most great full.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Rem Put an X in cells F22 or F23 makes Cells F14 AND F16
red
Rem _____________________AREA WORKS_____________________
Rem YOU CAN ENTER ANY TEXT, BUT ONLY AN X TRIGGERS THE RED

If Target.Address = "$F$22" Or Target.Address
= "$F$23" Then
If UCase(Target) = "X" Then
If Len(Range("F14")) = 0 Then
Range("F14").Interior.ColorIndex = 3
End If
End If
End If

If Target.Address = "$F$22" Or Target.Address
= "$F$23" Then
If UCase(Target) = "X" Then
If Len(Range("F16")) = 0 Then
Range("F16").Interior.ColorIndex = 3
End If
End If
End If

Rem Take out the X in cells F22 and F23 makes Cells F14
and F16 white
Rem _______________________AREA WORKS__________________

If Target.Address = "$F$22" Or Target.Address
= "$F$23" Then
If UCase(Target) = "" Then
If Len(Range("F14")) = 0 Then
Range("F14").Interior.ColorIndex = 0
End If
End If
End If

If Target.Address = "$F$22" Or Target.Address
= "$F$23" Then
If UCase(Target) = "" Then
If Len(Range("F16")) = 0 Then
Range("F16").Interior.ColorIndex = 0
End If
End If
End If

Rem Enter Text in F14 or F16 turns cells F22 and F23 red
Rem ______________________Area Works______________________

Dim ln As String

ln = Range("F14").Text
If Range("F14,F16") = (ln) Then
Range("F22,F23").Interior.ColorIndex = 3
End If

Rem Remove Text in F14 or F16 turns cells F22 and F23 white
Rem ______________________Area Works______________________

Dim fn As String

fn = Range("F16").Text
If Range("F14,F16") = (fn) Then
Range("F22,F23").Interior.ColorIndex = 0
End If

End Sub

 

[Orlando Magalhães Filho]

Hi Kelly,

Add some lines at the start of your code like below. To protect your VBA
code, at VBE Window, go Tools menu > VBAProjectProperties command >
Protection Tab > Select "Lock project for viewing" option and insert a
password.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If UCase(Target.Value) <> "X" And (Target.Address = "$F$22" Or
Target.Address = "$F$23") Then
If Target = Empty Then Exit Sub 'Maybe you don't need this
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If

Rem Put an X in cells F22 or F23 makes Cells F14 AND F16 red
Rem _____________________AREA WORKS_____________________
Rem YOU CAN ENTER ANY TEXT, BUT ONLY AN X TRIGGERS THE RED
....

HTH

---
Orlando Magalhães Filho

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)

 

[Chiller]

This may be off course !
But could you use "Data Validation" on the Cell
See \Data \ Validation
"allow" list or Custom
"Source range" (you need to set up a range name with "X"
in the cell, maybe sheet 2.

Format the cell to accept "X" from a pre-defined list,
either typed in or picked from the list?

There are various restriction levels in this function to
validate acceptable data input.

Then Protect the work sheet, with a password.

Chiller.