Hi All,

I want to select a non-contiguous range but with the restriction that it can
contain only one cell from each row. Is it possible to unselect all other
cells in the row when I add a new cell of the same row to the range. The
problem is that Ctrl-Click doesn't trigger Selection_Change event to give me
opportunity to reformat my range on the fly!

Regards,
Stefi

Hi
This sub will let you deselect any cells you don't want (a selected
cell, when clicked, will deselect) - keep your finger on Ctrl as you
select/deselect. The sub calls a function which you can put into a
normal code module, but the sub itself has to go into the sheet module.
You can probably change it a bit to deselect all other selected cells
in the same row automatically (I wrote this a few years ago so don't
ask me how it works!).

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim NewString As String
Dim StringAddresses As Collection, AddressCollection As New Collection
Dim StringAddressesCount As Integer, TotalCount As Long
Dim i As Integer, Cell As Range 'counters
Dim TempRange As Range
Dim RangeString As String

'The address of the target is the OldSelectionRange Address with any
element of the new Target added
'We can parse this extended string
Set StringAddresses = Get_String_Items(Target.Address)
StringAddressesCount = StringAddresses.Count
On Error Resume Next 'disable error handling
For i = 1 To StringAddressesCount
Set TempRange = Range(StringAddresses(i))
For Each Cell In TempRange
Err.Clear 'as will use it
With Cell
'try to add the Cell address. If there, cell has been
clicked already, so Unclick it
AddressCollection.Add .Address, .Address
If Err.Number < 0 Then 'cell address is already
there
AddressCollection.Remove .Address
End If
End With
Next Cell
Next i
On Error GoTo 0
'now have a collection of individual cell addresses
'any cell clicked an even number of times is not selected. Check
collection is not empty
'TempRange was the last Range clicked
TotalCount = AddressCollection.Count
If TotalCount = 0 Then
RangeString = TempRange.Address
Else
For i = 1 To TotalCount
RangeString = RangeString & "," &
CStr(AddressCollection(i))
Next i
'will have a leading comma so
RangeString = Right(RangeString, Len(RangeString) - 1)
End If
Range(RangeString).Select
End Sub


'Called by SelectionChange event of Register Worksheet to parse
'a comma separated string into pieces
Public Function Get_String_Items(InputString As String) As Collection
Dim TempString As String, ItemString As String
Dim PlaceComma As Long
Dim StringItems As New Collection


If Trim(InputString) < "" Then
TempString = InputString
PlaceComma = InStr(TempString, ",")
If PlaceComma = 0 Then
StringItems.Add TempString
Set Get_String_Items = StringItems
Else
TempString = TempString & ","
'While a comma exists in the string
Do While PlaceComma 0
'Get rid of any leading comma's
Do While PlaceComma = 1
TempString = Trim(Right(TempString,
Len(TempString) - 1))
PlaceComma = InStr(TempString, ",")
Loop
'If that leaves an empty string, leave the loop
If PlaceComma = 0 Then
Set Get_String_Items = StringItems 'which is
empty
Exit Do
End If
'otherwise
ItemString = Trim(Left(TempString, PlaceComma -
1))
StringItems.Add ItemString
TempString = Trim(Right(TempString,
Len(TempString) - PlaceComma))
PlaceComma = InStr(TempString, ",")
Loop
Set Get_String_Items = StringItems
End If
End If
End Function

regards
Paul


Stefi wrote:
Hi All,

I want to select a non-contiguous range but with the restriction that it can
contain only one cell from each row. Is it possible to unselect all other
cells in the row when I add a new cell of the same row to the range. The
problem is that Ctrl-Click doesn't trigger Selection_Change event to give me
opportunity to reformat my range on the fly!

Regards,
Stefi

Thanks Paul, I'll try it!
Stefi


€� ezt Ã*rta:

Hi
This sub will let you deselect any cells you don't want (a selected
cell, when clicked, will deselect) - keep your finger on Ctrl as you
select/deselect. The sub calls a function which you can put into a
normal code module, but the sub itself has to go into the sheet module.
You can probably change it a bit to deselect all other selected cells
in the same row automatically (I wrote this a few years ago so don't
ask me how it works!).

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim NewString As String
Dim StringAddresses As Collection, AddressCollection As New Collection
Dim StringAddressesCount As Integer, TotalCount As Long
Dim i As Integer, Cell As Range 'counters
Dim TempRange As Range
Dim RangeString As String

'The address of the target is the OldSelectionRange Address with any
element of the new Target added
'We can parse this extended string
Set StringAddresses = Get_String_Items(Target.Address)
StringAddressesCount = StringAddresses.Count
On Error Resume Next 'disable error handling
For i = 1 To StringAddressesCount
Set TempRange = Range(StringAddresses(i))
For Each Cell In TempRange
Err.Clear 'as will use it
With Cell
'try to add the Cell address. If there, cell has been
clicked already, so Unclick it
AddressCollection.Add .Address, .Address
If Err.Number < 0 Then 'cell address is already
there
AddressCollection.Remove .Address
End If
End With
Next Cell
Next i
On Error GoTo 0
'now have a collection of individual cell addresses
'any cell clicked an even number of times is not selected. Check
collection is not empty
'TempRange was the last Range clicked
TotalCount = AddressCollection.Count
If TotalCount = 0 Then
RangeString = TempRange.Address
Else
For i = 1 To TotalCount
RangeString = RangeString & "," &
CStr(AddressCollection(i))
Next i
'will have a leading comma so
RangeString = Right(RangeString, Len(RangeString) - 1)
End If
Range(RangeString).Select
End Sub


'Called by SelectionChange event of Register Worksheet to parse
'a comma separated string into pieces
Public Function Get_String_Items(InputString As String) As Collection
Dim TempString As String, ItemString As String
Dim PlaceComma As Long
Dim StringItems As New Collection


If Trim(InputString) < "" Then
TempString = InputString
PlaceComma = InStr(TempString, ",")
If PlaceComma = 0 Then
StringItems.Add TempString
Set Get_String_Items = StringItems
Else
TempString = TempString & ","
'While a comma exists in the string
Do While PlaceComma 0
'Get rid of any leading comma's
Do While PlaceComma = 1
TempString = Trim(Right(TempString,
Len(TempString) - 1))
PlaceComma = InStr(TempString, ",")
Loop
'If that leaves an empty string, leave the loop
If PlaceComma = 0 Then
Set Get_String_Items = StringItems 'which is
empty
Exit Do
End If
'otherwise
ItemString = Trim(Left(TempString, PlaceComma -
1))
StringItems.Add ItemString
TempString = Trim(Right(TempString,
Len(TempString) - PlaceComma))
PlaceComma = InStr(TempString, ",")
Loop
Set Get_String_Items = StringItems
End If
End If
End Function

regards
Paul


Stefi wrote:
Hi All,

I want to select a non-contiguous range but with the restriction that it can
contain only one cell from each row. Is it possible to unselect all other
cells in the row when I add a new cell of the same row to the range. The
problem is that Ctrl-Click doesn't trigger Selection_Change event to give me
opportunity to reformat my range on the fly!

Regards,
Stefi