I posed this question late yesterday and tried to 'bump' it (as I
assumed not many people saw it yesterday) ... but I don't see the
bumped version at all .... so I post again!

I've built a summary workbook to consolidate data gathered from approx
30 external workbooks. I've built a way to easily identify which (if
any) external workbooks were not updated on time -- an Advanced
Filter, using VBA. So, now I have a range of cells (Sheet='Admin',
range=G38:H68) which will hold the Store Name/# of those who didn't
submit on time. The 'used' part of this range will likely change with
each update (eg 5 stores are late this time, but only 2 next time and
none last time).

What I want to do is have a button on the Dashboard (what I call the
main sheet) which will allow users to see a list of the
'offenders' ( ie those that didn't send an update). This would be in
a pop-up window or userform, and only show the 'used' range (as
mentioned above) .... just two columns containing Name and Number of
the non-updaters.

How would you code this?

TIA,
ray

Ray,

A lot depends on the structure of your sheet, and how you store the values on the sheet....

Since you didn't state the structure of your data, this assumes that you have cells in a range that
may or may not be blank, and if they aren't blank, you want to show the data. In that case, you
could use something like:

Dim myStr As String
Dim myCell As Range

Sub GetInfo()

myStr = ""

For Each myCell In Worksheets("Admin").Range("G38:G60")
If myCell.Value < "" Then
If myStr = "" Then
myStr = myCell.Value & vbTab & myCell(1, 2).Value
Else
myStr = myStr & Chr(10) & myCell.Value & vbTab & myCell(1, 2).Value
End If
End If
Next myCell

MsgBox myStr ' myStr can be used any way you want

End Sub


HTH,
Bernie
MS Excel MVP


"Ray" wrote in message
ups.com...
I posed this question late yesterday and tried to 'bump' it (as I
assumed not many people saw it yesterday) ... but I don't see the
bumped version at all .... so I post again!

I've built a summary workbook to consolidate data gathered from approx
30 external workbooks. I've built a way to easily identify which (if
any) external workbooks were not updated on time -- an Advanced
Filter, using VBA. So, now I have a range of cells (Sheet='Admin',
range=G38:H68) which will hold the Store Name/# of those who didn't
submit on time. The 'used' part of this range will likely change with
each update (eg 5 stores are late this time, but only 2 next time and
none last time).

What I want to do is have a button on the Dashboard (what I call the
main sheet) which will allow users to see a list of the
'offenders' ( ie those that didn't send an update). This would be in
a pop-up window or userform, and only show the 'used' range (as
mentioned above) .... just two columns containing Name and Number of
the non-updaters.

How would you code this?

TIA,
ray

Hi Bernie .... thanks for the code!

Here's some additional info that may help to clarify somewhat ...

The raw data sits in A38:C68 -- basically like this:
Column A Col B Col C
Store X #155 Yes
Store Y #162 No
Store Z #379 Yes
Store AB #212 No

Range G38:H68 holds the results of an Advanced Filter applied via
VBA. From the above example, this range would look like this:
Col G Col H
Store Y #162
Store AB #212

The above filtered data is in G38:H40 ... the rest of the range
(G41:H68) is completely blank. The Adv-Filter is applied at the end
of another macro, so the resulting Filtered data is just sitting
around, waiting to be used.

The popup box (or userform) that I desire would be called via a button
or hyperlink on a different sheet ... and would simply show the 'used
range' of the Advanced Filter (G38:H40 from above).

does that clarify?

You could use something like this, assuming that you clear the range G38:G68 before returning the
new set, just in case no stores were late:

Dim myR As Range
Dim myCell As Range
Dim myStr As String

On Error GoTo NoCells
Set myR = Worksheets("Admin").Range("G38:G68").SpecialCells( xlCellTypeConstants)
On Error GoTo 0
For Each myCell In myR
If myStr = "" Then
myStr = myCell.Value & vbTab & myCell(1, 2).Value
Else
myStr = myStr & Chr(10) & myCell.Value & vbTab & myCell(1, 2).Value
End If
Next myCell
StrSet:
MsgBox myStr
Exit Sub
NoCells:
myStr = "No stores are late"
Resume StrSet



--
HTH,
Bernie
MS Excel MVP


"Ray" wrote in message
ps.com...
Hi Bernie .... thanks for the code!

Here's some additional info that may help to clarify somewhat ...

The raw data sits in A38:C68 -- basically like this:
Column A Col B Col C
Store X #155 Yes
Store Y #162 No
Store Z #379 Yes
Store AB #212 No

Range G38:H68 holds the results of an Advanced Filter applied via
VBA. From the above example, this range would look like this:
Col G Col H
Store Y #162
Store AB #212

The above filtered data is in G38:H40 ... the rest of the range
(G41:H68) is completely blank. The Adv-Filter is applied at the end
of another macro, so the resulting Filtered data is just sitting
around, waiting to be used.

The popup box (or userform) that I desire would be called via a button
or hyperlink on a different sheet ... and would simply show the 'used
range' of the Advanced Filter (G38:H40 from above).

does that clarify?

Hi Bernie -

Works perfectly ... with one small adjustment needed! The text in the
pop-up isn't aligned at all, so results look like this:
Atlanta 257
Frisco 183
Houston 379
Long Island 156
Orange County 167
Paramus 409
Philadelphia 215

Is there anyway to format the box so that it's a neater presentation?

Thanks VERY MUCH for your help with this!

Rgds, ray

Ray,

Unfortunately, not with a msgbox.... they use proportional fonts.

You could use a userform (Userform1) with a listbox (Listbox1) with 2 columns (Set the columncount
property of the listbox to 2), and code like this:

Dim myR As Range
Dim myCell As Range
Dim mySA() As String
Dim i As Integer

On Error GoTo NoCells
Set myR = Worksheets("Admin").Range("G38:G68") _
.SpecialCells(xlCellTypeConstants)
Load UserForm1
ReDim mySA(0 To myR.Cells.Count, 0 To 1)
On Error GoTo 0
i = 0
For Each myCell In myR
mySA(i, 0) = myCell.Value
mySA(i, 1) = myCell(1, 2).Value
i = i + 1
Next myCell
UserForm1.ListBox1.List = mySA
UserForm1.Show
Exit Sub
NoCells:
MsgBox "No stores are late"


HTH,
Bernie
MS Excel MVP


"Ray" wrote in message
oups.com...
Hi Bernie -

Works perfectly ... with one small adjustment needed! The text in the
pop-up isn't aligned at all, so results look like this:
Atlanta 257
Frisco 183
Houston 379
Long Island 156
Orange County 167
Paramus 409
Philadelphia 215

Is there anyway to format the box so that it's a neater presentation?

Thanks VERY MUCH for your help with this!

Rgds, ray

Thanks VERY MUCH Bernie .... very good information and quite useful!

Regards,
Ray

One final question ...

When I implemented the code above, it works fine when there are no
'errors' ... but, when there are no stores (and thus, an 'error'), the
code doesn't work properly. First, the 'no stores are late' msgbox
pops-up, but when it's dismissed, the userform shows up anyway!

How do I prevent the userform from appearing when there's an error?

Almost there ...

Rgds, ray

Ray,

The userform should not show up when there are no stores (the specialcells lines throws an error),
and didn't in my testing. What are you using to run the code? A macro, or one of the userform's
events?

HTH,
Bernie
MS Excel MVP


"Ray" wrote in message
ups.com...
One final question ...

When I implemented the code above, it works fine when there are no
'errors' ... but, when there are no stores (and thus, an 'error'), the
code doesn't work properly. First, the 'no stores are late' msgbox
pops-up, but when it's dismissed, the userform shows up anyway!

How do I prevent the userform from appearing when there's an error?

Almost there ...

Rgds, ray