View Full Version : Solved: if user keys a specific word in cell
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
04-19-2011, 10:02 AM
Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "NC" Then MsgBox "OK!"
End Sub
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
04-20-2011, 11:12 PM
it worked for me and I was able to save it..what kind of workbook are you saving it as?
Bob Phillips
04-21-2011, 12:15 AM
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?
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
04-21-2011, 06:38 AM
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
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
04-22-2011, 01:52 PM
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.
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?
This seems to work but is it correct?
On Error GoTo ERRO
If (Target.Value) = "NC" Then
MsgBox Target.Address
End If
ERRO:
BrianMH
04-22-2011, 02:42 PM
I would say
if target.Cells.count > 1 then
exit sub
end if
Simon Lloyd
04-22-2011, 11:02 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.