VBA msgbox to list Cells meet a Criteria

[Biz]

Dear All,

I am trying to develop a macro that lists all cells via msgbox that meet criteria "closed".

If column E contains closed then when vba is fired then lists all cells references that contain closed.

Col E

<TABLE style="WIDTH: 84pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=112><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><TBODY><TR style="HEIGHT: 31.5pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 84pt; HEIGHT: 31.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_365471 class=xl65 height=42 width=112>Status</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 84pt; FONT-FAMILY: Arial; HEIGHT: 20.25pt; COLOR: windowtext; FONT-SIZE: 16pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-background-source: auto; mso-pattern: black gray-125; text-underline-style: none; text-line-through: none" class=xl66 height=27 width=112>Closed</TD></TR><TR style="HEIGHT: 40.5pt" height=54><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 84pt; HEIGHT: 40.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=54 width=112>Active</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 84pt; FONT-FAMILY: Arial; HEIGHT: 20.25pt; COLOR: windowtext; FONT-SIZE: 16pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-background-source: auto; mso-pattern: black gray-125; text-underline-style: none; text-line-through: none" class=xl66 height=27 width=112>Closed</TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 84pt; FONT-FAMILY: Arial; HEIGHT: 20.25pt; COLOR: windowtext; FONT-SIZE: 16pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-background-source: auto; mso-pattern: black gray-125; text-underline-style: none; text-line-through: none" class=xl66 height=27 width=112>Closed</TD></TR></TBODY></TABLE>

msgbox should list $E$12 $E$14 $E$15.

Your help would be appreciated.

Biz

 

[wigi]

Hello

Why do you want the output to come in a MsgBox? That is about the worst output format you can have...

Why not simply using the autofilter on column E?

 

[Warship]

Maybe:

Sub ClosedAddresses()
    Dim c As Range
    Dim msgAddress As String
    msgAddress = ""
    
    For Each c In ActiveSheet.Range("E:E")
        If c = "" Then Exit For
        If c = "Closed" Then msgAddress = msgAddress & c.Address & " "
    Next
    
    MsgBox msgAddress

End Sub

 

[Biz]

Hi Wigi,

The only reason I am using msgbox because user is trying to submit Timesheet and I am blocking them from submission of closed jobs and listing msgbox with cells in violation.

Kind Regards,

Biz

 

[wigi]

Then, you would better go for a (simple) userform, containing a listbox or combobox. This will show, 1 by 1, the offending cells.

Clicking a cell in the list might then bring new functionality, to really help the user fill in the timesheet.

 

[Biz]

Maybe:

Sub ClosedAddresses()
    Dim c As Range
    Dim msgAddress As String
    msgAddress = ""
 
    For Each c In ActiveSheet.Range("E:E")
        If c = "" Then Exit For
        If c = "Closed" Then msgAddress = msgAddress & c.Address & " "
    Next
 
    MsgBox msgAddress
 
End Sub

Hi Warship,

I changed you code slightly and got result I wanted.

Sub ClosedAddresses()
    Dim c As Range
    Dim msgAddress As String
    msgAddress = ""
    
    For Each c In ActiveSheet.Range("E:E")
        If c = "Closed" Then msgAddress = msgAddress & c.Address & " "
    Next c
    
    MsgBox msgAddress
End Sub

Thank you again for quick response.

Biz

 

[Biz]

Then, you would better go for a (simple) userform, containing a listbox or combobox. This will show, 1 by 1, the offending cells.

Clicking a cell in the list might then bring new functionality, to really help the user fill in the timesheet.

Hi Wigi,

Sounds like fantastic idea. Could you please show sample code to make it work?

Biz

 

[wigi]

Please browse the forum and the existing topics, many times before code to fill a combobox was posted.

 

[Biz]

Please browse the forum and the existing topics, many times before code to fill a combobox was posted.

Thanks M8 I will look into it more closely.

Biz