Solved: if user keys a specific word in cell

[wilg]

I need to run a specific code if a user keyes in "NC" in the active cell.

I tried
If Int(ActiveCell.Value) = "NC" Then

but when I hit enter the active cell is the cell below now. So I don't think it fires.

Any suggestions?

[shrivallabha]

Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "NC" Then MsgBox "OK!"
End Sub

[wilg]

Hi thanks for the response but it doesnt seem to work. If I use it under

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

it will work but when I goto save it gives an error at

If Target.Value = "NC" Then

THE ERROR MESSAGE IS TYPE MISMATCH..

any other suggestions?

[BrianMH]

it worked for me and I was able to save it..what kind of workbook are you saving it as?

[Bob Phillips]

shrivallabha did not use the Workbook_SheetChange event , he used the Worksheet_Change, which goes in the appropriate sheet module. Why did you change it, do you have chart sheets?

[wilg]

Hi guys, I should have explained that sorry.

I used the workbook_change event as my workbook has 250 sheets. and each sheet it added from one master worksheet. I was trying to avoid using any code in the worksheet but rather using it in the workbook part so if I had to modify any code in the future I don't have to do it on 250 sheets.

Is there a way to adapt it to the workbook_change event?

[Simon Lloyd]

This is still a workbook event codePrivate Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Value = LCase("NC") Then
MsgBox Target.Address
End If
End SubBut do you really want the code to run for every single cell? you should narrow it down like thisPrivate Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Or Target.Column <> 3 Then Exit Sub 'change and add as needed or use the intersect method
If Target.Value = LCase("NC") Then
MsgBox Target.Address
End If
End Sub

[wilg]

Hi Simon, I'm sorry I cant post a workbook due to its sensitivity and size, but the 2 codes you provided still do not work for me.


The first one will fire only when I select back on that cell.

The second one I cannot quite understand to intersect.

I hope I'm not making this too complicated but in the workbook_change event all I want is if the cell value I just keyed was "NC" then I need to run another code or msgbox what ever..

If this cannot be don in the workbook_change event then please let me know.

[BrianMH]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Ucase(Target.Value) = "NC" Then
MsgBox Target.Address
End If
End Sub


This one works for me and works both on "NC", "nc", "Nc" and "nC". It uses workbook_sheetchange instead of workbook_sheetselectionchange. If you want it to only work on "NC" then remove the Ucase.

[wilg]

Hi Brian, thanks for the quick reply. I have the code working now.

I also found out the error was happening when multiple cells were selected to clear their contents.

So is there a way to manipulate your code to cancel if multiple cells are selected?

[wilg]

This seems to work but is it correct?

On Error GoTo ERRO
If (Target.Value) = "NC" Then
MsgBox Target.Address
End If
ERRO:

[BrianMH]

I would say

if target.Cells.count > 1 then
exit sub
end if

[Simon Lloyd]

I would say

if target.Cells.count > 1 then
exit sub
end ifIn this instance you don't really need the end if, you just use a one liner

If Target.Cells.count > 1 Then Exit Sub

If you also want to look it down to a columnIf Target.Column <> 1 Then Exit SubChange 1 for whichever column number you wish, but i already supplied these solutions in http://www.vbaexpress.com/forum/showpost.php?p=241560&postcount=7