Calling an Event Procedure from another form... 1

[FaneDuru]

I use two User Forms in Excel and I pass some information from textBoxes of Form1 in textBoxes of Form2. I can select a specific item in a listbox (using ListIndex) but I need to call the listBox_DblClick event.
I tried the code:

Frms!Form2.ListBox1_DblClick

but without success (I made the procedure Public).
I can call a Public procedure in this Form2

Public Sub DoubleClickRemote()
   me.ListBox1_DblClick
End Sub

but no result, also. I mean the Public procedure is triggered but the code inside is wrong.
Can anybody help?

Thanks in advance!

 

[SkipVought]

hi,

but I need to call the listBox_DblClick event.

A better approch is to put the code that you run in the event in it's own procedure in a MODULE.

Then you can call that procedure eaily from any other event or procedure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FaneDuru]

Thanks Skip!

So, I did it already. But in the Form module. There are some Private variables which can be used better from that location. But, I was wandering if it is possible to call the event procedure. I found some forums where some guys said that worked for them...

 

[SkipVought]

So, I did it already. But in the Form module.

a MODULE! Not a FORM module.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

You can declare userform in standard module:

public frm1 as userform1

and instantiate the form

set frm1=new userform1
frm1.show

Now it is possible to call public procedures (with any proper names) in frm1 as its methods.

combo

 

[FaneDuru]

Hi Skip,
I am not sure I understood what you mean: Is it wrong to say 'Form Module', or it will work only in a MODULE?

Thanks Combo,
Now my procedure to be called is a public one in the called Form Code Module and it works. I tried your suggestion for directly calling the method but I failed. I used the next code:
In a standard Module:

 Public Frm1 as MyFormToBeCalled

and I instantiated and try to call method in this way:

 Set Frm1 = New MyFormToBeCalled: Frm1.Show
  Frm1.ListBox1_DblClick
  'or
  Forms!Frm1.ListBox1_DblClick

but without success...
Which to be the code to call the double click method/event?

 

[SkipVought]

only in a MODULE

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FaneDuru]

Ok. Understood...

Thanks!

 

[combo]

Going back to my code...
Firs of all, the declaration of Frm1 is just to have a clear reference to the instantiated userform, without implicit vba action (hidden [tt]Dim MyFormToBeCalled as New MyFormToBeCalled[/tt] declaration when such userform is in vba project). It is not necessary, but in this case when there are no object (userform instance), any reference to userform class will create an object.

There are two problems when calling external event procedure as method:
1. when you display modal userform, the code is passed here, starts to continue after closing it. So it is possible to call external code only by active userform in case of two userforms or make an userform modeless and let code in module run),
2. the DblClick event requires object argument (Cancel is of MSForms.ReturnBoolean type with one default Value property). It's a problem with creation of such object, however one can pass 'Nothing' if the event procedure does not use it.

For instance this works:
Userforms (both modal):
1. UserForm1 with ListBox1 and CommandButton1,
2. UserForm2 with CommandButton1.

Private Sub CommandButton1_Click()
UserForm2.Show
End Sub

Public Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox "dblclick"
End Sub

Private Sub UserForm_Initialize()
MsgBox "initialise"
End Sub

Private Sub CommandButton1_Click()
UserForm1.ListBox1_DblClick Nothing
End Sub

Now you can see how it works:
When UserForm1 is displayed first, it can display UserForm2 that calls the public ListBox1_DblClick procrdure in UserForm1.
When one start from UserForm2, the first call of UserForm1 creates it hidden (fires 'Initialize' event) and next DblClick procedure is called (hidden userform is not modal).

There is a separate question why to use it, but it's a good example to understand how events and userforms work.

combo

 

[FaneDuru]

Thanks Maestro!

It realy works. I supposed that is a matter of argument missing and I tried False (True) but never immagined that 'Nnothing' is the appropriate one...
y single regret is that only a star can be given..

Thanks again!

Regarding the issue of necessary reason: I have two different forms for modifying, respectively, creating of some stuff. Sometimes I need to use the reference of an existing 'stuff' for being passed in the form of creation. Modify the 'stuff' a little and avoid writing in a lot of boxes. There could be many approaches but this one looked simpler for me. I solved it using for the called event a separate procedure (Public) able to be called from the other form and worked, but I wanted to know how to make a direct call to an event... Now, I know it.

 

[combo]

Thanks.
Actually, there is a way to pass an argument other than Nothing (can be used for KeyDown event and ReturnInteger object). Requires additional class module:

Implements MSForms.ReturnBoolean
Private mValue As Boolean

Private Sub Class_Initialize()
mValue = False
End Sub

Public Property Let Value(ByVal NewValue As Boolean)
mValue = NewValue
End Property

Private Property Get ReturnBoolean_Value() As Boolean
ReturnBoolean_Value = mValue
End Property

Private Property Let ReturnBoolean_Value(ByVal RHS As Boolean)
mValue = RHS
End Property

New code in UserForm2:

Dim oReturnBoolean As Class1

Private Sub CommandButton1_Click()
Set oReturnBoolean = New Class1
oReturnBoolean.Value = True
UserForm1.ListBox1_DblClick oReturnBoolean
End Sub

combo

 

[FaneDuru]

Here's the oportunity for the seccond star (if it works...)
Who knows when I can use that...?