Data Query based on Button Clicks VBA help required

  • Hi,


    I have a planning dashboard which will provide the various aspects regarding the employee.


    There are 3 sheets


    Decision support tool
    9-Box distribution
    Emp Database


    In Emp Database WS is where all the Employee Database is stored. B35 to AB35. The same range for Decision support tool WS and 9-Box distribution WS.


    In Decision support tool WS I have created buttons for various Groups (Group2, Group3, Group4...)



    My Requirement is,


    Requirement 1


    In Decision support tool WS, when I select the Tower from the drop down F23 and its related Group in F24 and click on Run (button), I want to display the content in Columns B to O for the select tower and its related group. (The data should be picked from the table in Emp Database WS).


    In Decision support tool, For that selected Tower and for selected Group, when I click on the Buttons (Group2, Group3, Group4, Group5, Group6) based on the employee group the content should be displayed.


    For Example I have selected Tower1 in F23 and Tower_1_Team_2 in F24 and when I click on the any of the button (Group2, Group3, Group4, Group5, Group6), I want to display the content for that particular group which I have clicked.


    Is this possible?


    Requirement 2


    In Decision support tool, Columns Q to V and X to AB are updated manually by the managers.


    When they update the information in those columns and click on Update Data Button, I want the data for the selected Tower and selected Group employees data to be updated in the Emp Database Worksheet for those employees listed in the Decision support tool and for which the data is entered manually.



    Please help me out in this.


    Your Help in highly appreciated.


    Thanks in Advance.


    Sample attached.

  • Re: Data Query based on Button Clicks VBA help required


    Hi kumarma


    Will you ever have more than 9 Groups?


    What's the intended purpose of the Edit Mode Button?

  • Re: Data Query based on Button Clicks VBA help required


    Hi kumarma


    This Code in the attached appears to do as you require. I've assumed NO to this

    Quote

    Will you ever have more than 9 Groups?


    The Edit Mode Button has not been programed as I don't know it's intended purpose.

  • Re: Data Query based on Button Clicks VBA help required


    Hi Jaslake,


    Sorry for not responding, i was on vacation.
    Thanks for the code. It is working fine but help me in modifying the code as per my Requirement.


    Decision Support Tool (WS)


    1) There will not be more than 10 towers.
    2) Group Drop Down menu is not required for me. I will want to keep only the Tower Filter. (F23).
    3) When i select the tower from the Drop Down and depending upon the Group button click event it should show the required Group information and when i click on Show All it should show me all the Group Resources in that selected Tower.
    4) I have added one more list option to the tower saying 'All Towers'. When i select the this option it should show all towers resources based on the Group Button click.
    5) Column P & W contains Formulas, when i update the information in columns O to AB manually and on Button Click event i want the information to saved in Emp Database WS for that resources. (Right now as per the code it is taking only the last row information for the selected tower/group selected through drop down)
    6) Yes, Clear Data is not required.


    9 - Box distribution


    1) When i click on the Group Buttons provided the information should be picked from the Emp Database WS and should be displayed here


    Please help me in the modifications to the code.

  • Re: Data Query based on Button Clicks VBA help required


    Hi,


    Modification.


    No need to create any separate functionality for the 9-Box distribution (WS) based on the buttons, but i want the data to be displayed in this WS based on the Button Click Event (based on the Group selected) in the Decision support tool WS and just want that Group Button to be highlighted in the 9 Box distribution WS.


    Is this possible.


    If not just want to display the information in 9 Box distribution table, based on the Group button selected in the Decision support tool.

  • Re: Data Query based on Button Clicks VBA help required


    Hi Jaslake,


    Please ignore the above posts. I will let you know the updated modifications in some time. Sorry for the inconvenience.

  • Re: Data Query based on Button Clicks VBA help required


    Hi Jaslake,


    Please find the updated modifications needed to the code.


    Decision support tool WS


    In Decision support tool WS, I want the code to pull the information from the Employee Database WS based on the Tower and Team Drop Downs (F23 & F24) selected and update it from Column B to Column N


    Managers will update the information from Column O to Column AB (****Excluding the Column P, W, X --> as these columns contain formulas and they should not chage). Once they have updated the information and click on Update Date button i want the code to paste the values of that particular resource (Tower/Team selected in drop down) Paste Special --> Values From Column O to Column AB in Decision suppor tool WS to Employee Database


    Currently the code is updating only the last column information.


    9-Box distribution WS


    Based on the drop down for Tower/Team selected in F4 & F5 the data for that particular tower/group should be copied from Employee Database WS (Column B to Column AB) and paste special values in the table (Column B to Column AB).


    Please help in modifying the code based on the updated requirement.

  • Re: Data Query based on Button Clicks VBA help required


    Hi kumarma


    When Decision Support Tool is populated with the Run Command from Employee Data Base you indicate to populate only Columns B to N.

    Quote

    In Decision support tool WS, I want the code to pull the information from the Employee Database WS based on the Tower and Team Drop Downs (F23 & F24) selected and update it from Column B to Column N


    What if Data exists in Columns O through AB of Employee Database? Should it NOT be brought over to Decision Support Tool? If it's NOT brought over and the Manager does NOT populate a previously populated Field, the previous Data will be lost...or...am I missing something?

    Quote

    Managers will update the information from Column O to Column AB (****Excluding the Column P, W, X --> as these columns contain formulas and they should not chage). Once they have updated the information and click on Update Date button i want the code to paste the values of that particular resource (Tower/Team selected in drop down) Paste Special --> Values From Column O to Column AB in Decision suppor tool WS to Employee Database


    Only you know how you intend to use this.


    Edit: Currently the Update Button replaces the ENTIRE Row in Employee Database with the Row of Data from Decision Support Tool.

    Code
    1. Set c = rng.Find(cel.Value, , xlValues, xlWhole, xlByRows, xlNext, False)
    2. ws1.Range(ws1.Cells(cel.Row, "B"), ws1.Cells(cel.Row, LC1)).Copy
    3. ws.Cells(c.Row, "B").PasteSpecial (xlPasteValues)
  • Re: Data Query based on Button Clicks VBA help required


    Thanks Jaslake,


    Yes, when Decision Support Tool is populated with the Run Command from Employee Data Base I want to populate only Columns B to N.


    Quote

    When Decision Support Tool is populated with the Run Command from Employee Data Base you indicate to populate only Columns B to N.


    You are right, If data is available from Column O to Column AB (***Excluding Column P, W, X as it contains formulas) in Employee Database WS for that resources based on the drop down menu selection (I am assuming that manager has already updated the information for these resources), else i want to show only the information from column B to N and i am assuming that manager has not yet filled the information for that resources and he wanted to do so now.


    Quote

    [What if Data exists in Columns O through AB of Employee Database? Should it NOT be brought over to Decision Support Tool? If it's NOT brought over and the Manager does NOT populate a previously populated Field, the previous Data will be lost...or...am I missing something



    On Update Data button click I want all the rows information to be copied --> Paste Special --> Values to be pasted in employee database WS based on the drop down menu.


    Will the below code does that



    Code
    1. Set c = rng.Find(cel.Value, , xlValues, xlWhole, xlByRows, xlNext, False)
    2. ws1.Range(ws1.Cells(cel.Row, "B"), ws1.Cells(cel.Row, LC1)).Copy
    3. ws.Cells(c.Row, "B").PasteSpecial (xlPasteValues)


    Also please help in 9-Box distribution ws code as well.


    Thanks in advance.

  • Re: Data Query based on Button Clicks VBA help required


    Hi kumarma


    This is done..

    Quote

    Also please help in 9-Box distribution ws code as well.


    So, in reality, you want Columns B to Column AB (***Excluding Column P, W, X as it contains formulas) populated with data from Employee Database. There will be information in Columns O to AB (if it exists), or not (if it does not exist).


    Try the Code in the attached...let me know of issues

  • Re: Data Query based on Button Clicks VBA help required


    Hi Jaslake,


    Thank You very much. It worked like a charm.


    But i have a final request for you.


    I am trying to analyze for which group (based on the group buton clicks) i want to do a countif and count functions but as when i click on the Group buttons the data is filtered as per the Groups and i want to do the below.


    I am using a normal COUNTIF Function to know the count of "Box 9" I have placed this in one cell "I12" and i am using the formula.
    the data is being filtered with the column J.


    1)


    =COUNTIF($X$36:$X$16837,I12) [This formula is in J13]


    X36 - X16837 will contain the Box 9, Box 8, Box 7 and so on till Box 1


    2)


    =COUNTIF($X$36:$X$16837,I12)/COUNT($B$36:$B$16837) [this formula is in I13]


    My Requirement is whatever the Group Button i have click (it will be filtered by Column J) i want to get the above functions work when in filter mode. It should show the result of the above formulas


    Is this possible?

  • Re: Data Query based on Button Clicks VBA help required


    Hi kumarma


    I have no idea what you're asking for. What's this refer to...

    Quote

    Box 9, Box 8, Box 7 and so on till Box 1


    You might look at the Sub Total Function...


    Here's a link http://www.techonthenet.com/excel/formulas/subtotal.php


    Function number is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.


    1 or 101 AVERAGE
    2 or 102 COUNT
    3 or 103 COUNTA
    4 or 104 MAX
    5 or 105 MIN
    6 or 106 PRODUCT
    7 or 107 STDEV
    8 or 108 STDEVP
    9 or 109 SUM
    10 or 110 VAR
    11 or 111 VARP


    If you will post your File with a clear description of this new requirement I'll be glad to look at it. Any additional requirements should be in a new Thread.

  • Re: Data Query based on Button Clicks VBA help required


    Hi Jaslake,


    I was able to figure out a way to do this. I have done this by creating a dynamic named range and have base data sorted in a specific manner


    I have created an UDF to track the row height to know whether it is filtered out or not and used COUNTIFS function to do the trick.

    Quote


    Thank You so much for your support and the code worked like a charm and everything is working perfectly.


    I have a final small request,


    As of now the data is being filtered out as per the Tower and Group using drop downs.


    If i want to view the overall employees irrespective towers and groups.


    If a button be created 'Show All Employees' and when i click on that it will display all the employees data in there and i should be able to filter it out as per the Groups by using the Group Buttons.


    Also,


    I want to protect this VBA project with Password. I want this Project to be password Protected.
    Password --> IndiaN


    Please let me know if you want to create another thread.

  • Re: Data Query based on Button Clicks VBA help required


    Hi kumarma


    Add this Code to your button on Sheet1


    Add this Code to your button on Sheet2


    To protect the project:
    Alt + F11
    Select Tools
    Select VBAProject Properties
    Select Protection