Object required error on CommandButton Click

I have a CommanButton on a sheet that simply opens a UserForm, but I get a Run-time error 424.....Object required error. Can anyone tell me how to fix. I'm new to VBA. See code:

Please Login or Register  to view this content.

Nothing looks wrong there. Are you sure the name is correct?

Is it possible to attach the workbook?

Hello rkjudy,

Well I sure can't see any reason for this not to work. Hopefullly one of the other members or a Forum moderator will help.

On an aside and still being a rookie, it seems you could just use a regular forms button for a click type procedure versus the active x. Not entirely sure the difference. Guess I need to do some reading.

Also, the slowness could be from the way you have your code. It appears you must have used the macro recorder to get most of your code. There is no need to use all of the Select/Selection.

If you don't mind I will clean some of it up and send you a sample. In the meantime hopefully somebody will come to our rescue.

Hello rkjudy,

Okay, figured out not neccesarily where your problem is; however, I know where the error is coming from.

Go to the VBE and the right click on the frmOrderEntryInfo. Select view code and then temporarily disable all the code.

Now go back to the worksheet and press the CommandButton1. This worked for me so it's telling me you have to clean up, or actually finish whatever code you have behind the frm.

Here is what I found:

Goto Tools>Options... and select Break On All Errors on the General tab.

Now if there is a problem in the initialize event the debugger should go to the line of code causing the problem.

Or create a break point (F9) on a line of code in the initialize event and then step through the code with F8.

Please Login or Register  to view this content.

Okay here are just a few tips which may help.

First, declare all variables and to ensure this happens enable Option Explicit.
Go to the VBE >> Alt + F11 >> Options >> Editor Tab >> Require Variable Declaration.

Second, a little code clean up is necessary. You have a lot in there and it may take some considerable effort, but in the end the your spreadsheet will thank you.

The basic premise; usually there is no need to select an object to work with it in VBA so you can drop most of the Select/Selection, it just slows down the overall speed. As you are recording code, whatever you do is recorded, just like when you scroll the mouse (ActiveWindow.SmallScroll Down:=-6). This part is not necessary and is one of the culprits to slowing overall performance.

Some more reading Beyond the Macro Recorder.

Now, for an example of how you can clean up what you have. If you add a blank sheet to your workbook and title that blank sheet Sheet1 you can run this test code. Open up the VBE and place this code in a module and then place your cursor on the routine. Using F8 you can step through the code and watch it work. This means, if you have a dual monitor setup you can step through the code as you watch it play out on the Excel worksheet.

Here’s a webpage which can add to speeding up code .

Last thing I'll say is indenting code is a very good habit. Not only does it make the code easier for you to follow, but when you ask somebody else to look at your code it will make it easier for them too. One of the links below is for identing code.

Please Login or Register  to view this content.

Also for the passwords…

Please Login or Register  to view this content.

http://www.excelforum.com/2078299-post14.html
http://www.mrexcel.com/forum/showthread.php?t=339643
http://www.mrexcel.com/forum/showthread.php?t=352116
http://www.mrexcel.com/forum/showthread.php?t=517549