The code you present looks OK. The problem lies within the code of the
Userform. In the VBA editor, go to the Tools menu, choose Options,
then the "General" tab. There, in the "Error Trapping" section, choose
"Break In Class Module". Save your workbook and re-open it. The code
should enter debug mode on the exact line of code that is causing the
problem. (If you don't have error trapping set to "Break In Class
Module", debugging will stop on the line that instantiates a class --
your code's Show methods -- rather than on the line of code within the
class that actually caused the error.)
ActiveSheet.Range("H15").Select
I wouldn't rely on that. When you save and close a workbook, it will
re-open to the sheet and cell that was active when the workbook was
saved. This may not be the sheet that you think it should be. Instead,
do something like
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("H15").Select
You can't just use
Worksheets("Sheet1").Range("H15").Select
alone because if Sheet1 isn't active, the code will fail. You must
first Select a sheet before Selecting anything on that sheet.
Or, much better, don't select anything at all. Get rid of the Select
code and use:
If Worksheets("Sheet1").Range("H15").Value = "" Then
UserForm1.Show
Else
UserForm2.Show
End If
In this case, it doesn't matter what sheet happens to be active when
the workbook is opened -- it will always test H15 on Sheet1.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
