Just noting... your (new) posted code shows you checking for Q7=0 whereas
your original posting showed you wanted to block access if Q7 = 1 (implying
access to those cells was okay to do when Q7 was a different value from
either the 0 or 1, whichever you actually meant). I would point out that,
when running as event code, and assuming access was alright under certain
circumstances, Mike's suggestion leaves some situation not completely
covered. I'll await your clarification for the above (and what follows)
before delineating the problems as I see them.

Now, the problem you are experiencing is due to the fact that you moved
suggested event code (which must be placed in the worksheet's module) to a
general module. Two points about having done that... one, the code will not
execute automatically from the general module (event code must be in a
worksheet module and be in event produces, not macro Subs, in order to
function automatically); and two, the Target object your code references
only exists within (certain) event procedures in a worksheet module... VB
has no idea what Target is if you call it from a general module.

I'm not sure I understand why you think you have to move the code to a
general module... the worksheet module is more than capable of handling lots
of code (the same amount as a general module by the way). With that said,
you might not actually need 80 individual sections of code depending on how
similar the 80 individual sections of code will be. Can you give us some
more detail the 80 individual sections you are envisioning (like the ranges
they apply to, assuming the rest of the code is the same)?

--
Rick (MVP - Excel)



"ordnance1" wrote in message
...
Thanks that worked great!

Below is my final code. My only problem now is that since there will be
over 80 of these statements I would like to move this out of the
SheetSelectionChange so I added the following line:

Module3.BlankDays

but I get an Object Required error. What have I missed?



Sub BlankDays()

If Range("Q7") = 0 Then
If Not Intersect(Target, Range("B5:C5")) Is Nothing Then
MsgBox "You have selected a day that is not available for vacation.
Please reselect."
Range("A3").Select
End
End If
End If

End Sub




"Mike H" wrote in message
...
Hi,

So is there any way make the
Range("B5:C5") unselectable?

No but you can stop them staying there.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B5:C5")) Is Nothing Then
MsgBox "Hey you out of there!!!"
Target.Offset(1).Select
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ordnance1" wrote:

I want to run the code below to prevent a range of cells from being
selected
if the Range("Q7") = 1. I have all cells on the worksheet locked but
the
user must be able to click on the locked cells to trigger a userform so
I
have to check Select Locked Cells. So is there any way make the
Range("B5:C5") unselectable?



If Range("Q7") = 1 Then
Range("B5:C5").Locked = True
End If

.