open Userform in Another workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

I am trying to open a userform in another workbook and change the value for
one of the comboboxes in that Userform... Here is what I have..

Workbooks.Open "E:\Path\filename.xls"
UserForm3.Show vbModal
c003.Value = "test"

It is not working!

Can someone help?

Thanks in advance,
geebee
 
What you need to do is have a small macro in that other workbook that shows
the userform, then run that macro using Application.Run.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
hi,

There is already a Module in the other workbook called custForm1 which
contains the following to open the Userform3:

Sub Z()
UserForm3.Show
End Sub

I just am not sure of the syntax now, becausae I am getting an error message
witrh the following:

Application.Run "Z"

Am I missing something?

Thanks in advance,
geebee
 
Application.Run "'another book2.xls'!Z"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
hi,

I now have...

Workbooks.Open "filename.xls"
Application.Run "filename2.xls'!Z"
CustFilter.c003.Value = "test"

I noticed that the fields value is not changing to "test" . The form is
modal. Does that have anything to do with it?

Thanks in advance,
geebee
 
I put this routine in the workbook with the userform:

Option Explicit
Sub ShowIt(myVal As String)
Load UserForm1
UserForm1.ComboBox1.Value = myVal
UserForm1.Show
End Sub

I saved and closed that workbook (as book1.xls).

Then I used this code to open and show the form:

Option Explicit
Sub testme()
Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="C:\my documents\excel\book1.xls")
Application.Run "'" & wkbk.Name & "'!Showit", "xxxx"
End Sub
 
Back
Top