Prev: Copy Picture problems
Next: FASTER USE OF THE REPLACE() FUNCTION !!!!!
From: Mike H on 27 Mar 2010 15:07
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" wrote:

> 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" <MikeH(a)discussions.microsoft.com> wrote in message
> news:1F6CAEB4-AF38-47B3-95C9-80CB61E10DEC(a)microsoft.com...
> > 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
> >>
> >> .
> >>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Copy Picture problems
Next: FASTER USE OF THE REPLACE() FUNCTION !!!!!