Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look ups, a prompt box and fun all around
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 -- druce ----------------------------------------------------------------------- drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255 View this thread: http://www.excelforum.com/showthread.php?threadid=52356 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look ups, a prompt box and fun all around
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look ups, a prompt box and fun all around
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 -- druce ----------------------------------------------------------------------- drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255 View this thread: http://www.excelforum.com/showthread.php?threadid=52356 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look ups, a prompt box and fun all around
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look ups, a prompt box and fun all around
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look ups, a prompt box and fun all around
drucey wrote:
Stewart Druce it is then! Even if it's a girl? <g Stewart |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look ups, a prompt box and fun all around
Unfortunatly for her, yes. Thanks all -- druce ----------------------------------------------------------------------- drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255 View this thread: http://www.excelforum.com/showthread.php?threadid=52356 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look ups, a prompt box and fun all around
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 +------------------------------------------------------------------- -- druce ----------------------------------------------------------------------- drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255 View this thread: http://www.excelforum.com/showthread.php?threadid=52356 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look ups, a prompt box and fun all around
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),Tru e) = 22) worked for me. -- Regards, Tom Ogilvy "drucey" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) | |||
Prompt for Value | Excel Programming | |||
Prompt for Value | Excel Programming | |||
Prompt for Value | Excel Programming | |||
Prompt | Excel Programming |