Solved

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

Posted on 2011-03-23
7
166 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:fordraiders
  • 4
  • 3
7 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35199764
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?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 35200019
becaue is they type something for a change in the cell I do not always want the code to execute...
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35200149
Then I would move the code to a separate routine, and use the SelectionChange event to hook the Shift+Enter key combo with Application.Onkey:
Sub KeyHook()
    Application.OnKey "+~", "macro_name"
End Sub
Sub KeyUnhook()
    Application.OnKey "+~"
End Sub

Open in new window


then in the worksheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 2 Then
        KeyHook
    Else
        KeyUnhook
    End If
End Sub

Open in new window


You will also want to use the Workbook_Activate and Deactivate events to hook/unhook if the cursor is in column B on the right sheet.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Author Comment

by:fordraiders
ID: 35200428
rorya, Could I also add if" sheet1" is not selected don't do this at all ?
Sheets("sheet1").Activate
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35200469
Yes - in the Workbook (as opposed to worksheet) events, you can use something like:
If activesheet.name = "Sheet1" Then

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 35200767
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
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 35201990
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now