Solved

execute routine only if active cell in a certain column

Posted on 2011-02-22
10
221 Views
Last Modified: 2012-05-11
excel 2003 vba

I have a routine in the
Private Sub Worksheet_Change(ByVal Target As Range)   event

However I only wan the code below to execute if any cell in Column O is selected. ?


Thanks
fordraiders
' looking for manual entry  01-06-2006 column O only
   Dim Result2 As String
   Set targ2 = Intersect(Target, [O:O])
   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)
             If Result2 <> "" Then
               MsgBox Result2
             End If
           End If
       End If
   End If
'=================================================

Open in new window

0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 5

Expert Comment

by:roger_karam
ID: 34955643
Hello, try this:

' looking for manual entry  01-06-2006 column O only
   dim target 
   set target = selection
   Dim Result2 As String
   Set targ2 = Intersect(Target, Range("O:O"))

   If targ2 Is Nothing Then exitsub
   '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)
             If Result2 <> "" Then
               MsgBox Result2
             End If
           End If
       End If
   End If
'=================================================

Open in new window

0
 
LVL 5

Assisted Solution

by:roger_karam
roger_karam earned 100 total points
ID: 34955652
sorry, typo there and forgot to remove one of the end if's - RK
' looking for manual entry  01-06-2006 column O only
   dim target 
   set target = selection
   Dim Result2 As String
   Set targ2 = Intersect(Target, Range("O:O"))

   If targ2 Is Nothing Then exit sub
   '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)
             If Result2 <> "" Then
               MsgBox Result2
             End If
           End If
       End If
'=================================================

Open in new window

0
 
LVL 24

Accepted Solution

by:
broomee9 earned 400 total points
ID: 34955665
Wrap it with this:

If ActiveCell.Column = 15 Then 'Column O
 'Is in column O
Else
 'Is not in column O
End if
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 5

Expert Comment

by:roger_karam
ID: 34955706
I like broomee's better then my own, but i'd still use exit sub so as not to have another if/else.
ie:

If ActiveCell.Column <>15 then exit sub

:-)
0
 
LVL 3

Author Comment

by:fordraiders
ID: 34955885

Target is passed as a byval...already correct ?

Private Sub Worksheet_Change(ByVal target As Range)


dim target   <--- dont need ?

0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34955963
you can still use broomee's answer

if target.column <> 15 then exit sub

However, you also might want to be carefull if the user is selection multiple columns (ex. N through P), in which case you might want to throw in there:

If target.Columns.Count > 1 Then Exit Sub

or

if target.columns.count > 1 then
msgbox ("More than one column selected")
exit sub
end if
0
 
LVL 24

Expert Comment

by:broomee9
ID: 34956057
Yes, you don't need to declare another target variable, unless you need it for something else.

You can do what I suggested by using Activecell.Column = 15 or by using Target.Column = 15.

I agree with Roger, you should also check for multiple cells being selected.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34956502
Target and ActiveCell are not the same.  Target.Address will give you the address of the last change in a Worksheet_Change event, whereas ActiveCell.Address will give you the address of the next selected cell, AFTER the Target was changed.  So, if you arrow down after changing a cell, that cell BELOW target would be the ActiveCell.

Dave
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 34956539
Thanks to all
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34956660
Thanks for the assist credit!

-RK
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

615 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