Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Part 2 of Worksheet_SelectionChange not working

Status
Not open for further replies.
Randy11

Randy11

Technical User
Oct 4, 2002
175
CA
Can not get the 2nd part of the code below working. The code is intended to do two separate operations, 1st is prevent mutiple cell deletion, 2nd part is to jump to a specific cell when a certain cell is selected. This is to help improve navigation of the worksheet. So do 1st, and then do 2nd. Assistance appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'The following

With Target

Dim rng As Range

If Not Intersect(Target, Range("A29:a58")) Is Nothing Then
If Selection.Cells.Count > 1 Then
Set rng = Selection
If Application.WorksheetFunction.CountA(rng) >= 1 Then
MsgBox "Cannot delete multiple cells - cancelling"
' Range("A29").Select
End If
End If
End If

Set rng = Nothing

End With

With Target

If Not Intersect(Target, Range("b29:b58")) Is Nothing Then
If Selection.Cells.Count > 1 Then
Set rng = Selection
If Application.WorksheetFunction.CountA(rng) >= 1 Then
MsgBox "Cannot delete multiple cells - cancelling"
' Range("A29").Select
End If
End If
End If

Set rng = Nothing

End With

'2nd Part of Code
If Target.Row = 17 And Target.Column = 2 Then
Range("b18").Select
End If

If Target.Row = 23 And Target.Column = 2 Then
Range("a29").Select
End If

End Sub
 
Sort by date Sort by votes


hi,
Code: 
'2nd Part of Code
    If Target.Column = 2 Then
        Application.EnableEvents = False
        Select Case Target.Row
            Case 17: Range("b18").Select
            Case 23: Range("a29").Select
        End Select
        Application.EnableEvents = True
    End If

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Does this not do the same thing?
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        
        If Not Intersect(Target, Range("A29:B58")) Is Nothing Then
            If .Cells.Count > 1 Then
                If Application.WorksheetFunction.CountA(.Cells) >= 1 Then
                    MsgBox "Cannot delete multiple cells - cancelling"
                Else
                
                '[b][highlight]??? what here?[/highlight][/b]
                
                End If
            Else
            
            '[b][highlight]??? what here?[/highlight][/b]
            
            End If
        End If
         
    '2nd Part of Code
        If .Column = 2 Then
            Application.EnableEvents = False
            Select Case .Row
                Case 17: Range("b18").Select
                Case 23: Range("a29").Select
            End Select
            Application.EnableEvents = True
        End If
    
    End With
End Sub

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Hi Skip There are more scenarios. Will place whole code below, tried to shorten but can see this was not helpful. Here is complete code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'The following

With Target

Dim rng As Range

If Not Intersect(Target, Range("A29:a58")) Is Nothing Then
If Selection.Cells.Count > 1 Then
Set rng = Selection
If Application.WorksheetFunction.CountA(rng) >= 1 Then
MsgBox "Cannot delete multiple cells - cancelling"
' Range("A29").Select
End If
End If
End If

Set rng = Nothing

End With

With Target

If Not Intersect(Target, Range("b29:b58")) Is Nothing Then
If Selection.Cells.Count > 1 Then
Set rng = Selection
If Application.WorksheetFunction.CountA(rng) >= 1 Then
MsgBox "Cannot delete multiple cells - cancelling"
' Range("A29").Select
End If
End If
End If

Set rng = Nothing

End With

With Target

If Not Intersect(Target, Range("d29:d38")) Is Nothing Then
If Selection.Cells.Count > 1 Then
Set rng = Selection
If Application.WorksheetFunction.CountA(rng) >= 1 Then
MsgBox "Cannot delete multiple cells - cancelling"
' Range("A29").Select
End If
End If
End If

Set rng = Nothing

End With

With Target

If Not Intersect(Target, Range("d41:d45")) Is Nothing Then
If Selection.Cells.Count > 1 Then
Set rng = Selection
If Application.WorksheetFunction.CountA(rng) >= 1 Then
MsgBox "Cannot delete multiple cells - cancelling"
' Range("A29").Select
End If
End If
End If

Set rng = Nothing

End With

'Part 2 (the 1st part of this code works great. This 2nd
'part does not work at all. The 2nd part of the code has
'nothing to do with the 1st part. Only that the code needs
'on run on the same worksheet.
If Target.Row = 17 And Target.Column = 2 Then
Range("b18").Select
End If

If Target.Row = 23 And Target.Column = 2 Then
Range("a29").Select
End If

If Target.Row = 54 And Target.Column = 2 Then
Range("d29").Select
End If

If Target.Row = 39 And Target.Column = 4 Then
Range("d41").Select
End If

If Target.Row = 40 And Target.Column = 4 Then
Range("d41").Select
End If

If Target.Row = 46 And Target.Column = 4 Then
Range("e29").Select
End If

If Target.Row = 21 And Target.Column = 5 Then
Range("e29").Select
End If

If Target.Row = 54 And Target.Column = 1 Then
Range("b29").Select
End If

If Target.Row = 22 And Target.Column = 5 Then
Range("e29").Select
End If

If Target.Row = 23 And Target.Column = 5 Then
Range("e29").Select
End If

End Sub
 
Upvote 0 Downvote


Well the principal is the same, regardless of the number of statements: turn off events, do your selection logic, turn on events.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote


Is there some LOGIC to these selections?

What if the Target selection is not one of these stated reference, it just stays there?

What is the PURPOSE?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Hi Skip Am still stuck with this. Have tried a number of different ways. Believe I will need to go back to the books.
Thanks....

 
Upvote 0 Downvote


Code: 
    '2nd Part of Code
        Application.EnableEvents = False    '[b][highlight]turn OFF events[/highlight][/b]
        Select Case .Column
            Case 1
                Select Case .Row
                    Case 54: Range("b29").Select
                End Select
            Case 2
                Select Case .Row
                    Case 17: Range("b18").Select
                    Case 23: Range("a29").Select
                    Case 54: Range("d29").Select
                End Select
            Case 4
                Select Case .Row
                    Case 39: Range("d41").Select
                    Case 40: Range("d41").Select
                    Case 46: Range("e29").Select
                End Select
            Case 5
                Select Case .Row
                    Case 21: Range("e29").Select
                    Case 22: Range("e29").Select
                    Case 23: Range("e29").Select
                End Select
        End Select
        Application.EnableEvents = True     '[b][highlight]turn ON events[/highlight][/b]
    End With

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Compile Error "Invalid or Unqualified reference. .Column highlighted.

Select Case .Column
 
Upvote 0 Downvote


Of course, you need the With Target reference, as in the former code I posted.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Sorry Skip, not getting it. Must be Monday!
 
Upvote 0 Downvote
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    [b][highlight blue][white]With Target[/white][/highlight blue][/b]
        
        If Not Intersect(Target, Range("A29:B58")) Is Nothing Then
            If .Cells.Count > 1 Then
                If Application.WorksheetFunction.CountA(.Cells) >= 1 Then
                    MsgBox "Cannot delete multiple cells - cancelling"
                Else
                
                '??? what here?
                
                End If
            Else
            
            '??? what here?
            
            End If
        End If
    [b][highlight]     
    '2nd Part of Code
        Application.EnableEvents = False    'turn OFF events
        Select Case .Column
            Case 1
                Select Case .Row
                    Case 54: Range("b29").Select
                End Select
            Case 2
                Select Case .Row
                    Case 17: Range("b18").Select
                    Case 23: Range("a29").Select
                    Case 54: Range("d29").Select
                End Select
            Case 4
                Select Case .Row
                    Case 39: Range("d41").Select
                    Case 40: Range("d41").Select
                    Case 46: Range("e29").Select
                End Select
            Case 5
                Select Case .Row
                    Case 21: Range("e29").Select
                    Case 22: Range("e29").Select
                    Case 23: Range("e29").Select
                End Select
        End Select
        Application.EnableEvents = True     'turn ON events[/highlight][/b]
    [b][highlight blue][white]End With[/white][/highlight blue][/b]
End Sub


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Thank you for all of your assistance. Figured out what was causing the problem of this not working properly. Have code that automatically password protects the worksheets. The option of selecting locked cells was not choosen. Yikes, was definitely Monday.... Appreciate your expertise.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
860
strongm
strongm
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
936
morechocolate
morechocolate
Katto
  • Locked
  • Question
VBA permutation of arrays
2
Replies
22
Views
728
Katto
Katto
IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
343
Andrzejek
Andrzejek
thanhthanh12
  • Locked
  • Question
How compare 2 macros in vba word
Replies
3
Views
257
mintjulep
M

Part and Inventory Search

Sponsor

Back
Top