Solved

execute routine only if active cell in a certain column

Posted on 2011-02-22
10
219 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

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…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 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