DropDown problem in Excel 2003

sektor · March 25th, 2008, 03:59 AM

I have used a code from "Excel 2007 VBA Programmer's Reference" book to create a DropDown on a worksheet (Chapter 10 "Adding controls").
It's OK in Excel 2007, but when I open it in Excel 2003, the error occur when code reach the following line of code:

With Sheet2.DropDowns(Application.Caller)

VBA says that it can't get property DropDowns of Worksheet class (Run-time error 1004). What should be corrected to avoid this error?

tstav · March 25th, 2008, 08:13 AM

I would guess it's the Sheet qualifier that causes the problem.
If the Sheet's name is Sheet2 maybe you should use

Code:

Sheets("Sheet2").Dropdowns(Application.Caller)

or, if the Sheet has been assigned to a Sheet variable e.g. Products then you should use

Code:

Products.DropDowns(Application.Caller)

sektor · March 27th, 2008, 02:21 AM

I changed the sheet qualifier as "Sheets" and as "Worksheets" but resultless. Is there any other way to fix this problem?

tstav · March 27th, 2008, 05:20 AM

Sektor, hoping that I'm not over-analytical, let me mention this:
In order for the
With Sheet2.DropDowns(Application.Caller)
to work, a valid SheetName must have been assigned to the "Sheet2" qualifier.
i.e. the following lines must come beforehand (in Excel 2003 VBA):

Code:

Dim Sheet2 as Worksheet
Set Sheet2 = Worksheets("Sektor") 'if Sektor is the name of the Sheet with the dropdown
With Sheet2.DropDowns(Application.Caller)
' etc.....

I guess you must have something similar in XL 2007 VBA (if not exactly the same).

If this is not the case and Sheet2 is the name of the Sheet, then I would insist that my previous mail should work just fine (use Sheets("Sheet2")).
Try this: Change the name of your Sheet to "Sektor" and try using Sheets("Sector").