Sure
Just a little background. I am creating a vacation calendar for a workgroup
of about 185 people in 3 different workgroups. Since the number of vacation
days available is a percentage of the number of employees in each workgroup
(each workgroup having its own quota). Just to add some complication to this
the number of people allowed off on Saturdays and Sundays is different from
the rest of the week. I want to build in the ability to expand (never know
if the economy will improve) or constrict if (my have some layoffs in July).
So to deal with Saturdays and Sundays where the number allowed off is
different than weekdays (and Saturdays and Sundays are not the same number).
For the sake of speed in populating the calendar we want the ability to
select the whole week (by selecting the first cell and dragging across to
the last cell) and have the userform place the selected name in the each
cell. All of that is working but I wanted to prevent someone fro selecting a
Saturday or Sunday that is not actually available.
So I envision having to do this for each Saturday and Sunday. The = # will
go from 0 to 18 The Range # ( "Q7" ) will be different for each day.
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
If Range("Q7") = 1 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
If Range("Q7") = 3 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
"Rick Rothstein" wrote in message
...
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
.