Excel: Random message when select given range

[Wolfen02]

I'm able to generate a random message for a given sheet, but am blanking out on narrowing the event down to a range within the sheet. Also the rnd function is returning two messages after the first time run. Tried to set the integer to zero to no avail. Following is the code I'm using. Any suggestions?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Integer
With Worksheets(2)
    r = (3) * Rnd + 1
        Cells(1, 1).Activate
            If r = 1 Then
                MsgBox "Thou shalt not type there!"
                r = 0
            End If
            If r = 2 Then
                MsgBox "You're typing privleges have been revoked."_ 
                & Chr(10) & "Please seek Help!"
                r = 0
            End If
            If r = 3 Then
                MsgBox "Oh no you don't!"
                r = 0
            End If
End With
End Sub

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[SkipVought]

Wolfen02,

Since you are using the Worksheet_SelectionChange event, there are several problems...

1) Why are you specifying

With Worksheets(2)

what would happen if the sheet inadvertently moved

Instead

With ActiveSheet

2) that statement has absolutely no effect on your code.

3) By using this statement

Cells(1, 1).Activate

you could be triggering the worksheet_selectionChange event AGAIN.

Activating Cells(1,1) adds absolutely no value.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Integer
    r = (3) * Rnd + 1
    Select Case r
        Case 1: MsgBox "Thou shalt not type there!"
        Case 2: MsgBox "You're typing privleges have been revoked." _
        & Chr(10) & "Please seek Help!"
        Case 3: MsgBox "Oh no you don't!"
    End Select
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Have a look at the INTERSECT function to determine if 2 ranges intersect - either that or use row / column boundaries


If target.row > 3 and target.row < 15 and target.column > 2 and target.column < 4 then
'Do stuff
Else
exit sub
end if

will only "Do Stuff" if the selection is within C4:C14

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Oh, so if you wanted to gen the message based on COLUMN

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        Select Case .Column
            Case 1: MsgBox "Thou shalt not type there!"
            Case 2: MsgBox "You're typing privleges have been revoked." _
            & Chr(10) & "Please seek Help!"
            Case 3: MsgBox "Oh no you don't!"
        End Select
    End With
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Well I thought so Skip but it's a bit tricky to decipher what the actual question is so I just went for focussing on:

but am blanking out on narrowing the event down to a range within the sheet

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Wolfen02]

Skip,
Didn't think about the activate cells command causing a second occurance. New at this. Will try again with your suggested changes.

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[Wolfen02]

Skip & Geoff,

Sorry I wasn't clearer. I'm attempting to block users from entering information into a given range (D5,I19) on the sheet so as to not change formulas.

Between the two of you, I've got it working now. Thanks!!

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[SkipVought]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'"A1:C4" is the FORBIDDEN RANGE
    Set rng = Application.Intersect(Target, Range("A1:C4"))
    If Not rng Is Nothing Then
        MsgBox "Thou shalt not type there!"
        [a5].Select
    End If
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Loomah]

Have I missed something here? Why not just protect the sheet?
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SkipVought]

Yes, but I think that he wants to provide his own message.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884