detecting range selection for copy

[samohtwerdna]

hello again,

I have a spreadsheet that contains Jobs in reservation status and Jobs that have actually come and are in house. The Jobs in reservation status get moved around frequently (not by me). When these jobs are moved the user needs to select a range from ($A$"" - to $O$"") and then do some copying and pasting. Every column after O contain formuli and are protected. Sometimes the user does not select the whole range and stops at $N ect which confusses the schedule.

Is there an easy way I can detect whether the range being selected contains the A-O columns and If not sends an error to the user - in VBA??

Thanks for the Help

[Jacob Hilderbrand]

You can use Intersect to check.

[vba]
If Intersect(Selection, Range("A:O")) Is Nothing Then
'A:O is not in the selection
Else
'A:O is in the selection
End If
[/vba]

You can use the same method to check if the selection contains any cells that you do not want selected.

[samohtwerdna]

Thanks for the help Jake - but I am still quite a novice at VBA so how do I know when the user has made a selection?

Should I put this code in the sheet its self - in the Workbook - or in its own module?... does it even matter?

I've been trying to put it into my own function that gets called when the user make a selection to copy.

[VBA]
Sub MyDetect()
Dim MyAlert As string

If Intersect(Selection, Range("A:O")) Is Nothing Then
'A:O is not in the selection
MyAlert = "You Don't have all the Cells you need!!"
MsgBox MyAlert
Else
'A:O is in the selection
End If


End Sub[/VBA]

What calls MyDetect?

[Bob Phillips]

Maybe on selection ?

[VBA]Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A:O"
Const MyAlert As String = "You Don't have all the Cells you need!!"

If Intersect(Selection, Range(WS_RANGE)) Is Nothing Then
MsgBox MyAlert
Else
'A:O is in the selection
End If

End Sub[/VBA]

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

[samohtwerdna]

I figured that it would be on the selection - but SelectionChange doesn't seem to want to work

I put xld's code into the code for "Sheet1" but still no effect when copying the wrong range?

Perhaps SelectionChange is not what I want??

[samohtwerdna]

What about RangeSelection ?
Could I use some thing like: if RangeSelection = ("A:O") then ??

[samohtwerdna]

OK so I can use the ActiveWindow.RangeSelection

Like :
[VBA]Private Sub Worksheet_SelectionChange(ByVal target As range)
Const WS_RANGE As String = "A:O"
Const MyAlert As String = "You Don't have all the Cells you need!!"

If ActiveWindow.RangeSelection.Address = WS_RANGE Then
MsgBox ActiveWindow.RangeSelection.Address
Else
MsgBox MyAlert
End If

End Sub
[/VBA]
But this is anoying because now any selection comes up with MyAlert
So I'm thinking of doing an ElseIF ""RangeSelection.Address > INCMPLT_RANGE - but that still will not be exactly what I want- and seems a little sloppy.

Any suggestions??

[Cyberdude]

Every column after O contain formuli and are protected.

"Formuli"?? Are you perhaps British?

[samohtwerdna]

Nope, not british

Anyway, I am not getting anywhere with this! SelectionChanged with intersect is not working for me - and neither is the ActiveWindow.RangeSelection.Address - (which can tell me the range address, but is always specific ($A$12:$O$12) so if I write an If ActiveWindow.RangeSelection.Address = ("A:O") it wont work)

I am hoping that the If Intersect(Selection, ("A:O")) is the correct code but I'm missing something somewhere else. Anybody have an idea what I might be missing?

I notice when debugging that "Selection" appears to be empty no matter who many cells I have in a selection window.