Excel Database Macro Help

[peachroses82]

Hey everyone,

I have an excel database that I have created with about 20 worksheets and over 45 macros. Each macro takes a section from one of the 20 worksheets and copies it to the main worksheet for an easy search function and user friendliness. I have a dropdown box with each of the 45 sections listed as selections.

I need the coding/macro that will run a macro whenever something is chosen in that drop down box, and will run the appropriate macro. For instance, if someone chooses Accountability -Employee, I would like for the AccountabilityEmployee macro to run which would populate that page with the corresponding information and so on for all of the 45 macros.

Obviously, I don't want you to give me the entire coding for the whole 45 macros, but if you can please just get me started, I would appreciate it.

Thank you.

[davegugg]

Please Login or Register  to view this content.

[peachroses82]

Thank you for the start. Here is the code that I have:

Code:

Please Login or Register  to view this content.

The cell on this spreadsheet that is housing the drop down box is C4,D4,E4 merged together. Where do I need to reference this cell in the coding?

Thanks!

[davegugg]

Your post does not comply with Forum Rule 3: Use code tags when posting code. Please edit your post and add code tags. Once added, we can resume finding a solution for your problem.

[peachroses82]

I'm sorry. I didn't know how to do code tags. I figured it out. It has been edited

[royUK]

Not really an Excel database, all data should be stored in one master sheet to be used as a database

[davegugg]

While I'd agree most people throw around the term "Excel Database" too much, and it usually makes me cringe, I'm not sure I'd agree that all data has to be on one sheet for it to be a database. Relational Database concepts say that data should be normalized into different tables so as to minimize storing the same data in multiple places. I would think each Excel worksheet could be thought of as a "table" and the data could be joined together using indexes on a "main" sheet.

Anyway, use the worksheet change event and test to see which cell has been changed like this:

Please Login or Register  to view this content.

[peachroses82]

Okay, here is what I have so far:

Please Login or Register  to view this content.

However, whenever I try to change the value in the drop down box, the VB debugger opens up with an error message saying "Compile Error: Method or data member not found"

If I hit okay, the top line of code is highlighted yellow with an arrow and .ComboxBox in the third line of code is highlighted dark blue. I am really not sure what I did incorrectly. I copied and pasted.

[davegugg]

You're not using a combobox, you're using Cell C4, so:

Please Login or Register  to view this content.

This should go in the code for the worksheet containing Cell C4.

[peachroses82]

Fantastic!!!!!!!!!!!!!! It works. Thank you so much!!!

[royUK]

I agree with separate database sheets, e.g. I would have one for say Suppliers, one for Purchase etc. However, I don't think this is what the OP has