Directly referencing userforms externally

[dnickelson]

Getting stumped. Trying to play with WHScripting to access some of the excel features so I'm working with the objects in VBA first (so I at least have a starting space) but can't figure out how (or if it's possible) to access the Userforms in another workbook directly, keep getting errors. Various error messages possibly related to how I'm accessing the objects at all. I'm hoping to do various things with the userform (show\hide, grab variables, validation, etc), but stuck on the very first thing which is access it in the first place. Looked through the topics, but so much about userforms, can't filter it down to any external references. Rather not have to have the original workbook do all the work, but do I have a choice?

Sub something()
Dim wb As Workbook

Application.Workbooks.Open ("c:\copy of text.xls")
Workbooks("copy of text.xls").UserForms.Count


End Sub
         'Object doesn't support this property or method

Sub somethingelse()
Dim wb As Workbook

Application.Workbooks.Open ("c:\copy of text.xls")
With Workbooks("copy of text.xls")
    userform1.Show
End With

End Sub
        'object required

Sub somethingother()
Dim wb As Workbook

Application.Workbooks.Open ("c:\copy of text.xls")
With Workbooks("copy of text.xls")
    UserForms(1).Show
End With

End Sub
        'subscript out of range

haven't necessarily thought it all the way through, how to keep track of what's doing what, but the idea is that I can have a whscript file a user can open which will bring up userforms I've created in various workbooks, maybe word docs, etc. the main point is a point of reference external to any of the individual vba applications that can (hopefully) directly utilize some of the vba stuff I've already done.

maybe a little ambitious for not even being able to figure out this first part, but....

 

[macleanb]

Hi

One thing - UserForms collection only gets populated with items when userforms are loaded - so it would be empty in your example.


Have you worked on getting any VBA to run via an external script - you know kinda hello world type of stuff?


Take a look at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno2k3ta/html/odc_VBNETcallsVBA.asp


for some clues...

 

[macleanb]

Just saw this link posted by PennySaver to a different question - but I reckon it will be of interest you (its a great Excel/VBA sitte alround - so have a browse)


http://www.cpearson.com/excel/vbe.htm

 

[dnickelson]

Thanks much. I have done some of the 'hello world' type trials and the interface does work reasonably well. Thanks for the note about the UserForms not being populated when I might be expecting them to. I was activating and opening the work though so I thought that would have made them available, but I'll follow some other pathes until this makes sense to me.

The link was handy too. I'm actually trying it from WHScripts rather than .NET, but I'm actually looking to move over to VB rather than scripts anyway.
Like they say, the stuff Microsoft writes all works the same except where it doesn't. (or maybe that's just me that says that )

Thanks,
-Dan

 

[macleanb]

I like to think of it as the great thing about Microsoft standards - is that theres so many to choose from