Excel Target Intersect issue

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.
bfreescottAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ardhendu SarangiSr. Project ManagerCommented:
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
Ardhendu SarangiSr. Project ManagerCommented:
0
Patrick MatthewsCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bfreescottAuthor Commented:
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
folderolCommented:
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
folderolCommented:
Man, I keep forgetting that you have to end a comment line with a single ' to turn off the green font! :)
0
folderolCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zorvek (Kevin Jones)ConsultantCommented:
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
bfreescottAuthor Commented:
i love it when the solutions are just a few characters away... thanks folderol!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.