VBA De-select

[Katy Jordan]

Hi, the below retains the selection in worksheet array, how can i get the code to de-select

i tried adding .Range("A5").Select, but the selection is still in place

 With Worksheets(WSary(a)).Range("A6:M" & NR & ",Q6:R" & NR)
        .Borders.LineStyle = xlNone
        .Borders.LineStyle = xlContinuous
        .Interior.ColorIndex = 0
        .Borders.Weight = xlHairline
       [COLOR=Red] .Range("A5").Select[/COLOR]
       End With

 

[Katy Jordan]

It works!!! I forgot to put Dim SelectionsCol As Collection at the very top of the code, when i run your code the code selects the last sheet, how can this be amended so the code selects "Sheet("task")

I'd suggest to do saving - restoring of each sheet selection in the separate subrotines.
Just call SaveSelections ActiveWorkbook before your code and call RestoreSelections at the end.

The code:

Dim SelectionsCol As Collection

Sub Test3()
  
  ' Save selection of each sheet
  Application.ScreenUpdating = False
  SaveSelections ActiveWorkbook
  
  ' Put your code here, below is just for testing
  For Each Sh In Worksheets
    Sh.Activate
    Selection.Offset(10, 10).Select
  Next
  
  ' Restore each sheet selection
  RestoreSelections
  Application.ScreenUpdating = True

End Sub

Private Sub SaveSelections(Wb As Workbook)
  Dim Sh As Worksheet, ScUpd
  Set SelectionsCol = New Collection
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    For Each Sh In Wb.Worksheets
      Sh.Activate
      SelectionsCol.Add Selection
    Next
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
End Sub

Private Sub RestoreSelections()
  Dim x, ScUpd
  On Error Resume Next
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    For Each x In SelectionsCol
      x.Parent.Activate
      x.Select
    Next
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
  Set SelectionsCol = Nothing
End Sub

 

[Katy Jordan]

It works!!! I forgot to put Dim SelectionsCol As Collection at the very top of the code, when i run your code the code selects the last sheet, how can this be amended so the code selects "Sheet("task")

I'd suggest to do saving - restoring of each sheet selection in the separate subrotines.
Just call SaveSelections ActiveWorkbook before your code and call RestoreSelections at the end.

The code:

Dim SelectionsCol As Collection

Sub Test3()
  
  ' Save selection of each sheet
  Application.ScreenUpdating = False
  SaveSelections ActiveWorkbook
  
  ' Put your code here, below is just for testing
  For Each Sh In Worksheets
    Sh.Activate
    Selection.Offset(10, 10).Select
  Next
  
  ' Restore each sheet selection
  RestoreSelections
  Application.ScreenUpdating = True

End Sub

Private Sub SaveSelections(Wb As Workbook)
  Dim Sh As Worksheet, ScUpd
  Set SelectionsCol = New Collection
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    For Each Sh In Wb.Worksheets
      Sh.Activate
      SelectionsCol.Add Selection
    Next
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
End Sub

Private Sub RestoreSelections()
  Dim x, ScUpd
  On Error Resume Next
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    For Each x In SelectionsCol
      x.Parent.Activate
      x.Select
    Next
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
  Set SelectionsCol = Nothing
End Sub

 

[ZVI]

The updated version to restore active sheet selecting as well

Dim SelectionsCol As Collection

Sub Test4()
  
  ' Save selection of each sheet
  SaveSelections ActiveWorkbook
  
  ' Put your code here, below is just for testing
  With ActiveSheet
    For Each Sh In Worksheets
      Sh.Activate
      Selection.Offset(10, 10).Select
    Next
    .Activate
  End With
  
  ' Restore each sheet selection
  RestoreSelections
  
End Sub

Private Sub SaveSelections(Optional Wb As Workbook)
  Dim Sh As Worksheet, ScUpd
  If Wb Is Nothing Then Set Wb = ActiveWorkbook
  Set SelectionsCol = New Collection
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    With Wb.ActiveSheet
      For Each Sh In Wb.Worksheets
        Sh.Activate
        SelectionsCol.Add Selection
      Next
      .Activate
    End With
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
End Sub

Please on answering choose [Post Replay] button (left side) instead of the

button if quotation is not required.

 

[ZVI]

It works!!! I forgot to put Dim SelectionsCol As Collection at the very top of the code, when i run your code the code selects the last sheet, how can this be amended so the code selects "Sheet("task")

It's nice to hear that you've solved issue!

Use this updated code:

Dim SelectionsCol As Collection

Sub Test4()
  
  ' Save selection of each sheet
  SaveSelections ActiveWorkbook
  
  ' Put your code here, below is just for testing
  With ActiveSheet
    For Each Sh In Worksheets
      Sh.Activate
      Selection.Offset(10, 10).Select
    Next
    .Activate
  End With
  
  ' Restore each sheet selection
  RestoreSelections
  
End Sub

Private Sub SaveSelections(Optional Wb As Workbook)
  Dim Sh As Worksheet, ScUpd
  If Wb Is Nothing Then Set Wb = ActiveWorkbook
  Set SelectionsCol = New Collection
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    With Wb.ActiveSheet
      For Each Sh In Wb.Worksheets
        Sh.Activate
        SelectionsCol.Add Selection
      Next
      .Activate
    End With
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
End Sub

Private Sub RestoreSelections()
  Dim x, ScUpd
  On Error Resume Next
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    With SelectionsCol.Item(1).Parent.Parent.ActiveSheet
      For Each x In SelectionsCol
        x.Parent.Activate
        x.Select
      Next
      .Activate
    End With
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
  Set SelectionsCol = Nothing
End Sub

If required then add Sheet("task").Activate after RestoreSelections

 

[Katy Jordan]

Thanks Vlad, this solved it.

Whilst on this topic, can i put a named range inside the array rather than me hardcode sheet name, it will make it easier for the user to add new worksheets.

WSary = Array("Sheet1", "Sheet2")

 

[ZVI]

...can i put a named range inside the array rather than me hardcode sheet name...

Not sure that this makes sense but you can put in array the names of the ranges instead of the sheet names.
If the named range MyRangeName is defined then you can refer to it via Range("MyRangeName")

 

[Norie]

Katy

I was just curious but I thought this thread was about deselecting a range not selecting/reselecting/restornig selections?

Did I miss something?

PS It's highly likely I have missed something - not easy to follow code on a mobile with a 2 inch screen.

 

[Katy Jordan]

Not sure that this makes sense but you can put in array the names of the ranges instead of the sheet names.
If the named range MyRangeName is defined then you can refer to it via Range("MyRangeName")

So do you mean like this

At present
WSary = Array("Sheet1", "Sheet2")

Change to
WSary = Array(Range("MyRangeName"))

 

[ZVI]

So do you mean like this

At present
WSary = Array("Sheet1", "Sheet2")

Change to
WSary = Array(Range("MyRangeName"))

It's correct, can also be like this:

  'At present
  WSary = Array("Sheet1", "Sheet2")
  ' Reference to the range
  Worksheets(WSary(0)).Range("Address")...

  'Change to
  WSary = Array("MyRangeName1", "MyRangeName2")
  ' Reference to the named range
  Range(WSary(0))...

But I don't find much benefit from this