Applying restrictions around ActiveSheet.Rows(Target.Row).Select

[beam_me_ip]

Hi,

I'm sure (or at least hoping) this is an easy one, but here goes...

I have a worksheet where I need an entire row to be selected whenever the user clicks any cell for that row. This is achieved using:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Rows(Target.Row).Select
Target.Activate

End Sub

There are 2 issues with this:

1. I want this to be confined only to rows in the worksheet where this is data. For example rows 1-7 are blank, and row 8 the header so these should be ignored. Equally any rows after the last row of data (according to what's in column A - which will always have values) should also be ignored. The number of rows with data is variable. Can the code be amended in someway?

2. The worksheet in question also includes buttons assigned to 'sort' macros enabling the data to be organised according to a number of different criteria (first name, surname, DOB, etc). However the above procedure prevents these from working due to the requirement of selecting a range of cells to sort. Is there a way of "turning off" the procedure to enable the sort to take place after any of the buttons are clicked, and then it "turnt on" again afterwards?

Help would be very much appreciated!

Beam

 

[beam_me_ip]

Ok, I've sussed no.2 by disabling events before the sort and reenabling afterwards. Can anyone help with 1?

 

[p45cal]

1. try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static Blocked As Boolean
If Not Blocked Then
  Blocked = True
  Set activezone = Intersect(Rows("9:" & Rows.Count), Union(Columns(1).SpecialCells(xlCellTypeConstants), Columns(1).SpecialCells(xlCellTypeFormulas))).EntireRow
  If Not Intersect(activezone, Target) Is Nothing Then
    ActiveSheet.Rows(Target.Row).Select
    Target.Activate
  End If
  Blocked = False
End If
End Sub

No need to disable events.

2. There's probably no need to disable events for this either. Your sorting code should not need to select anything. Post the sorting code here and I/we will tweak it to avoid any selecting, which will (a) reduce screen flicker and (b) speed it up.

 

[beam_me_ip]

Thanks p45cal.

It doesn't like the following command:
Set activezone = Intersect(Rows("9:" & Rows.Count), Union(Columns(1).SpecialCells(xlCellTypeConstants), Columns(1).SpecialCells(xlCellTypeFormulas))).EntireRow

"Runtime error 1004. No cells were found". Any ideas?

Also, I need to accomodate in the code the fact that the sheet will by default be protected.

As for the sort code, it's just an extended version of a recorded sort routine. Example below.

Sub Sort_byName()

ActiveSheet.Unprotect

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Set rngMyRange = Range("A8" & ":F" & lastrow)
rngMyRange.Select

Selection.Sort Key1:=Range("D8"), Order1:=xlAscending, Key2:=Range("C8") _
, Order2:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

RowCount = Range("8:8", Range("A8").End(xlDown)).Rows.Count
For Each cell In Range("D8:D" & 8 + RowCount - 1)
If cell <> "" Then GoTo countdone
myBlanks = myBlanks + 1
Next

countdone:
If myBlanks > 0 Then
Range(Cells(8, "D"), Cells(8 + myBlanks - 1, "D")).EntireRow.Cut
Rows(8 + RowCount).Insert
End If

ActiveSheet.Protect

End Sub

 

[p45cal]

about the runtime error; it stems from "to be confined only to rows in the worksheet where this is data ... according to what's in column A" where I look for values derived from plain values in a cell and derived from formulae. I tested it with both! The following works whether there are both, neither or just one.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim aaa As Range, bbb As Range, aabb As Range
Static Blocked As Boolean
If Not Blocked Then
    Blocked = True
    On Error Resume Next
    Set aaa = Columns(1).SpecialCells(xlCellTypeConstants)
    Set bbb = Columns(1).SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If aaa Is Nothing And bbb Is Nothing Then
        Blocked = False
        Exit Sub
    End If
    If aaa Is Nothing Then
        Set aabb = bbb
    ElseIf bbb Is Nothing Then
        Set aabb = aaa
    Else
        Set aabb = Union(aaa, bbb)
    End If
    Set activezone = Intersect(Rows("9:" & Rows.Count), aabb).EntireRow
    If Not Intersect(activezone, Target) Is Nothing Then
        ActiveSheet.Rows(Target.Row).Select
        Target.Activate
    End If
    Blocked = False
End If
End Sub

(I may have made heavier weather of it than I need.)

re:"Also, I need to accomodate in the code the fact that the sheet will by default be protected."
It depends (a) on the type of protection; if it prevents the selection of cells by the user then yes something will be needed, and (b) what you might want the user to do that protection is stopping. What's going wrong?

I'll addres your sort routine in another message.

 

[p45cal]

For the sort routine without selecting anything test this:

Sub Sort_byName()
ActiveSheet.Protect UserInterfaceOnly:=True
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rngMyRange = Range("A8:F" & lastrow)
rngMyRange.Sort Key1:=Range("D8"), Order1:=xlAscending, Key2:=Range("C8"), Order2:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
RowCount = Range("8:8", Range("A8").End(xlDown)).Rows.Count
For Each Cell In Range("D8:D" & 8 + RowCount - 1)
    If Cell <> "" Then GoTo countdone
    myBlanks = myBlanks + 1
Next
countdone:
If myBlanks > 0 Then
    Range(Cells(8, "D"), Cells(8 + myBlanks - 1, "D")).EntireRow.Cut
    Rows(8 + RowCount).Insert
End If
End Sub