Look ups, a prompt box and fun all around

[drucey]

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 formattin
turns the item row green. This means it has been recieved. The tickbo
although floating on the the item row, actually refers to a hidde
cell. This hidden cell turns TRUE or FALSE depending on the tickbo
status, and the conditional formatting of the item row looks at thi
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 the
does something (i can do!) and a no does something else.

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

 

[S. I. Becker]

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

 

[drucey]

Stewart Druce it is then!

Thank you SO much for taking the time to do that chap.

I'm keeping my eyes peeled on the forum to see if there are an
questions i can answer, to try and contribute instead of just asking

 

[drucey]

and one quick thing - it's spot on, but my tick box cells are actually
in the same column as the item cells, but 100 cells below.

Is there any easy way to incorporate this, or would it be best for me
to put them on the same row, but a few columns off screen?

 

[Tom Ogilvy]

the best place is to put them under the checkboxes and make the checkboxes
opaque so they cover the cell.

 

[S. I. Becker]

Stewart Druce it is then!

Even if it's a girl? <g>

Stewart

 

[drucey]

Unfortunatly for her, yes.


Thanks all

 

[drucey]

Hmm having some trouble getting it to recognise the ticks...

It does the prompt box which is very good, but even if no ticks hav
been ticked (with items in the rows), it still brings up the promp
box

Could someone possibly have a look pls?

I've attached my work of art!

(To get it going, put in a random supplier, a random item, then hi
"Save/Send" (let it send the email, it only sends it to my addy at th
moment), and then "Save Delivery Info" comes up - thats the macro tha
is as above

+-------------------------------------------------------------------
|Filename: FM Purchase Order.zip
|Download: http://www.excelforum.com/attachment.php?postid=4470
+-------------------------------------------------------------------

 

[Tom Ogilvy]

change this:
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 = F ' You will need to fill this in - the column that
contains the text
Const TickColumn = "Y" ' 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

To

Dim allTrue as Boolean
AllTrue = (Application.Countif(Range("Y18").Resize(22,1),True) = 22)

worked for me.