Define target to a variable with CTRL key and mouse

[thomasks]

I need to assign a range to a variable when the user selects rows in excel. Even if he selects multiple selections by holding down CTRL and selecting rows with the mouse.
Can anyone show me how this is accomplished?

 

[PHV]

Have you tried to play with the Target parameter of the SelectionChange event procedure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[thomasks]

What I am doing is, putting the sheet name in the first column of the sheet that was used to select the rows, after I have copied the selected data to a newly created sheet. However this code only works if the user selects a contiguous set of rows. If he/she holds down the control key and selects different rows on the sheet then the code chokes and the sheet name is not copied to the first column of the selected rows.
Here is the code that I am using:

'Put the sheet name in row 1 of the new sheet
ActiveSheet.Range("b3").Select
ActiveSheet.Cells(1, 2) = sSheetName
    'Select the worksheet that the rows were selected from
    'then define the rows selected by parsing the string that stores the address
    'of the selected range
ActiveWorkbook.Sheets("formatted_data").Select
    'Retrieve the selected range from excel
sRange = ActiveWindow.RangeSelection.Address
    'This code locates the colon that seperates the range values
iColonLocate = InStr(1, sRange, ":", vbTextCompare)
    'This code tells us the length of the entire string holding the range values
iStringLength = Len(sRange)
    'Now we store the first row number after stripping out the dollar signs and the colon
sRowsFirst = Mid(sRange, 2, iColonLocate - 2)
    'Store the last row number after stripping out the dollar signs and the colon
sRowsLast = Mid(sRange, iColonLocate + 2, iColonLocate + 2 + iStringLength)
    'Loop through the rows placing the sheet name in column "A"
For R = sRowsFirst To sRowsLast
ActiveSheet.Cells(R, 1).Value = sSheetName
Next R
    'Return to the new worksheet that we created
ActiveWorkbook.Sheets(sSheetName).Select

 

[PHV]

You may try to play with something like this:
For Each sRange In Split(ActiveWindow.RangeSelection.Address, ",")
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886