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 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!

[S. I. Becker]

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

[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 any 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.

--
Regards,
Tom Ogilvy


"drucey" wrote:

>
> 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?
>
>
> --
> drucey
> ------------------------------------------------------------------------
> drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
> View this thread: http://www.excelforum.com/showthread...hreadid=523566
>
>

[S. I. Becker]

drucey wrote:
> 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 have been ticked (with items in the rows), it still brings up the prompt 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 hit "Save/Send" (let it send the email, it only sends it to my addy at the moment), and then "Save Delivery Info" comes up - thats the macro that is as above)

[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.

--
Regards,
Tom Ogilvy




"drucey" <[email protected]> wrote in
message news:[email protected]...
>
> Hmm having some trouble getting it to recognise the ticks...
>
> It does the prompt box which is very good, but even if no ticks have
> been ticked (with items in the rows), it still brings up the prompt
> 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 hit
> "Save/Send" (let it send the email, it only sends it to my addy at the
> moment), and then "Save Delivery Info" comes up - thats the macro that
> is as above)
>
>
> +-------------------------------------------------------------------+
> |Filename: FM Purchase Order.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4470 |
> +-------------------------------------------------------------------+
>
> --
> drucey
> ------------------------------------------------------------------------
> drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
> View this thread: http://www.excelforum.com/showthread...hreadid=523566
>