Solved

Excel Macro Help- need a macro that runs based on a drop-down data validation field

Posted on 2013-01-04
39
286 Views
Last Modified: 2013-01-14
Hi Experts,

Please open the attached example and follow along:

I would like a Macro that sorts data based on the following:

If someone selects AMF under "select organization" I would like only the names with 1 as the "org code" (see Data1 and Data2 worksheets) to report. If someone selects BO only "org code" 2, BPFTI "org code" 3, etc... (I can add the orgs. to the macro as needed once I have the syntax)

Further, if someone selects "Non-Compliant" I would like only the names with 0 as the "times completed" to report. If someone selects "Compliant" I would only like the names with 1 in the "times completed" to report.

Here is where its gets a little tricky... the real-data's worksheets are not called "data 1" and "data 2" the worksheet names change from the source database on each export. So the Macro has to run on ALL worksheets in the workbook.
HOWEVER, the field "times completed" is not always in column F, sometimes it is in column E.

I'm not sure what the best solution is give that little caveat... I'm open to having 2 macros, one that runs in the case that it is in column F and one that runs in the case that it is column E.

Lastly, I do no want to run the macro until both drop-down lists are selected. And I would also like to have the macro unhide "Data 1" and "Data 2" after it has completed running. I have left those worksheets visible for the purpose of this question.

Thanks in advance!
MacroHelpExpertsExchange.xlsx
0
Comment
Question by:-Polak
  • 21
  • 18
39 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
You did not say where and how you want the report of the selection to be like when the user select AMF you say:
I would like only the names with 1 as the "org code" (see Data1 and Data2 worksheets) to report.
>> To report where and how in what format ? In hte first sheet in Col C ??? not clear.

Then when you say Col F is not always the same ... that's not a problem does the header like the first row of that col is the same so we can locate it ???
gowflow
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
>> To report where and how in what format ? In hte first sheet in Col C ??? not clear.
I would just like the Macro to Sort/Only Display the corresponding information on the worksheets Data1 and Data2. (please note the request to have Data 1 and Data 2 hidden until the macro runs
Basically: Select Organization, Select Compliance, Run Macro, have Data1, Data 2 unhide and only have the pertenant information displayed based on the selections.

Then when you say Col F is not always the same ... that's not a problem does the header like the first row of that col is the same so we can locate it ???
The title of the column header is always "Times Completed" if you can locate that way. It is always in Row 1.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Basically: Select Organization, Select Compliance, Run Macro, have Data1, Data 2 unhide and only have the pertenant information displayed based on the selections.

>>> Data1 and Data2 are not hidden !!! you want them hidden by default and when the user selects they got unhidden ??

that's all I need for now.
gowflow
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Yes, they will be hidden in the final workbook that I add the finished macro to. So esentially the Macro will have to have an "Unhide" on all applicable worksheets after sorting.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok chk out the file and let me know if this is what you want. Make sure your macroes are enabled.

I only changed a small thing I inverted Compliant and non-compliant in the Menu sheet that all.

gowflow
MacroHelpExpertsExchange.xlsm
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Yes, thats exactly the result I'm looking for. However, you did miss two points that I made:

the real-data's worksheets are not called "data 1" and "data 2" the worksheet names change from the source database on each export. So the Macro has to run on ALL worksheets in the workbook.

Meaning that in the non-dummy data the worksheets export as different names each time so I just need the marco to run on all active sheets.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
no problem just 1 question:
will the workbook always have 4 sheets ? Menu SelectPMO and the 2 other sheets ?
gowflow
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
there could be Data 1-5 or even 1-10 depending on how someone exports from the source database.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok fine but for sure you will have Menu and SelectPMO at least !!!! as they are a reference. I noticed also a small bug that has been addressed.

This version will go thru as many as there is worksheets up to the maximum that Excel allows and will apply the criterias you advised.

Pls check it thourouly and let me know.
gowflow
MacroHelpExpertsExchange-AllShee.xlsm
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Okay, everything seems to work correctly. Now I have to try to apply this to the Real-Data....

Looking at the Macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
'Dim WSData1 As Worksheet
'Dim WSData2 As Worksheet
Dim WSActive As Worksheet
Dim WSData As Worksheet
Dim Itm As Long
Dim FilterOff As Boolean
Dim c As Range

Set WS = Sheets("Menu")
Set WSActive = ActiveSheet
'Set WSData1 = Sheets("Data1")
'Set WSData2 = Sheets("Data2")
    
If WSActive.Range("A2").Value <> "" And WSActive.Range("B2").Value <> "" Then
    
    If WSActive.Range("A2").Value <> "" Then
        Set c = WS.Range("A:A").Find(What:=WSActive.Range("A2").Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            For Each WSData In ActiveWorkbook.Worksheets
                If WSData.Name <> "Menu" And WSData.Name <> "SelectPMO" Then
                    Filter WSData, 2, c.Row, FilterOff
                    'Filter WSData1, 2, c.Row, FilterOff
                    'Filter WSData2, 2, c.Row, FilterOff
                End If
            Next WSData
        End If
    
    End If

    
    If WSActive.Range("B2").Value <> "" Then
        Set c = WS.Range("B:B").Find(What:=WSActive.Range("B2").Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            If c.Row = 1 Then Itm = 0
            If c.Row = 2 Then Itm = 1
            For Each WSData In ActiveWorkbook.Worksheets
                If WSData.Name <> "Menu" And WSData.Name <> "SelectPMO" Then
                    Filter WSData, 6, Itm, FilterOff
                    'Filter WSData1, 6, Itm, FilterOff
                    'Filter WSData2, 6, Itm, FilterOff
                End If
            Next WSData
        End If
    Else
        If WSActive.Range("A2").Value = "" And WSActive.Range("B2").Value = "" Then
            FilterOff = True
        End If
        For Each WSData In ActiveWorkbook.Worksheets
            If WSData.Name <> "Menu" And WSData.Name <> "SelectPMO" Then
                Filter WSData, 6, -1, FilterOff
                'Filter WSData1, 6, -1, FilterOff
                'Filter WSData2, 6, -1, FilterOff
            End If
        Next WSData
    End If

Else
    FilterOff = True
    For Each WSData In ActiveWorkbook.Worksheets
        If WSData.Name <> "Menu" And WSData.Name <> "SelectPMO" Then
            Filter WSData, 2, -1, FilterOff
            'Filter WSData1, 2, -1, FilterOff
            'Filter WSData2, 2, -1, FilterOff
        End If
    Next WSData
    
End If

End Sub

Sub Filter(WS As Worksheet, Col As Long, Item As Long, FltrOff As Boolean)
Dim c As Range

If Col = 6 Then
    Set c = WS.Range("1:1").Find(What:="Times Completed", LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
        Col = c.Column
    End If
    
End If
If Item <> -1 Then
    WS.Range(WS.Cells(2, Col), WS.Cells(2, Col)).AutoFilter field:=Col, Criteria1:=Item
Else
    WS.Range(WS.Cells(2, Col), WS.Cells(2, Col)).AutoFilter field:=Col
End If

If FltrOff Then WS.AutoFilterMode = False

End Sub

Open in new window


What lines in that code are stipulating that AMF = 1 BO = 2 BPFTI = 3? Sorry, I thought the code would be a little easier to understand....
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
no problem I did not code anything it is all flexible and that's my way of doing.
If you look at sheet Menu you have your col A and it is the row coresponding to the data that is taken to figure out the 1 and 2 etc ...

So as long as you follow this principle no matter how many names you put it will be fine as long as you use the row as code to the item you create.

Is this fine for you ?>
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Did you implement in your work did the solution work for you ? any help needed pls do not hesitate to let me know.
gowflow
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
no problem I did not code anything it is all flexible and that's my way of doing.
If you look at sheet Menu you have your col A and it is the row coresponding to the data that is taken to figure out the 1 and 2 etc ...

So as long as you follow this principle no matter how many names you put it will be fine as long as you use the row as code to the item you create.

Is this fine for you ?>

I see what you did, no this will not work, this is because in the real data "AMF" has to equal several numbers not just "1" or "2".

The rules for the real data are as follows:
AMF Equals:
2804040000000000  
2804040001000000  
2804040003000000
2804040004000000
2804040005000000
2804040006000000

BO Equals:
2804000200000000

BPFTI Equals:
2804050000000000
2804050001000000
2804050001010000
2804050001020000
2804050002000000
2804050002040000
2804050002050000
2804050002060000
2804050003000000
2804050003030000
2804050003040000
2804050004000000
2804050004010000
2804050004020000
2804050004030000

EMO Equals:
2804010000000000
2804010001000000
2804010001010000
2804010001020000
2804010001030000
2804010004000000
2804010004010000
2804010004020000
2804010004030000
2804010005000000

FOF Equals:
2804060000000000
2804060002000000
2804060002010000
2804060002040000
2804060002050000
2804060002060000
2804060002070000
2804060003000000

MSF Equals:
2804030000000000
2804030002000000
2804030002010000
2804030002020000
2804030004000000
2804030004010000
2804030004020000
2804030004030000
2804030004040000
2804030006000000

ORS Equals:
2804020000000000
2804020001000000
2804020002000000
2804020002010000
2804020002020000
2804020002030000
2804020002040000

XO Equals:
2804000100000000

XD Equals:
2804000000000000

Sorry I didn't make this clear initally, my thought was that you would code AMF="1" into the macro so that I could just modfiy the macro for the production version.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Again no need to code. here is my suggestion in sheet Menu like in the attached file we can use Col C to be min code number and Col D to be Max Code number so the C-D would be the range of codes for each item in Col A. This solution is flexible for you as you can add codes ... check how I filled them (took it from what you posted) and if it is ok then I will modify the code to work with these codes.

Note: this file is only to look at sheet Menu nothing has been modifed in the code.
Let me know.
gowflow
Copy-of-MacroHelpExpertsExchange.xlsm
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Yes, that will work just as well!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Try this version of the file does it meet the new requirement ?
gowflow
MacroHelpExpertsExchange-AllShee.xlsm
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Doesn't seem to be working properly, I've tried to code the frist 3 rows in Data 1 as AMF and the macro does not seem to filter them properlly.
MacroHelpExpertsExchange-AllShee.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I think this should do it. I changed the formating for both C and D columns in Menu to be Number format with 0 decimals. You will need to format all the Code columns in your workbook to be the same
Number
0 Decimals.

Let me know.
gowflow
MacroHelpEE-NEW.xlsm
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
That's understandable.
Please see the attached for the last changes, I tried to change around your code to accomodate the headers in the real version but I failed. Your code is unaltered here. Please reposition where the macro runs the filter.

Also, please add in something to the macro that unhides the sheets which it filtered after it has completed running.
MacroHelpEE-NEW.xlsm
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Also, please add in something to the macro that unhides the sheets which it filtered after it has completed running.
>>> What do you mean by that ???? once both fields are filled it runs the filter and if you want to remove the filter just delete 1 of the fields data either Col A or Col B

working on hte other issue. Menatime how did you paste the data ? still text this is why it is not working well !!! you need to copy/paste from Menu the item that you want and then modify it in the sheet.

This is a problem with Excel as after 16 digits all numbers turn to Exponetial so we hv to twaist the things to get around that.

Anyway I got an error here and checking it.

LAST BUT NOT LEAST:
If you had supplied the final format since the beginning we would not hv waisted all this time. I give solutions that works coz I test them thouroughly ! so if you give me part of the story and then you want to change then it does not work with you then we are both waisting time.

gowflow
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
LAST BUT NOT LEAST
Yes, sorry about that, I really thought the macro would be easier for me to change around then it ended up being. I apologize for that immensely.

Menatime how did you paste the data ?
I used the "Move and Copy" feature and just moved the worksheet from the real-report to your workbook.

Regarding the numbers, in the real-report, sometimes the formatting on the Org. Code field is "General", sometimes it's "Number" and sometimes it's "Text". I am comfortable with changing the formatting on the fields to "Number" (no decimals) but that doesn't change over all of the cells to actual numbers... why is this?

What do you mean by that ???? once both fields are filled it runs the filter and if you want to remove the filter just delete 1 of the fields data either Col A or Col B

When I send out the the final version of this workbook for distribution, worksheets Dummy 1 and Dummy 2 .... 3, 4, 5, 6, 7 etc... will be hidden worksheets. I want the macro to unhide the worksheets after selections have been made in "Select PMO" and "Select Compliance"
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Try this version
gowflow
MacroHelpEE-NEWUser.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
When I send out the the final version of this workbook for distribution, worksheets Dummy 1 and Dummy 2 .... 3, 4, 5, 6, 7 etc... will be hidden worksheets. I want the macro to unhide the worksheets after selections have been made in "Select PMO" and "Select Compliance"

>> Well this too I did not know. So to cut it short test the version I posted then tell how it is and also tell me if there is a password to the sheets.
will wait for your feedback before making any further change.

gowflow
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
That version works perfectly. Thank You. How did you manage to change over the Org. Code cells which were formatted as text to numbers so quickly?

Can this be include into the macro so that I do not have to reformat the original report?
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
You should ask for a Million Dollar !!!

Well the version I send you I did manually, but figured out that you may hv zillions of records so I included a macro that will clean the whole thing out.

Pls follow these basics:
1) When there is code in a module, leave it in a module don't copy it onto a worksheet as it may mix the references.
2) If there is a button on a worksheet that calls a macro and you don't want that button then simply delete the button but before that remember what is the macro that is lying under that button so you can activate it manually.

3) To implement your production:

3.1) Open this workbook and DO NOT ACTIVATE MACROES.
3.2) do as you did before and move all your data sheets into this workbook and once finished save the workbook and close it.
3.3) Make a backup copy of the workbook and give it a new name, case when you run the macro if something goes wild then you hv your workbook saved under the new format and intact.
3.4) Activate Macroes for a copy of the new workbook (I say Activate as usually for security reasons your macroes should be all disabled with notification)
3.5) Run the button in sheet SelectPMO it will format all your 'pertinent' worksheets in the workbook for the Columns Org Code.
3.6) Test the workbook with selections.

Ket me know your feedback.


1 Last Issue before I send the final version, it is still not clear to me the status of the worksheets (hid/unhide)
My understanding correct me if I am wrong when you open the workbook all sheets are hidden Except Menu and SelectPMO. Is that correct ? and when you make a selection you want to unhide the data sheets ?

So if this is what you eant you need to tell me what sheet to unhide ? Only the ones that contain results or all of them ? and when to hide them again ?? as this is not clear to me.

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Pls answer my questions for the Hide/Unhide worksheets so I can finalise it and send it to you.
gowflow
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
3) To implement your production:

3.1) Open this workbook and DO NOT ACTIVATE MACROES.

You forgot the attachment
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well if you read the whole thing you will see that I am waiting for your answer before attaching the workbook !!!!

gowflow
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Gotcha, all of the sheets, even the ones without results.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I REiterate the questions:

==============
1 Last Issue before I send the final version, it is still not clear to me the status of the worksheets (hid/unhide)
My understanding correct me if I am wrong when you open the workbook all sheets are hidden Except Menu and SelectPMO. Is that correct ? and when you make a selection you want to unhide the data sheets ?

So if this is what you eant you need to tell me what sheet to unhide ? Only the ones that contain results or all of them ? and when to hide them again ?? as this is not clear to me.
===============

Pls answer each and every question.
gowflow
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
I'd like only SelectPMO to be visible, but if "Menu" has to be visable, i'm okay with that.

Sheets never need to be hidden again after inital run.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
intiial run is always relative !!! so what is the use of hiding them if after the first run you don't want them to be hidden.

It seems you cannot envision the end result. I will help you.
What you want is when you start the workbook to hv all sheets hidden (menu should be unhidden to allow to add data if and when you need, unless it is final and finshed and no need to add anymore data to this sheet then we can permanantly hide it. YOU TELL ME.

So after the workbook started all is hidden except SelectPMO then when selection are performed, then all sheets unhidden up until you close the workbook. When you close the workbook they will become again all hidden. For the next opening.

Is that what you want ???
gowflow
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
You'll never need to modify Menu.

Exactly what I want.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Here it is
gowflow
MacroHelpEE-Final.xlsm
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Thank You gowflow, I've done my acceptance based testing and discovered one small-glitch.

There is some data input inside of row 6 in the header of the real data that I have removed for our build-out. When there is data input into row 6 the filter does not locate correctly. Is there anything you can do about this?

See worksheet "Testing 1"
EE-Glitch.xlsm
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
One more Glitch noticed, if there is any data in Column C below row 7 the filter places itself in row 8 rather than row7
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Hope that it will take care of the 'reported' Glitches !!! Tell me if u find more hick-ups. That's great it is called REAL testing that very few people care to do.
gowflow
EE-Glitch.xlsm
0
 
LVL 1

Author Closing Comment

by:-Polak
Comment Utility
Thank you for your patience with me on something that required a lot of reiteration. You solution is excellent and meets all of my requirements.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I'm gald it finally met what you wanted and trust me my satissfaction is to get the user get EXACTLY what he wants and nothing less.
gowflow
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now