General discussion

Locked

Excel VBA

By AWebb2 ·
I have two wokbooks.

WB1.xls - contain a Userform named "Console"
WB2.xls

Both worksheet are open. In WB2 there is a command button with the following code:

Public Sub Back2Console_Click()

' refer to WB1 which was opened first
Workbooks(1).Activate

' Show UserForm
Console.Show

End Sub

On clicking the Command button in WB2, I am getting the following:

Run-time error '424':
Object required


My intention is to get to the form (Console) in WB1. How can I fix this code?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel VBA

by Raffi_ In reply to Excel VBA

Quite aside from anything else that is going on your Userform "Console" is probably a reserved word. Rename the Userform in any case.

Regards
Raffi_

Collapse -

Excel VBA

by AWebb2 In reply to Excel VBA

Poster rated this answer

Collapse -

Excel VBA

by Peyison In reply to Excel VBA

When you activate the other workbook, that does not give you access to any of the VBA objects in that workbook (forms, modules, etc.)

So when you call Console.Show in WB2, it doesn't matter what the active workbook is, you're still within the VBA project environment of the workbook WB2. It is looking for a form called Console within the workbook WB2.

There is a way to reference VBA objects in one workbook from another, but you can only access code modules, not forms.

What you would need to do is create a public sub or function in a code module (or class module) in WB1 that would show the form Console. Then have WB2 call this sub to open the form.

To provide the ability for WB2 to call WB1, you need to set up WB1 as a reference.

First, rename the VBA project of WB1 to something other than VBAProject. Call it WB1Project.

In the VBA editor, select the project for WB2.

Select Tools -> References and select WB1Project (or whatever you named it).

You can now access public subs and functions, variables and constants in code modules in WB1 from WB2. Use the project name and module name in the reference:

(in WB2)
Sub Test()
WB1Project.Module1.ShowConsole
End Text

(in WB1 - in a code module called Module1)

Public Sub ShowConsole
Console.Show
End Sub

Hope this helps.

Collapse -

Excel VBA

by AWebb2 In reply to Excel VBA

Thanks. Answer was excellent. Got the job done immediately.

Collapse -

Excel VBA

by AWebb2 In reply to Excel VBA

This question was closed by the author

Back to Windows Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums