Displaying multiple forms

[DaveRadford]

Afternoon,

This is probably simple but i have tried various syntax's and it still seems to complain.

I want to display multiple forms for example, if the 1st range is selected, i want to show the 1st form if the second range is selected then ill want to show the second form, making way for the 'is nothing' of course. In time im sure there will be a 3rd form but that shouldnt be a problem once i have the correct syntax.

Thanks,


If Intersect(Target, Range("$B$11:$B$45")) Is Nothing Then Exit Sub
UserForm1.Show vbModeless

If Intersect(Target, Range("$D$11:$D$45")) Is Nothing Then Exit Sub
WSFilter
CreateWSList
UserForm2.Show vbModeless

 

[Andrew Poulsom]

Try:

    If Not Intersect(Target, Range("B11:B45")) Is Nothing Then
        UserForm1.Show vbModeless
    ElseIf Not Intersect(Target, Range("D11:D45")) Is Nothing Then
        WSFilter
        CreateWSList
        UserForm2.Show vbModeless
    End If

 

[DaveRadford]

Thanks it works a great, do you happen to know why it brings up the second form whenever you click on a locked cell?

Thanks,

 

[Andrew Poulsom]

Where is that code (please post the entire procedure)? Are the locked cells in the range D11:D45?

 

[DaveRadford]

The code is:

If Not Intersect(Target, Range("B11:B45")) Is Nothing Then
UserForm1.Show vbModeless
ElseIf Not Intersect(Target, Range("D11:D45")) Is Nothing Then
WSFilter
CreateWSList
UserForm2.Show vbModeless
End If

the locked cells arent within the range specified in the target range as the locked cells are sued to display values not cells that are for user input.

For some reason Form2 displays whenever a locked cell is clicked.

Hope this helps.

 

[Andrew Poulsom]

You still haven't posted the entire procedure.

 

[DaveRadford]

In the spreadsheet cells are either have locked ticked or not.


and for example:

ActiveSheet.EnableSelection = xlUnlockedCells

Range("B11:B46").Select
Selection.Locked = False

this will determine when the spreadsheet is opened what the users can and cant click in to.

for some reason if anyone double clicks a cell that is locked and cant click in to it displays the second form.

There really isnt much in the way of code for locking the cells, and thats all the code for loading the forms.

I just assumed it may have had something to do with the nothing statement :/

Thanks,

 

[Andrew Poulsom]

Why don't you post the entire procedure that contains my code? I don't think it's anything to do with the Nothing statement, but I can't be sure until I see the entire procedure.

 

[RoryA]

This:

If Not Intersect(Target, Range("B11:B45")) Is Nothing Then
UserForm1.Show vbModeless
ElseIf Not Intersect(Target, Range("D11:D45")) Is Nothing Then
WSFilter
CreateWSList
UserForm2.Show vbModeless
End If

is not an entire procedure, which is why Andrew is asking to see the whole thing. Looking at it, I assume it's an event procedure and at a guess, I'd say the SelectionChange procedure. If so, then your attempting to select a locked cell, when not allowed to do so, is probably causing the selection to jump back to the unlocked region, somewhere in the region you are checking, hence triggering your code again.

 

[DaveRadford]

Its quite allot of Code,

Do you want me to email it or paste it in here?

Thanks,