Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs down

    Very rusty w/ my VBA, (haven't used it in many years) - have much of this file working but desperately need help with 2 things (as noted in Title):

    SUMMARY: (2 issues needing resolution)
    (1) hide the UserForm and take user to the OUTPUT sheet after they click the "Generate Reports" button.
    (2) activate the 2nd criteria button on the UserForm by adding code that won't conflict w/ the first button's code. (tried it - but got errors)
    =============================================

    DETAILS/BACKGROUND:
    Here's the anticipated steps for User:
    Open file, dialog box opens (Welcome UserForm1)
    #1- Welcome form provides them 2 criteria filtering buttons (currently coded for 1...need to know how to make the 2nd button work (add to existing code)).

    ==The top one has them select which 'BlockPoint' they wish to filter down to.. (this looks to Column E on the DROPDOWNS sheet)
    ==The 2nd combo has them select which 'Status' they wish (i.e. "Pending"). (this looks to Column D on the DROPDOWNS sheet)

    #2- Once they hit "Generate Report" it would take them to the results page (OUTPUT)
    (they can run their mtg using this, or print as desired) (right now, it generates but does not take them "show" that Output page)

    #3- Then, if they want to change the criteria (I provided an icon/image at the top of both sheet to provide the user quick access back to the criteria filtering buttons (found on the Welcome UserForm1) allowing them to select a different Blockpoint (like "BP2") and perhaps only approved items ("yes") rather than ("Pending).


    (so, far, I've got 1, PART of 2,and PART of 3 working)
    * Generate button on UserForm1 (generates) - but need to also have user taken to the OUTPUT sheet.
    * Last, I need to give the user to use both command buttons on the welcome UserForm.
    The top one works (to select "BlockPoint" criteria) for filtering.... but how do I add to the existing code to make the 2nd command button work to further filter output results "Status" (using Column H data within the DROPDOWNS sheet).


    Since the boss needed it for today - any help possible is MUCH APPRECIATED to get them going for the agile development tracking meetings.

    Thanks so much! Chris
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    To get the second drop down to populate correctly you need to edit the code in the user form as follows (the bold part is the only change)
    Private Sub UserForm_Activate()
    ComboBox1.List = Range("AppliesTo").Value
    ComboBox1.Value = "--- Select Report Criteria ---"
    ComboBox2.List = Range("Approved").Value
    ComboBox2.Value = "--- Select Status Criteria ---"
    End Sub

    To change the active sheet at the end of the CommandButton1_Click code and close the userform you need to add the following lines in front of the End Sub for this subroutine.
    ActiveWorkbook.Sheets("OUTPUT_REPORT").Activate
    Me.Hide

    Your code is searching the Column D rather than Column I in the AGILE_CAPTURE_LOG sheet - change this to by changing a letter in the line in the code which should now say
    Set Rng = Worksheets("AGILE_CAPTURE_LOG").Range("I3")

    Finally, the way the code includes all BP1 items is by design - it is meant to be a catch all for all BP1.x items rather than exactly catching BP1 but excluding any BP1.x. If you adjust your understanding of what BP1 means then you don't need to change the code to specifically find only BP1. If you can't get over this, remove BP1 from the pick list so it can't be selected.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    New Lounger
    Join Date
    Sep 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wink

    [quote name='Andrew Lockton' post='792638' date='09-Sep-2009 00:00']To get the second drop down to populate correctly you need to edit the code in the user form as follows (the bold part is the only change)
    Private Sub UserForm_Activate()
    ComboBox1.List = Range("AppliesTo").Value
    ComboBox1.Value = "--- Select Report Criteria ---"
    ComboBox2.List = Range("Approved").Value
    ComboBox2.Value = "--- Select Status Criteria ---"
    End Sub

    To change the active sheet at the end of the CommandButton1_Click code and close the userform you need to add the following lines in front of the End Sub for this subroutine.
    ActiveWorkbook.Sheets("OUTPUT_REPORT").Activate
    Me.Hide

    Your code is searching the Column D rather than Column I in the AGILE_CAPTURE_LOG sheet - change this to by changing a letter in the line in the code which should now say
    Set Rng = Worksheets("AGILE_CAPTURE_LOG").Range("I3")

    Finally, the way the code includes all BP1 items is by design - it is meant to be a catch all for all BP1.x items rather than exactly catching BP1 but excluding any BP1.x. If you adjust your understanding of what BP1 means then you don't need to change the code to specifically find only BP1. If you can't get over this, remove BP1 from the pick list so it can't be selected.[/quote]

    ====
    THANKS Andrew SOO much for explaining what I need to do to correct the issues! I can't wait to implement the changes!
    THANK YOU -- THANK YOU -- THANK YOU for your expeditious and most helpful response!
    Chris

  4. #4
    New Lounger
    Join Date
    Sep 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    [quote name='Skeeder' post='792639' date='09-Sep-2009 00:07']====
    THANKS Andrew SOO much for explaining what I need to do to correct the issues! I can't wait to implement the changes!
    THANK YOU -- THANK YOU -- THANK YOU for your expeditious and most helpful response!
    Chris[/quote]

    ====
    Found a problem ---
    "BP 1.0" was selected w/ button 1
    "Pending" was selected w/ button 2
    but for some reason a "No" line item slipped through and is found on the OUTPUT report?
    Attached Files Attached Files

  5. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    [quote name='Skeeder' post='792642' date='09-Sep-2009 15:33']Found a problem ---
    "BP 1.0" was selected w/ button 1
    "Pending" was selected w/ button 2
    but for some reason a "No" line item slipped through and is found on the OUTPUT report?[/quote]The Yes/No/Pending is NOT part of the search in the code you have. There needs to be a nested loop added to check the second parameter on the lines where there is a hit.

    It strikes me that you don't need any of this code - you could be using the autofilter and it would be much simpler to handle this task. Is there a good reason for not doing this or have you never seen this feature?

    The coding alterations are a bit trickier - the following code has a few changes but should do the job
    Code:
    Private Sub CommandButton1_Click()
     'GENERATE REPORT
     
      Dim Cell As Range
      Dim OutRng As Range
      Dim R As Long
      Dim RegExp As Object
      Dim Rng As Range
      Dim RngEnd As Range
      Dim What As String
      Dim What2 As String
      
      What = ComboBox1.Value
      What2 = ComboBox2.Value
      Select Case What
    	Case Is = "", " ======", "--- Select BLOCKPOINT Criteria ---"
    	  Exit Sub
      End Select
      
      Set Rng = Worksheets("AGILE_CAPTURE_LOG").Range("I3")
      Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
      Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))
      Set OutRng = Worksheets("OUTPUT_REPORT").UsedRange
      OutRng.Offset(1, 0).ClearContents
      Set OutRng = OutRng.Cells(2, 1)
      Set RegExp = CreateObject("VBScript.RegExp")
      RegExp.Global = False
      RegExp.IgnoreCase = True
      RegExp.Pattern = "^(" & What & "\b)|" & "(\b" & What & "\b)|" _
    				   & "(\b" & What & ")$"
    	
      For Each Cell In Rng
    	If RegExp.Test(Cell) Then
    	  If Cell.Offset(0, -1).Value = What2 Then
    	   Cell.EntireRow.Copy Destination:=OutRng.Offset(R, 0)
    	   R = R + 1
    	  End If
    	End If
      Next Cell
    	
      Set RegExp = Nothing
    
    'The next 2 lines tells it to close the UserForm after the GENERATE button is clicked and moves user to the named sheet.
      ActiveWorkbook.Sheets("OUTPUT_REPORT").Activate
      Me.Hide
    
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #6
    New Lounger
    Join Date
    Sep 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    You are ***AWESOME*** everything is working except for the header -- is not feeding over to the OUTPUT sheet for some reason?
    What do I need to add -- to bring both the header (row 2 of the AGILE_CAPTURE_LOG) and the filtered content to the OUTPUT sheet?

    Yes, I'm very familiar w/ the AutoFilter feature (very handy for those who know how to use them) - but, I'm handing over this tool to some folks that are not that familiar w/ Excel features...
    So, the need for a quick way to enter line items (on the AGILE sheet)
    and a need for quick pulling of filtered reports (found on the OUTPUT sheet) was the only way to avoid head-aches during fast-paced agile development/review/approval meetings.

    This will be a HUGE help -- I can't tell you HOW much I appreciate the help in refreshing my memory on how to code this stuff...
    (used to be the office expert on it all -- many, MANY yrs ago -- but changed work-load pulled me away from VBA and now I'm upset to see I've forgotten SO much) FRUSTRATING!!
    So, I've been taking old tools of mine and picking and choosing pcs of code to re-use -- but having trouble making it flow and play nicely together!

    If you know what the piece is--- that I'm missing to make it feed the column header over - I'd greatly appreciate the help finalizing the task...
    Would I add it in here -- or module?

    [codebox]Private Sub CommandButton1_Click()
    'GENERATE REPORT

    Dim Cell As Range
    Dim OutRng As Range
    Dim R As Long
    Dim RegExp As Object
    Dim Rng As Range
    Dim RngEnd As Range
    Dim What As String
    Dim What2 As String

    What = ComboBox1.Value
    What2 = ComboBox2.Value
    Select Case What
    Case Is = "", " ======", "--- Select BLOCKPOINT Criteria ---"
    Exit Sub
    End Select

    Set Rng = Worksheets("AGILE_CAPTURE_LOG").Range("I3")
    Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))
    Set OutRng = Worksheets("OUTPUT_REPORT").UsedRange
    OutRng.Offset(1, 0).ClearContents
    Set OutRng = OutRng.Cells(2, 1)

    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.Global = False
    RegExp.IgnoreCase = True
    RegExp.Pattern = "^(" & What & "\|" & "(\b" & What & "\|" _
    & "(\b" & What & ")$"

    For Each Cell In Rng
    If RegExp.Test(Cell) Then
    If Cell.Offset(0, -1).Value = What2 Then
    Cell.EntireRow.Copy Destination:=OutRng.Offset(R, 0)
    R = R + 1
    End If
    End If
    Next Cell

    Set RegExp = Nothing

    'The next 2 lines tells it to close the UserForm after the GENERATE button is clicked and moves user to the named sheet.
    ActiveWorkbook.Sheets("OUTPUT_REPORT").Activate
    Me.Hide

    End Sub[/codebox]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •