Problem executing public subroutine!

[jakuza]

I have two different forms (Class1 and Class2) which are similar and both of them have object ComboBox1. Because these ComboBoxes have same list of values I created Public subroutine in one of Modules to run certain macro when certain value from ComboBox is selected:

Public Sub RunMacro1 (FormName As Object)

If FormName.ComboBox1.Value = "Birthday" Then
    Macro1_click
End If
End Sub

Also I call this subroutine with RunMacro1 (Class1) but it gives me an error: "Object doesn't support this property or method".
I figured out that I can not write FormName.ComboBox1 to reference to an object but how can I do this?

 

[bertie]

The best way to execute a macro dependant on a ComboBox selection would be to use a ComboBox_Change event.

First, In the VBA Editor window click Insert => Module. Before you can access a UserForm's control you have to LOAD it into memory; then SHOW the form.

For testing I have used a form named Class1

[COLOR=darkblue]Sub[/COLOR] Main()
   Load Class1 [COLOR=green]'load the form[/COLOR]
   Class1.Show 'then show it
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

In the same module place the test macros:

[COLOR=darkblue]Sub[/COLOR] RunMacro1()
   MsgBox "RunMacro1 Success"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] RunMacro2()
   MsgBox "RunMacro2 Success"
[COLOR=darkblue]End[/COLOR] Sub

The two procedures below go into the UserForm module.
On the UserForm double click on the ComboBox to get the outline of the change event.
I have used a Select Case statement to determine which macro to run.
Note how we reference the module where the macro located.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ComboBox1_Change()
   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] U[COLOR=darkblue]Case[/COLOR](ComboBox1.Value)
      [COLOR=darkblue]Case[/COLOR] "BIRTHDAY"
         [COLOR=#ff0000]Module1[/COLOR].RunMacro1
      [COLOR=darkblue]Case[/COLOR] "ITEM"
         Module1.RunMacro2
      Case [COLOR=darkblue]Else[/COLOR]   [COLOR=green]'default option[/COLOR]
         [COLOR=green]'do this[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Here is the form's initialisation event:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
   [COLOR=darkblue]With[/COLOR] ComboBox1
      .AddItem "Birthday"
      .AddItem "Item"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps,
Bertie

 

[jakuza]

Bertie,

thanks for reply. You are right but that is not what I am looking for. The problem is that if I put Select Case statement in change event I must put it in both forms (Class1 and Class2). It is e.g. 100 lines of same code. And if I add another form it is another 100 lines. I am trying to optimize my code, that is why I want to put it in one procedure or subroutine.
Is it possible to use variable for object reference? Or is there some way to search all forms in project like the sheets.
e.g. I can use below code to search every sheets in workbook.

For Each ws in Worksheets
   If ws.CodeName="Sheet2" then
      do something...
   End if
Next ws

Is there some function Forms to use it in Forms(FormName).ComboBox.Value to reference to some object?

 

[bertie]

First, you could reduce the change event to:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ComboBox1_Change()
   Module1.ExecuteMacro ComboBox1.Value
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

With ExecuteMacro in Module1

[COLOR=darkblue]Sub[/COLOR] ExecuteMacro([COLOR=darkblue]ByVal[/COLOR] mySelection [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR])
   Select [COLOR=darkblue]Case[/COLOR] U[COLOR=darkblue]Case[/COLOR](my[COLOR=darkblue]Select[/COLOR]ion)
      [COLOR=darkblue]Case[/COLOR] "BIRTHDAY"
         RunMacro1
      [COLOR=darkblue]Case[/COLOR] "ITEM"
         RunMacro2
      Case [COLOR=darkblue]Else[/COLOR]   [COLOR=green]'default option[/COLOR]
         [COLOR=green]'do this[/COLOR]
   [COLOR=darkblue]End[/COLOR] Select
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

But this may be more what you are looking for:

[COLOR=darkblue]Sub[/COLOR] Main()
   [COLOR=darkblue]Dim[/COLOR] sFormName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   
   sFormName = "Class1"
   LoadForm sFormName
   
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Sub[/COLOR] LoadForm([COLOR=darkblue]ByVal[/COLOR] sFormName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR])
   [COLOR=darkblue]Dim[/COLOR] frm [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
   [COLOR=darkblue]Set[/COLOR] frm = VBA.UserForms.Add(sFormName)
   
   Load frm
   frm.Show
[COLOR=darkblue]End[/COLOR] Sub

 

[jakuza]

Thanks Bertie,
this code give me inspiration:

Private Sub ComboBox1_Change()
   Module1.ExecuteMacro ComboBox1.Value 
End Sub

I was not thinking enough. Instead of calling the value from combobox in a subroutine I could simply forward it! This solved my problem. No need to reference to an object. I needed to know only value from combobox.

Really grateful,
-JAKUZA-