Page 1 of 2 12 LastLast
Results 1 to 10 of 15

VBA - pop up window to return a list of data based on cells

This is a discussion on VBA - pop up window to return a list of data based on cells within the Excel Questions forums, part of the Question Forums category; Hello all New request this morning. I've been trying all sorts of things with formula and I think I'm going ...

  1. #1
    New Member
    Join Date
    Jun 2017
    Posts
    26

    Default VBA - pop up window to return a list of data based on cells

    Hello all

    New request this morning. I've been trying all sorts of things with formula and I think I'm going to need VBA to get this task done. I've got minimal experience using VBA so I need some help. Here's what I'm trying to achieve;

    I would like for a pop up window to appear and list the contents of some cells which have a FALSE statement in another column.

    Column B has a large list of labels (a huge mix of text essentially but I'll keep it simple with Label 123). Column B's text doesn't have any patterns. The text can be separated by some blank cells, and sometimes not, sometimes quite a few blank cells before the next cell with text. This is the data I want to appear in the pop up box but only if column E contains a FALSE in the corresponding row.

    For example;

    A B C D E
    GROUP 1 - Enable
    LABEL 1 TRUE
    LABEL 2 FALSE
    LABEL 3 FALSE
    LABEL 4 TRUE
    GROUP 2 - Condition
    LABEL 5 TRUE
    LABEL 6 FALSE

    So in the above example, the pop up window would have a message that says something like " you have differences;" followed by a list of the FALSE conditions. To add to the complexity, the data sets are in groups. I'd like to quote the group number above the label differences in BOLD, the group description has been simplified for this example but is a complex naming structure that has no pattern such as group 1, group 2 etc.

    There are ~450 labels in total over ~1400 rows, 7 groups in total, again all with no pattern to how they are arranged. Followed by a final statement along the lines of "do you agree with the differences?". The above condition would display something like this:

    You have differences;

    GROUP 1 - Enable
    LABEL 2
    LABEL 3

    GROUP 2 - Condition
    LABEL 6

    Do you Agree with the differences?

    For background. I'm comparing multiple data-sets with large amounts of data and I'd like a very simple window to aid the comparison and highlight differences in the data-set. There will almost always be differences but the aim to is try and reduce those over time so the FALSE statement will change to TRUE's meaning we have a more common approach.

    Any questions or thoughts?

    I can provide my document if required however I'd rather not make it public info.

    Many Thanks for your help

  2. #2
    Board Regular
    Join Date
    Sep 2012
    Location
    Melbourne Australia
    Posts
    1,503

    Default Re: VBA - pop up window to return a list of data based on cells

    You could do this with a pivot table.
    Select your data, insert pivot table. Put the label in the row section and the section with the true or false in the filter. then ok.
    Go to the pivot and select the filter and choose false, this will show all the items that are false with their labels.

    Otherwise would a simple filter work?

    The alternative in VBA would be to create forms and write code to capture the information. Creating the forms will take longer than the pivot tables but its up to you.
    Write a man a macro he is happy for the day....teach a man to write a macro, he'll be happy forever!

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Posts
    12,114

    Default Re: VBA - pop up window to return a list of data based on cells

    Try this:-
    Results in Msgbox.
    Code:
    Sub MG19Jun25
    Dim Rng As Range, Dn As Range, Msg As String, Temp As String
    Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    For Each Dn In Rng
        If Not IsEmpty(Dn.Offset(, -1).Value) Then
            Temp = Dn.Offset(, -1)
            If Not Msg = "" Then Msg = Msg & vbLf
            Msg = Msg & Temp & vbLf
        End If
        If Dn.Offset(, 3).Value = "False" Then
            Msg = Msg & Dn.Value & vbLf
        End If
    Next Dn
    MsgBox Msg
    End Sub
    Regards Mick

  4. #4
    Board Regular
    Join Date
    Jan 2008
    Posts
    12,114

    Default Re: VBA - pop up window to return a list of data based on cells

    NB:- If you have a large amount of data, you could create a userform with a listbox.
    The code below will load the required data into Listbox1 of the Userform.
    Code:
    Sub MG19Jun33
    Option Explicit
    Private Sub UserForm_Initialize()
    Dim Rng As Range, Dn As Range, Msg As String, Temp As String
    Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    For Each Dn In Rng
        If Not IsEmpty(Dn.Offset(, -1).Value) Then
            Temp = Dn.Offset(, -1)
            If Not Msg = "" Then Msg = Msg & vbLf
            Msg = Msg & Temp & vbLf
        End If
        If Dn.Offset(, 3).Value = "False" Then
            Msg = Msg & Dn.Value & vbLf
        End If
    Next Dn
    ListBox1.List = Split(Msg, vbLf)
    End Sub
    End Sub
    Regards Mick

  5. #5
    New Member
    Join Date
    Jun 2017
    Posts
    26

    Default Re: VBA - pop up window to return a list of data based on cells

    Quote Originally Posted by MickG View Post
    Try this:-
    Results in Msgbox.
    Code:
    Sub MG19Jun25
    Dim Rng As Range, Dn As Range, Msg As String, Temp As String
    Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    For Each Dn In Rng
        If Not IsEmpty(Dn.Offset(, -1).Value) Then
            Temp = Dn.Offset(, -1)
            If Not Msg = "" Then Msg = Msg & vbLf
            Msg = Msg & Temp & vbLf
        End If
        If Dn.Offset(, 3).Value = "False" Then
            Msg = Msg & Dn.Value & vbLf
        End If
    Next Dn
    MsgBox Msg
    End Sub
    Regards Mick
    Almost perfect...thank you.

    Only problem is that there are quote a few 'FALSE' statement so my list is long and has pushed the 'OK/CLOSE' button off the bottom of the screen and I can't click it. What are my options?

    Additionally, the list only seems to take into account the first three groups before it runs out of room so the next 4 are missing?
    Last edited by JaguarSean; Jun 19th, 2017 at 08:15 AM.

  6. #6
    Board Regular
    Join Date
    Jan 2008
    Posts
    12,114

    Default Re: VBA - pop up window to return a list of data based on cells

    The close Button is the "X" at the top right of the msgbox
    I should try the Userform method with the Listbox.
    The listbox will have a scroll bar for all your data.

  7. #7
    New Member
    Join Date
    Jun 2017
    Posts
    26

    Default Re: VBA - pop up window to return a list of data based on cells

    Quote Originally Posted by MickG View Post
    The close Button is the "X" at the top right of the msgbox
    I should try the Userform method with the Listbox.
    The listbox will have a scroll bar for all your data.
    I can get to the X, thats no problem.

    I think the listbox sounds like a good idea, unfortunately, something I've never heard of. If I insert your code I get errors. I assume I need to do other things in addition?
    Last edited by JaguarSean; Jun 19th, 2017 at 08:34 AM.

  8. #8
    Board Regular
    Join Date
    Jan 2008
    Posts
    12,114

    Default Re: VBA - pop up window to return a list of data based on cells

    Can you place a "forms button" or an active x "commandbutton" on your sheet with the code line:-
    Code:
    Userform1.show
    If so I will only need to explain the userform bit.
    Let me know !!!

  9. #9
    New Member
    Join Date
    Jun 2017
    Posts
    26

    Default Re: VBA - pop up window to return a list of data based on cells

    I can place a command button.

    If I apply this Macro;

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    Userform1.Show
    '
    End Sub


    I get an error in the userform1.Show line

  10. #10
    Board Regular
    Join Date
    Jan 2008
    Posts
    12,114

    Default Re: VBA - pop up window to return a list of data based on cells

    The error is because you have not yet got a userform.
    Follow details below:-
    Any problems call back!!!

    To Create Userform & ListBox. In Data sheet.
    Click Alt + F11 to show VBEditor

    If Properties window on left not showing, Click, Ctrl+R.

    With code window showing
    "To Insert Userform:-

    From Vbtoolbar click "Insert", "Userform".
    Userform appears in vbeditor window
    Adjust Userform size by strectching corners/sides.

    "To Insert Listbox":-
    From VbToolBar select "View" "Toolbox", ToolBox appears.
    From "ToolBox" , Click and drag "Listbox" (top right) to centre of Userform. Size as required.


    "To Load Code" :-
    Right Click Userform, Select "View Code", Code window appears.
    Paste the second bit of code I sent into code window
    Close Vbeditor

    Test code by clicking your Button/CommandButton with that bit of code:- Userform1.show.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com