drucey wrote:
Firstly, you people are brilliant.

Secondly,


I have 15 rows that people fill out an order with.
Each row is an item.

Next to the row is a tickbox - when ticked, conditional formatting
turns the item row green. This means it has been recieved. The tickbox
although floating on the the item row, actually refers to a hidden
cell. This hidden cell turns TRUE or FALSE depending on the tickbox
status, and the conditional formatting of the item row looks at this
hidden cell.

I'm trying to make a macro that:

IF ALL rows that have text in, the corresponding tickbox is ticked,
then do a prompt box something like "All items recieved?". A yes then
does something (i can do!) and a no does something else.

Please help me with this. I'll name my first born after you!


Drucey,

I'm not quite sure what you mean by "next to the row there is a tickbox"
but what I'm assuming you mean is that in one column you have (15 cells
of) text, and that in the cells next to them there are tick boxes, and
in other cells in the same row there are hidden cells with the true
false value, e.g.

A B C(hidden)
1 Item1 [ ] FALSE
2 Item2 [X] TRUE
3 Item3 [ ] FALSE
....

If it's something different please advise, otherwise the code below
should help. Watch out for line-wrap!

Sub LookupsAPromptBoxAndFunAllAround()

Dim i as Long
Dim AllTrue as Boolean
Dim Row as Long

Const FirstRow = 1 ' Where to start searching - change this as needed -
1 to 65536
Const LastRow = 15 ' Where to stop searching - change this as needed - 1
to 65536
Const TextColumn = ?? ' You will need to fill this in - the column that
contains the text
Const TickColumn = ?? ' You will need to fill this in - the (hidden)
column with the ticks in

AllTrue = True

For Row = FirstRow To LastRow
If CStr(Cells(Row, TextColumn).Value) < "" Then
If Not CBool(Cells(Row, TickColumn).Value) Then
' Row has text in but doesn't have a tick
AllTrue = False
Exit For ' No need to keep searching
End If
End If
Next

If AllTrue then
' All Rows with text have ticks
Select case MsgBox("All items received?", vbQuestion Or vbYesNo, "Put
Your Caption Here")
Case vbYes
' Stuff to do if Yes is pressed
Case vbNo
' Stuff to do if No is pressed
Case Else
' Stuff to do if something else is pressed (e.g. Cancel)
' You can make the box have Yes, No and Cancel buttons
' By replacing vbYesNo above with vbYesNoCancel
End Select
End If

End Sub

Stewart