?
Solved

Excel Target Intersect issue

Posted on 2010-01-05
9
Medium Priority
?
451 Views
Last Modified: 2012-05-08
I need to be able to prevent users from selecting an entire column (using the column headers: A, B, C, etc.) because the cells in that column are affected by an Intersect funciton that is called in Worksheet_SelectionChange.  Selecting the entirecolumn affects all of the cells in that column which is an undesired behavior.
0
Comment
Question by:bfreescott
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 26185828
Can you lock the cells in the column and protect the sheet? That would leave the rest of the worksheet editable but would prevent the users from messing around with your columns.

To protect the sheet, go to Tools -> Protection -> Protect Sheet

- Ardhendu
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 26185849
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26185877
Hello bfreescott,

It would help if you could be more specific about what you need.  The following event sub finds the
intersection between the Target and UsedRange.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    Dim rng As Range
   
    Application.EnableEvents = False
   
    If Not Intersect(Target, Me.UsedRange) Is Nothing Then
        Set rng = Intersect(Target, Me.UsedRange)
    Else
        Set rng = Nothing
    End If
   
    If Not rng Is Nothing Then
        'put your code here, using rng instead of Target
    End If
   
    Application.EnableEvents = True
   
End Sub



Regards,

Patrick
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:bfreescott
ID: 26185950
Thanks guys!  Here is my proc which is what you were referring to matthewspatrick, but I have already determined the range that I want to be affected by the Change event. And I can't lock the cells in the column Pari123 b/c I need the users to be able to edit them.  The only thing I want to do is to prevent the selection of the EntireColumn because doing so affects cells outside of my range object.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
    Dim p As Integer
    Dim r As Range
 
    p = Cells(Rows.count, "B").End(xlUp).Row
    Set r = Range("A4:A" & p)
     
    If Not Intersect(Target, r) Is Nothing Then
        If Target.Cells(1, 1).Value = "a" Then
            Target.Cells(1, 1).Value = ""
        Else
            Target.Cells(1, 1).Value = "a"
        End If
    End If
'
End Sub
0
 
LVL 19

Expert Comment

by:folderol
ID: 26186025
The popular usage of intersect in the SelectionChange event is to test for one cell in a range.  If this is your intent, then this mod to Patrick's idea might be what you are looking for.

Your code will run only when one cell is selected, and that cell is in the designated column (column C in this example).

Tom.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C1:C" & UsedRange.Rows.Count)) Is Nothing And Target.Count = 1 Then
        ' your code here
    End If
    
End Sub

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 26186044
Man, I keep forgetting that you have to end a comment line with a single ' to turn off the green font! :)
0
 
LVL 19

Accepted Solution

by:
folderol earned 1000 total points
ID: 26186100
Sorry bfreescott,

I didn't go back and look at the PAQ.  

The Target.Rows.Count = 1 will prevent the selectionchange from firing if the entire column is selected.

Tom.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
''
    Dim p As Integer
    Dim r As Range
 
    p = Cells(Rows.count, "B").End(xlUp).Row
    Set r = Range("A4:A" & p)
     
    If Not Intersect(Target, r) Is Nothing and target.rows.count = 1 Then
        If Target.Cells(1, 1).Value = "a" Then
            Target.Cells(1, 1).Value = ""
        Else
            Target.Cells(1, 1).Value = "a"
        End If
    End If
''
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26186288
A different approach. This code looks at the top left cell of the selection and, if it is in column A and at or below row 4 and the cell to the right contains a value then "a" is placed in the top left selected cell. If the cell to the right does not contain a value then an empty string is placed in the top left selected cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With Target.Cells(1, 1)
        If .Row >= 4 And .Column = 1 Then
            If Len(.Offset(0, 1)) > 0 Then
                .Value = "a"
            Else
                .Value = vbNullString
            End If
        End If
    End With

End Sub

Kevin
0
 

Author Closing Comment

by:bfreescott
ID: 31673225
i love it when the solutions are just a few characters away... thanks folderol!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

807 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