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 .

[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

[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