Two userforms in one sheet

[umberto79]

Hello dear Users,

Can You help me a bit with my little problem.
My issue is - I would like to display UserForm1 when double-clicking in cell "c7" , but when double-clicking in another cell (for example) "c8"
it will show me Userform2.Here is my code for one Userform.

Many thanks in advance,

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim Rng1 As Range

'Assign the range to work with
Set Rng1 = Range("c7")
'Only work on assigned range
If Intersect(Target, Rng1) Is Nothing Then Exit Sub
'Cancel cell editing that would normally trigger when you double click
Cancel = True

'Call the userform
UserForm1.Show

End Sub

[paulked]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


Dim Rng1 As Range, Rng2 As Range


'Assign the ranges to work with
Set Rng1 = Range("c7")
Set Rng2 = Range("c8")


'Cancel cell editing that would normally trigger when you double click
Cancel = True


'Only work on assigned ranges and call the correct userform
If Not Intersect(Target, Rng1) Is Nothing Then UserForm1.Show: Exit Sub
If Not Intersect(Target, Rng2) Is Nothing Then UserForm2.Show: Exit Sub


End Sub

[SamT]

Cancel used in that manner means you can never Dble-Clck any cell expecting DbleClcking to work
Exit Sub used that way means the next line will never be executed


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Working with single cell targets means one doesn't need to use Intersect

'Only work on assigned ranges
If Target is Range("C7") Then ShowForm 1, Cancel
If Target is Range("C5") Then ShowForm 2, Cancel

'Alternate, using Intersect
If Not Intersect(Target, Range("C5:C7") is Nothing Then AltShowForm Target Cancel
End Sub


Private Sub ShowForm(FormNum As Long, ByRef Cancel as Boolean)
'ByRef effects Cancel in calling procedure
Cancel = True

If FormNum = 1 then Userform1.Show
If FormNum = 2 then Userform2.Show
End Sub


Private Sub AltShowForm(ByVal Target As Range, ByRef Cancel as Boolean)
If Target Is Range("C6") then Exit Sub 'Cancel is False by default

Cancel = True

'You can only Dbl-Clck a single cell at a time
If Target Is Range("C7") then Userform1.Show
If Target Is Range("C5") then Userform2.Show
End Sub

[snb]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
if not intersect(target,range("C7:C8"))is nothing then
cancel=true
if range.row=6 then
Userform1.show
else
Userform2.show
end if
end if
End Sub

But working with 1 userform, containing a Multipage is probably more elegant.

[paulked]

@SamT I agree with the cancel, but not
Exit Sub used that way means the next line will never be executed, try it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range("c7")) Is Nothing Then UserForm1.Show: Cancel = True: Exit Sub
If Not Intersect(Target, Range("c8")) Is Nothing Then UserForm2.Show: Cancel = True: Exit Sub

End Sub

Of course, Exit Sub is not needed.

Totally agree with snb, the 'Master of arrays'!

[SamT]

Tried it

Private Sub test()
Static x

If Not x Then x = True: Exit Sub
If x Then x = "3": Exit Sub

MsgBox x

End Sub
Exit sub prevents next line from executing. x is Static, so you can step thru it as often as you like, the MsgBox never shows

[paulked]

Sam


Exit sub prevents next line from executing.

WRONG! (sorry for shouting, but it's important!)

I just tried your snippet (which, by the way, I feel is a cheap attempt at proving me wrong - I asked you to try my code which works!) and on the 1st run it assigned x as true then exited the proc. - correct.

On the 2nd run it assigned x as 3 - it did NOT exit at the "If Not x Then x = True: Exit Sub" line... it DID execute the next line "If x Then x = "3": Exit Sub" and exited the proc. - correct.

But all you are doing in that proc. is toggling x between "True" and "3" and then exiting at whichever state it was not in, it will never get to the line "MsgBox x"... but it did pass the line with Exit Sub when 'Not x' - the proof!

This will get to the message box at the end and give the correct result (as my earlier code did):



Private Sub test()
Dim x&, sOops$
x = 3: sOops = "Sam is wrong!"
If x = 3 Then MsgBox "x is Three": x = 5: MsgBox "x is now " & x
If x = 2 Then MsgBox "x is Two": Exit Sub
If x = 4 Then MsgBox "x is Four": Exit Sub
MsgBox "x is still " & x & vbCr & sOops
End Sub

[Jan Karel Pieterse]

I guess all the discussion proves is that multiple statements on a single line can cause confusion as to how the code works. I find code using multiple statements on a single line hard to read.

[paulked]

Quite right Jan, I tend to avoid it too... except when I'm in a hurry :conscious.

[SamT]

Right, Paul.
Whatever you say, Paul.
I know nothing compared to you, Paul.

BTW, Paul, try comprehending the flow of execution of your own little "Proof" snippet before yelling at someone.


I am sorry, I did not realize you so resented someone critiquing your code. It won't happen again.

[paulked]

@Umberto Sorry for the bickering, hope you got your problem sorted :thumb

[Paul_Hossler]

What wouldn't something simple like this work?

I don't see the 'Cancel' as being germane since if this DC event fires, then I don't know if anything would fire



Option Explicit


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Cells(1, 1).Address(False, False) = "C7" Then
Load UserForm1
UserForm1.Show
Else
Load UserForm2
UserForm2.Show
End If
End Sub