Run-time error 1004

Experts:

I came across a **working** dashboard example which was authored by "veekay".   Please see attached XLS ("Dashboard").   The demo works great using the following process:
1. Click on command button "Update Drop Downs"
2. Select values from the combo boxes (Products, Region, Customer Type)
3. Click on command button "Show Data"
... given the selected cmd button values, data is shown in row 13++

For testing purposes, I mimiced the dashboard example (i.e., ensuring I copy all require objects and VBA code) before attempting to develop my own process (with different data).
Unfortunaly, when executing the "Show Data" command button (after "Updating Drop Down" and selecting values from "Products", "Region", and "Customer Type", I am getting run-time error '1004' (Methods 'Range' of object' _ Worksheet failed).

What causes this problem in my replicate XLS?    Did I forget to update some VBA?

Thank you in advance,
EEH
Dashboard.xlsm
Excel-as-a-Database.xlsm
ExpExchHelpAnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
You forgot to replicate named ranges (see the names manager in the formulas tab).

Plus your workbook suffer from various syndroms:
- Global variables.
- Explicit use of "active" objects (ActiveWorkbook, Selection ect ....)
- Implicit use of "active" objects.
- Lack of error handlers.
- Explicit references (AKA Early binding).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExpExchHelpAnalystAuthor Commented:
Fabrice:

Thank you for the prompt response... great -- I added the missing named range and the replica XLS works now.

Wrt the other syndroms, did I forget to include them somewhere else (or did the original version also "suffer" from the same symptons)?
If I forgot them, where do I make those changes?   If not, how can the VBA code be improved to address your recommendations?

Thanks,
EEH
0
Fabrice LambertFabrice LambertCommented:
Same symptoms in the original workbook.

Not much to say beside Learning good practices with VBA.
Give meaningfull names to variables.

Avoid "active object" because they are subject to change upon any user interraction, thus are unreliable and chaotic, anthe fact that they exist doesn't mean you should use them.

Avoid also the generic "sheets", "Range", "Cells" object as they implicitly refer to ActiveWorkbook.Sheets, ActiveWorkbook.ActiveSheet.Range, ActiveWorkbook.ActiveSheet.cells (yuck ! Too many active objects in those statements).

Avoid global variables unless you have no other choices (remember that variables can always be given as parameters to functions).

Apply the Single Responsibility Principle (SRP):
- A function or class should do one thing, and do it right (instead of doing many things badly), it is far easyer to maintain.

Personnal notes:
You'll probably find articles about declaring all variables at top of functions and hungarian notation, I don't consider those good practices.
In my eyes, pre-declaring variables is a bad practices because:
- The variable declaration can be far away from its initialisation.
- It unnecessary extend the variable's lifetime.

Hungarian notation is bad because:
- Prefixing the variable name with its type reduce lisibility.
- It give no information about the variable's role.
- If a variable type change (int becomes double, or long or whataver), your whole code becomes a lie.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ExpExchHelpAnalystAuthor Commented:
Fabrice - as indicated, I did not develop the VBA, so I can't take credit for it.

Thank you for providing the resolution to the run-time error... I appreciate it.

EEH
0
ExpExchHelpAnalystAuthor Commented:
Thank you... appreciate the feedback.
0
Fabrice LambertFabrice LambertCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.