UserForm1.Show gives an error... 1

[logius]

Okay, here's the setup: I've got an .XLS file storing a set of macros (modules) and a single userform (called UserForm1). Now, I'm invoking the macros from another worksheet that I opened in Excel and I'm trying to load the userform that I created:

Sub ShowMyForm()
    UserForm1.Show    
End Sub

but everytime I try, I get the same error:

Run-time error '424'
Object Required

Can someone help me out here? I'm getting annoied with this thing.

 

[Mala]

Simply using UserForm1 means 'UserForm1 in the ActiveWorkbook -

Since u're invoking this from another workbook - which does NOT contain UserForm1 - u get the error msg.

See the VBA help & then try making the proper reference to UserForm1 - and PLEASE don't do yourself the disservice of getting annoyed - the thing'll elude you more...

Good luck!

 

[cryoguy]

Mala is correct. One solution to your problem may be as follows:
Place the following code in the module of any workbook (other than one named Book1.xls):

Sub ShowMyForm()
Application.Run ("Book1!ShowForm&quot
End Sub

Then place the following code into a different workbook in this case called Book1.xls which contains the Form UserForm1:

Sub ShowForm()
UserForm1.Show
End Sub

I hope this solution is adequate for you. Of course if the workbook invoked by the ShowMyForm() macro is not open (in this case Book1.xls) or if it does not contain a macro called ShowForm() you will still get a 424 error.

 

[Mala]

Good, cryoguy!

Inclusion of the following code would do the needful (and avoid the error msg) if the file is not open:

Sub CheckOpen()
BookName = "C:\MyDocuments\ShowFormBook.xls"
For Each Bk In Workbooks
If Bk.FullName = BookName Then Exit Sub
Next
Workbooks.Open FileName:=BookName
End Sub

This sub can be entered into you suggested sub or referred to as:

Sub ShowMyForm()
CheckOpen
Application.Run ("Book1!ShowForm&quot
End Sub

 

[Mala]

PS Correct the name of the UserForm workbook in either of the two procedures - it should be one of (of course, you'll actually use the name YOU have given to the file):

'ShowFormBook' or 'Book1' in BOTH procedures

 

[logius]

Well, actually I'd thought of that. The only problem is that adding modules to the referencing workbook isn't an option.

Users are supposed to be able to download "sterile" data in a .CSV format (clean of any modules/forms/add-ins) and run the macros I created to generate their charts. What I wanted to do was create a user interface that would allow them to specify the ranges of the data (they won't always want ALL the data in the file), type of chart, etc.

The purpose is to make things more convenient for the users (who will probably have next to no experience with Excel) so they won't have to select the data manually and go through the process of creating the chart. Yeah, I know, lazy bums...

 

[Mala]

In this case the procedure would be to import the data from a user-specified CSV in Excel and THEN process it. It shouldn't be very difficult to write code for this...

If you could send a sample .CSV and concerned workbook(s), I can try to devise a solution for you.

mala_rs_singh@rediffmail.com