Solved

execute routine only if active cell in a certain column

Posted on 2011-02-22
10
220 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

759 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