Issue with Multiple cases in same sub

Hi Guys,

I'm having an issue with a sheet I'm making for work.

I have 40 tabs/sheets in it containing a variety of information for a number of systems we install. I have a systems options sheet with a number of dropdowns to configure the sheet according to the users need, hiding/unhiding tabs, rows and columns throughout the document. I've done this by using a case for each option in the drop downs and it's been working well.

However I've just updated it to add some new bits in including a dropdown for "export to customer" which hide's all the unnecessary information so the document can be PDF'd and given to the client at the end of the job. The problem I'm now having is that as part of the export option I've referenced cells on every sheet to hide and unhide, by default the export option is set no meaning all information is visible. This is where my problem is, as some of the systems we install use some common hardware so share tabs. So I'm selecting the system type in one dropdown which should as an example hide columns F:K, show L:Q and hide R:Z based on selecting system A of the first drop down (columns would be different for system B, C ect). But the information in F:Z isn't something the customer needs to see so I've got the export case set to hide/unhide all columns within that range. So now regardless of what system I select in the other drop downs the case for export is overriding them and keeping them hidden.

So my question is, can I.......

A) isolate the export case I've created somehow within the sub so excel will ignore it unless the export dropdown is used (selecting one dropdown seems to run through the case options for all of the other dropdowns).
B) run the export case in a second sub so the rest of the options in the original sub will ignore it
C) Insert a drop down as an item (like an action button) that we be completely separate from the other dropdowns (done using the data validation list function)

I'm still kind of new to all this so if you can think of a better way of doing it I'm open to any suggestions. I can't post the document on here unfortunately as it contains some sensitive information and allot of work had gone in to it so my company doesn't want our competitors to get hold of it. But if needed I can post a section of the VBA code so you can see how I've gone about it.

Originally Posted by mjb0810

I can't post the document on here unfortunately as it contains some sensitive information and allot of work had gone in to it so my company doesn't want our competitors to get hold of it. But if needed I can post a section of the VBA code so you can see how I've gone about it.

Whilst I understand you wanting to keep the effort invested from competitors, I don't think you're going to get very far if we cannot see the workbook I'm afraid.
It doesn't sound particularly straightforward so trying to envisage the data layout from what you've described is not exactly easy.

BSB

Originally Posted by BadlySpelledBuoy

Whilst I understand you wanting to keep the effort invested from competitors, I don't think you're going to get very far if we cannot see the workbook I'm afraid.
It doesn't sound particularly straightforward so trying to envisage the data layout from what you've described is not exactly easy.

BSB

Yeah I know, unfortunately it's not the simplest of documents to try and explain. I've got 40 tabs on there so far and have sunk about 170 hours in to it trying to work the way we need it to. If I can just solve the export issue I'll be in a very good place with it.

I'll try to explain a bit better below.

This is a screenshot from the options page, each drop down has a names cell reference which is then referenced as a select argument in VBA with a case for each option from the drop down.
1.JPG

This is an example of one of the cased which hides/shows the information relevant to the selection
2.JPG

The select's for each dropdown are all contained in the same sub as per the example below
3.JPG

The problem I'm having is that whenever one of the dropdowns is changes it seems to fire off the code for the rest of them as well, not just the one that has been changed. If there's a way of separating the select arguments so it will only fire the changes for the option that's been changed it will solve my current issue.

I'm wondering if it can be done using combobox or a list box with the export code contained within a section for the item rather than just in the sheet. I've currently got the export to PDF automated from an action button and that only triggers when clicked so thinking this could be the answer but not 100% how to lay out the VBA code for those items.

I can put together a basic representation of the sheet tomorrow and share it if needed to give a better idea of how it works. For some reason I couldn't copy the code straight in to the reply so had to use screenshots.

Originally Posted by BadlySpelledBuoy

If I've understood correctly then a worksheet change event should help.

Let's assume the 6 dropdowns in the screenshot are named ranges called Option1, Option2, Option3 etc.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("Option1") Is Nothing Then
        'INSERT RELEVANT CODE FOR OPTION1 HERE
    ElseIf Not Intersect(Target, Range("Option2") Is Nothing Then
        'INSERT RELEVANT CODE FOR OPTION2 HERE
    ElseIf Not Intersect(Target, Range("Option3") Is Nothing Then
        'INSERT RELEVANT CODE FOR OPTION3 HERE

    'etc.
End Sub

Does that help at all?

BSB

I've shut my laptop down now but will give it a try in the morning and let you know if it's solved it.

Hi BSB,

After a little bit of playing around I have got it working and it has solved the issue of the code clashing. I'm now having a different issues, the changes to the sheet aren't triggering when the dropdown is selected like before. Now I'm having to make the selection, then click off of the drop down then back on to it and that's when it triggers. Is there a way to tweak the code so it updates when a selection is made?