Solved

execute routine only if active cell in a certain column

Posted on 2011-02-22
10
216 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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.

758 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