asked on

CODE in ColumnB execute only when maybe Enter key is pressed while in columnB

EXCEL 2003 VBA

Maybe I have this code in the wrong place ?

But I do not want this code to be executed from Column B unless the Enter key been pressed..

ie...
person is in column B

maybe use key combination Shift+Enter keys ?

Thanks
fordraiders

Private Sub Worksheet_Change(ByVal target As Range)
' looking for manual data entry
'  Application.ScreenUpdating = False
'  Application.EnableEvents = True

Dim blnValid As Boolean
Dim objCell As Range
Dim targ As Range
Dim targ2 As Range


ONLY PROCEED IF  B and Q are not both blank
Set targ = Union([B:B], [Q2:Q65536])
If Intersect(target, targ) Is Nothing Then Exit Sub


 
 If ActiveCell.Column <> 17 Then 'Column O

 ' looking for manual entry  01-06-2006 column A only
   Dim Result As String
   Set targ = Intersect(target, [B:B])
   If Not targ Is Nothing Then
   'check for other things
   ' This is for Column A
       If targ.Cells.Count = 1 Then
           If targ.Value <> "" Then
             'we are in the entry column
             Result = GetData(targ)
             AltSearch
             GreenAltSearch
             ParentSearch
             If Result <> "" Then
               MsgBox Result
             End If
           End If
       End If
   End If
   
   
 End If
'=================================================

If ActiveCell.Column = 17 Then 'Column q
 'Is in column q
' looking for manual entry  01-06-2006 column O only
   Dim Result2 As String
   Set targ2 = Intersect(target, [Q:Q])
   If Not targ2 Is Nothing Then
   'check for other things
   ' This is for Column O
       If targ2.Cells.Count = 1 Then
         If targ2.Value <> "" Then
             'we are in the entry column
             Result2 = GetDataWwg(targ2)
             AltSearch
             GreenAltSearch
             ParentSearch
              
              If Result2 <> "" Then
               MsgBox Result2
             End If
           End If
       End If
   End If
Else
 'Is not in column O
End If
'=================================================
Err_Worksheet_Change:
'  On Error GoTo 0
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
  
  
End Sub

Open in new window

Rory Archibald

Can I ask why it matters if they use the Enter key rather than say the Tab key to enter the data? Or do you literally want to trap the Enter key even if they don't change anything?

Fordraiders

ASKER

becaue is they type something for a change in the cell I do not always want the code to execute...

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

rorya, Could I also add if" sheet1" is not selected don't do this at all ?
Sheets("sheet1").Activate

Rory Archibald

Yes - in the Workbook (as opposed to worksheet) events, you can use something like:

If activesheet.name = "Sheet1" Then

Open in new window

Fordraiders

ASKER

rorya, This causes other problems, if I move the code out ...then I have to pass my "target" as range down the procedures?

' looking for manual entry 01-06-2006 column b
Dim Result As String
Set targ = Intersect(target, [B:B])

Sub KeyHook(ByVal target as Range)
Application.OnKey "+~", "macro_name(target)"
End Sub

Sub KeyHook(ByVal target As Range)
target = target
Application.OnKey "+~", "mfSearchResult, target"
End Sub
Sub KeyUnhook()
Application.OnKey "+~"
End Sub

Fordraiders

ASKER

ok, I just set a new target on active sheet and set the active cell so the intersecrt could work..does ok still testing...Thanks a bunch