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

JaguarSean

New Member
Joined
Jun 5, 2017
Messages
31
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;

ABCDE
GROUP 1 - Enable
LABEL 1TRUE
LABEL 2FALSE
LABEL 3FALSE
LABEL 4TRUE
GROUP 2 - Condition
LABEL 5TRUE
LABEL 6FALSE

<tbody>
</tbody>

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
 
excellent instructions.

Ive placed an activex command button on my sheet with the following macro attached:

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

In the VBA window, under 'Forms', I have a UserForm1. Within that i created a window as per your instructions. Copied in your code and exit.

Click the button and I get the following error:

Compile error: Invalid outside procedure

Is my macro correct?
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
just stepped through it all again to ensure I did it all right, this time I get an error:

Only comments may appear after End Sub, End Function, or End Property

This is yellow:

Private Sub UserForm_Initialize()

This is when I click my command button in the sheet
 
Last edited:
Upvote 0
Sorry, bit pigs ear on the copying front !!!
The top and bottom lines where not required.

Remove the previous code from the Userform VbWindow and replace with the code below.
Hopefully its a bit better !!
Code:
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))
Msg = "You Have Differences" & vbLf
For Each Dn In Rng
    If Not IsEmpty(Dn.Offset(, -1).Value) Then
        Temp = Dn.Offset(, -1)
        If Not Msg = "You Have Differences" 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
Msg = Msg & vbLf & "Do you Agree with Diferences ??"
ListBox1.List = Split(Msg, vbLf)
End Sub
 
Upvote 0
Sorry, bit pigs ear on the copying front !!!
The top and bottom lines where not required.

Remove the previous code from the Userform VbWindow and replace with the code below.
Hopefully its a bit better !!
Code:
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))
Msg = "You Have Differences" & vbLf
For Each Dn In Rng
    If Not IsEmpty(Dn.Offset(, -1).Value) Then
        Temp = Dn.Offset(, -1)
        If Not Msg = "You Have Differences" 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
Msg = Msg & vbLf & "Do you Agree with Diferences ??"
ListBox1.List = Split(Msg, vbLf)
End Sub

Bingo. that works a treat. You Sir are awesome!

just a couple of minor editing tweaks to make I think. I'll drop a note back on here tomorrow with those. Off to enjoy the sunny weather!

Thanks again
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top