UserForm Show Problem

[K]

Hi all, I have code (see below) in sheet module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
If Not Application.Intersect(Range("D1112"), Target) Is Nothing Then
UserForm1.Show
End If
End Sub

when i double click on range D11 or D12 i get error message saying
" Run-time error '459':
Object or class does not support the set of events "

and when i click on debug then line "UserForm1.Show" gets higlighted.
It was working alright couple days ago but suddenly i start getting
this error. Please can any friend can help.

 

[Joel]

fiorst comment out Userform1.show and replace with a=1 to make sure it it the
userform that has the problem. If the error doesn't occur then there is
something wrong with the target location.

Otherwise uncomment out the userform statement and put a break point using
F9 on the first line of every function in the userform. The first line is
the line with SUB like any onclick or initialize function.

The run the code. Keep on pressing F5 until the error occurs. the last
break point you reach will tell you which function is causing the error.

the start stepping through the last routine until you find the problem

One thing that may help find the problem quickly is to change the VBA errror
trapping. Go to VBA menu tools - OPtions - General - Error trapping. Change
setting to Trap on All errors. Also in finding problems like this I comment
out all ON Error statements. You may be bypassing an error and then casuing
a 2nd statement to fail. Yo can later add back the error trapping statment
after yo get the code working.

 

[Chip Pearson]

In the VBA editor, go to the Tools menu and choose Options. There,
select the General tab and change the Error Trapping setting to "Break
In Class Module". If the setting is not "Break In Class" and an error
occurs in the code within the UserForm, the debugger will take you to
the line of code in which the class (in this case the loaded instance
of the user form) was created. In your code, there is nothing wrong
with the UserForm1.Show code as such, but if an error occurs within
the form, that line is highlighted by the debugger. When error
trapping is "Break In Class Module" and there is an error within the
userform's code, the debugger will take you to the line of code that
actually caused the error within the form's code.

As a general rule, you should always have error trapping set to "Break
In Class Module" unless you have a very good reason not to.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)