Prevent clicking on a cell

[ordnance1]

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

 

[Mike H]

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.

 

[Rick Rothstein]

You could try this concept where I control the ability to select those cells
via worksheet event procedures (so if you are already using these event
procedures, then you will have to merge my code into them). Put the
following code in the code window for the worksheet that you want to have
this functionality. Note that if Q7 is not currently 1 and either B5 or C5
is the ActiveCell, either whenever you activate the sheet or if you change
Q7 to 1 via code, then my code will make A2 the active cell. If you want a
different cell to become active under either of these two conditions, then
change my Range("A2") to reflect the cell you want to use in both the
Activate and the Change event procedures.

'*************** START OF CODE ***************
Dim LastCell As String

Private Sub Worksheet_Activate()
If Range("Q7").Value = 1 And Not Intersect(ActiveCell, _
Range("B5:C5")) Is Nothing Then Range("A2").Select
LastCell = ActiveCell.Address
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "Q7" And Target.Value = 1 Then
If Not Intersect(ActiveCell, Range("B5:C5")) Is Nothing Then
Range("A2").Select
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B5:C5")) Is Nothing Then
If Range("Q7").Value = 1 Then Range(LastCell).Select
End If
LastCell = ActiveCell.Address
End Sub
'*************** END OF CODE ***************

 

[ordnance1]

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

 

[Rick Rothstein]

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)?

 

[Mike H]

Hi,

I think your saying you now have this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
BlankDays
End Sub

Which call the sub outine Blankdays when the user changes selection. If so
TARGET will be unassigned in the subroutine and we can get around that like
this

Change your worksheet code to this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
rCell = Target.Address
BlankDays
End Sub

And declare rCell as Public and use this in your subroutine


Public rCell
Sub BlankDays()
If Range("Q7") = 0 Then
If Not Intersect(Range(rCell), ActiveSheet.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

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

 

[ordnance1]

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

 

[ordnance1]

Rick I have sent this twice but it does not seem to be displaying so I will
try again.

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("A").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