PDA

View Full Version : [SOLVED] UserForm does not show ... Runtime error



krishnak
07-27-2016, 02:37 PM
I created a UserForm and the following macro opens it:

Sub DoMetrics()
frmMetrics.Show
End Sub

This macro is placed in a module created in the Workbook. I placed a button on a worksheet (Sheet4) and assigned the above macro for that button.
The UserForm opens when the button is actuated.

I moved (cut and paste) the button to another worksheet (Sheet2) in the same workbook and reassigned the button to the same macro.
When I actuate the button now, I get the following runtime error 1004:

"Method of 'Range' of object '_Worksheet' failed."

When I move (cut and paste) this button to the previous worksheet (Sheet4), it works again. There seems to be a reference to a Worksheet, but I am not able to understand it.

As I understand, we have a module common to all worksheets and the macro should be available to the button from whichever worksheet it is placed.
I'll appreciate if anyone explains to me what the problem is.

Kenneth Hobs
07-27-2016, 04:09 PM
Sounds like your userform's code is the problem. Use Debug's F8 in that Sub to step line by line to see where the problem lies.

Paul_Hossler
07-27-2016, 06:03 PM
Or post a workbook with enough of the code that demonstrates the problem

krishnak
07-28-2016, 07:43 AM
I created another Test workbook with a UserForm with a button to open the Form moved from one worksheet to another.
This works without problem.
Obviously I have a problem in the Form_Initialize code or cod for other events. I'll check and come back if I cannot find the solution.
Thanks for the help.

krishnak
07-28-2016, 02:30 PM
I have a macro that sets the ranges from the data in the data worksheet. I am using the Cells notation for the ranges.
When I am calling the macro from another worksheet, the Range is throwing an error as I did not include a period(.) before the Cells keywords.
After correcting the code with periods, I could move the button to any worksheet and the Form works.