Need Excel VBA guru to help with simple problem (I think)

[dwest100]

Hi,
I have this sub:
Sub ShowForm(frmName)
frmName.Show
End Sub

From another sub which is attached to the OK button of a form, I pass the form name as an argument. But I get an error saying that "the macro ShowForm() can not be found."

The only way I can get the form to display is to remove the arguments and just have the parentheses.

Can I not pass arguments from macro to macro????

Here is the portion of the code attached to the OK button:

newMenuItem.Caption = "Adjust the Schedule"
newMenuItem.OnAction = "ShowForm(frmAdjustSchedule)"

The objective is to show the form when the menu item is clicked by passing the form name as an argument to the ShowForm macro. That way, I don't have to write a ShowForm() macro for each different form called by the menu items. Thus, bloating my code.

Any help would be greatly appreciated!

Here is the code for your reference:

Sub ShowForm(frmName)
   frmName.Show
End Sub

Sub Remove_Workbook_Menu()
    'this removes the menu if it is present
    On Error Resume Next
    CommandBars("Worksheet Menu Bar").Controls("Task Blocks").Delete
    On Error GoTo 0
End Sub

Sub Add_Workbook_Menu_And_Items()
    Dim newMenu
    Dim newMenuItem
    'delete the menu if it exists by calling this subroutine
    'Remove_Menu

    'add a new menu to the worksheet menu.  The menu is temporary and

'will disappear when Excel closes
    With CommandBars("Worksheet Menu Bar")
        Set newMenu = .Controls.Add( _
                Type:=msoControlPopup, _
                before:=.Controls("Help").Index, _
                temporary:=True)
    End With
    'give the new menu a name
    newMenu.Caption = "Task Blocks"

    'add a menu item to the new menu
    Set newMenuItem = newMenu.Controls.Add(Type:=msoControlButton)
    'give the new menu item a name and assign a form to it
    [COLOR=red]newMenuItem.Caption = "Adjust the Schedule"
    newMenuItem.OnAction = "ShowForm(frmAdjustSchedule)"[/color]
    'add another menu item to the new menu
    Set newMenuItem = newMenu.Controls.Add(Type:=msoControlButton)
    'give the new menu item a name and assign a macro to it
    newMenuItem.Caption = "Add Website Project"
    newMenuItem.OnAction = "Sheet3.Website"
    'add another menu item to the new menu
    Set newMenuItem = newMenu.Controls.Add(Type:=msoControlButton)
    'give the new menu item a name and assign a macro to it
    newMenuItem.Caption = "Add Printing Project"
    newMenuItem.OnAction = "Sheet3.Printing"
    'add another menu item to the new menu
    Set newMenuItem = newMenu.Controls.Add(Type:=msoControlButton)
    'give the new menu item a name and assign a macro to it
    newMenuItem.Caption = "Take A Break"
    newMenuItem.OnAction = "Sheet3.Break"
    'add another menu item to the new menu
    Set newMenuItem = newMenu.Controls.Add(Type:=msoControlButton)
    'give the new menu item a name and assign a macro to it
    newMenuItem.Caption = "New Task"
    newMenuItem.OnAction = "Sheet3.NewTask"
    
End Sub

 

[DeeBeeGee]

Why not cut out the ShowForm subroutine and simply use the .Show property of the form you want to show?
or are there other commands you have stripped from the sub for simplicity?

 

[dwest100]

Thanks for the reply DeeBeeGee. I've tried doing that but I get the same error as described above.
Any ideas why?

 

[GOstlund]

Sub ShowForm( FormName As String )
Me( FormName ).Show
End Sub


----------
HTH
Gavin Ostlund

 

[dwest100]

Thanks Gavin,
I will give that a try and post the results later tonight.
Don

 

[Zathras]

You can try this and see if it works for you:

Sub ShowForm()[COLOR=green]
  ' *** CAUTION *** This use of the Caller property is undocumented.
  ' When invoked from a menu, Caller(1) returns the position from the
  ' menu where the selected item is found.  Use with care.[/color]
  ShowRequestedForm Application.Caller(1)
End Sub

Sub ShowRequestedForm(AMenuItemNumber As Integer)
  Select Case AMenuItemNumber
    Case 1:  UserForm1.Show
    Case 2:  UserForm2.Show
  End Select
End Sub

Change your original macro to look like this for the OnAction assignments:
[tt]
newMenuItem.OnAction = "ShowForm"
[/tt]
But it might be safer and simpler (and easier for someone else coming along behind you) to have a specific macro for each menu action. Only simple macros can be assigned. I.e., macros that appear in the macro list, which means no parameters.

 

[dwest100]

Thanks!
I tried Gavin's solution to no avail.

The problem here is the calling of a macro with arguments from the menuitem OnAction event. I'm coming to the conclusion that it can't be done because of the arguments.

As a result, I must have a showform sub with no arguments for each menu item. It works fine if I do that. But it's such a stupid thing to program that way. What if I had 15 menu items! What needless code bloat.

A post on another forum suggested I look at classes. But I know nothing of them.

Is that an avenue worth exploring?

Thanks!

 

[Zathras]

So..... Did you try my suggestion?
It works in both Excel 97 and Excel 2000

BTW, in terms of "code bloat" I don't think it makes much difference between adding a one-line sub and adding a case to a select case structure. Both techniques are not much more than a couple of branch instructions.

 

[dwest100]

Hi Zathras,
Sorry for the delay. I had to get some sleep.

I just left it as is with a sub for each menu item.
As you mentioned, it's really not code bloating and it's a documented way of dealing with it.

Thanks for passing your knowledge along though. I sincerely appreciate it!
Don