Excel 2003 Win XP - Command Button problem?

Al,

If you're referring to opening the User Form itself, Martin Green suggests the following in his 'Excel VBA Notes: 4' (which can be downloaded from his website -http://www.fontstuff.com/downloads/Excel%20VBA%20Course%20Notes%204%20-%20User%20Forms.pdf):

Attach a comand button to your worksheet and assign a macro to it, the macro being placed in a workbook module e.g. if the User Form is named frmCourseBooking, then the following Sub will open it

Sub OpenCourseBookingForm()

    frmCourseBooking.Show

End Sub

Create the Sub first. Then to attach a button to your worksheet, open the Forms toolbar, click the Button icon, and then click the sheet. The assign macro dialogue box will then open automatically. The default button name is Button 1 but this can be edited and the button resized if necessary.

Hi Tom - thank you so much for taking the trouble to reply. I re-did the spreadsheet doing exactly as you said - still Error 424 -Object required ?!

The ONLY difference I can spot opening two instances of Excel and having the downloaded sheet on one and mine on the other, side by side on the screen is:-

When R/Click Cmd Button - Assign Macro - the box appears -

1) Downloaded one has 'VBA04-UserForms.xls'!OpenCourseBookingForm

2) My sheet has the name of it ExcelCodingTest!OpenCourseBookingForm

i.e. no exclamation marks round the File Name

Tearing my hair out now !! I have good use I can make of this if only I can get past this hurdle ?!

Thanks

Al

Al,

It's not just the single quote marks that have been left out but also the .xls

Try using 'ExcelCodingTest.xls'!OpenCourseBookingForm

That assumes the filename of your Excel workbook is ExcelCodingTest. Generally you don't need to specify the filename when assigning a macro and in the assign macro dialogue box you could just type OpenCourseBookingForm as the name of the macro.

Hope that does the trick for you.

Hi Tom - Thanks for your patience - Sorry I already had the .xls part of it in place

When I click the command button error 424 'Object required' appears in:-

Sub OpenCourseBookingForm()

frmCourseBooking.Show

End Sub

frmCourseBooking is highlighted - presuably indicating that frmCourseBooking cannot be found despite the fact it is visible in VBAProject(ExcelCodingTest) in a folder called Forms and if I double click it the form appears - Name frmCourseBooking !?

Dumfounded

Al

Al,

Sorry about the .xls - I saw in your first post it was included but in your second it was omitted.

If you can post your ExcelCodingTest file in a public folder on Microsoft Skydrive I'll have a look at it & see if I can find the problem that way.

Hi Tom - You must have the patience of a Saint !?

I had never heard of Skydrive - anyway, I have loaded the file onto it, but I am not sure how you would find it?

Do you need my EMail or the file name........?

I have put the file in a folder called ' ExcelFile ' shared with everyone - but how do you find it ?

Sorry to be dim

Al

Hi Tom - Perhaps this would work ?

http://cid-276c0d9e4760fad2.office.live.com/browse.aspx/ExcelFile 

Regards,

Al

Al,

Thanks for posting your file to Skydrive - you were right, all I needed was the web address.

You have a combo box on your user form (top right) that's currently named cboDept but the Initialize routine in the user form code is looking for a control named cboDepartment - so all you need to do is change the name of the combo box to cboDepartment.

You also have a check box named chkVeg where code is looking for chkVegetarian, and at the end of the cmdOK_Click sub you need to complete the line Range("A1").Select - there might be other little slips I haven't seen.

I would also delete the 3 lines of code in the Sheet1 code window (right click Sheet1 in the VB editor and select view code) - the open command isn't right and is also superseded by the code in Module 1.

Good luck with your VBA'ing and feel free to ask more questions!

Hi Tom - Very quick replies - thanks ever so much

I have been through the masochistic mill with Access VBA (for fun?!) over the years - but do dislike giving in.

I have done all you suggested - it works !

I have just been typing along winded reply stating I could not understand why it stopped where it did - 'Object Required' threw me.

Obviously if the module is trying to show a form where the initialisation code is faulty .....................!

Silly me ! (I knew I had abreviated some of these names, but thought I would sort the rest out later - wow - what a time waster - Never put off 'til tomorrow............................. !

I sincerely thank you very much for your help, very much appreciated

Kind regards,

Al

Al,

Thanks for the feedback.

The error messages from VBA aren't always very illuminating so it's a good idea to try getting an early handle on debug techniques (as well as general VBA techniques). I found a good reference when first learning VBA was 'Excel VBA Programming', written by John Walkenbach (who also has his own website: http://spreadsheetpage.com/). The book is well illustrated and has a nice sense of humour to help things along, and includes chapters on error trapping and debugging. If interested, make sure you get the edition for your version of Excel (2003/2007/2010).

The particular error message here, 'Object Required', was tricky - not least because the user form and all the boxes on it are all Excel objects. Also, as you said, clicking debug just pointed to the frmCourseBooking.Show line, even though there was actually nothing wrong with that line! When stepping through the code line by line, however, it was the cboDept line that was causing the error mesage (combo box is an object!).