Solved

execute routine only if active cell in a certain column

Posted on 2011-02-22
10
218 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
  • 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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 41

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 74
modify formula same cell multiple times 15 28
Best Excel  formula for  this scenario 2 33
And OR formula 5 20
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

813 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

9 Experts available now in Live!

Get 1:1 Help Now