Checkboxes on spreadsheet misaligned when sorting

[rbeech23]

Hi - I have a spreadsheet that's being populated from another sheet with data, checkboxes and date pickers. This is to enable tracking of orders. The data gets added no problem but when I run a sort on the rows the checkboxes become misaligned. In the attached spreadsheet the first sheet OrderProcessing is how the data looks before the sort. The second sheet OrderProcessing-Sorted is after I've highlighted A3:V11 and sorted on column R. The sort works okay but there are some of the checkboxes that don't line up whereas others do. Please assist - thanks

[torachan]

Hi.
I seem to have seen this one before.
The primary reason everything moves about is the checkbox container is not fully enclosed in some of the cells (i.e. the checkbox overlaps the cell boundary)
It is something I have witnessed may times (to the extent I have a piece of code that I use to straighten-up checkboxes).
To be perfectly frank neither checkboxes or datepicker are designed to be used in the manner you are trying to.
You should not embed multiple datepickers in the sheet - a single datepicker should be called by a Worksheet_Change event and the date selected enters the cell.
Instead of the checkboxes merely put a tick in the relative cells-far less maintenance and far more sort friendly and stable.
Merely passing on 45 years worth of experience - I have made most of the mistakes and still make the odd one.
torachan

[rbeech23]

Hi Torachan, I like your suggestions so have replaced the checkboxes with the tick option which works fine. However I'm struggling with the date picker. Do you have an example of when a cell is clicked a date picker opening and applying the selected date to the cell ? Or in fact in my case multiple cells. Thanks

[torachan]

Hi.
I have quickly thrown together on your existing sheet what I was proposing.
Ranges E4:I9 & T4:U9 click on cells within these ranges and you have a dropdown.
The button on the dropdown flip/flops from a 'tick' to a 'X' according to the state of the cell.
If the cell is clear the button will put in a 'tick', if the cell has a'tick' the button will have a'X' and pressing will clear the cell.
On range J4:S9 click on a cell and the datepicker bar will appear, if you want the default (today) just click the date window.
If you want the calendar choice click the side arrow and select another date.
I have not had time to try a filter or sort, as the cell contain sheet values this should be OK as there are no Forms/ActiveX objects present on the sheet.
torachan.

nearly forgot, the sheet is vertically elastic, if you add/subtract at the bottom of column A the 'tick' & 'datepicker' features expand/contract with the sheet.

[rbeech23]

Hi Torachan, that is fabulous !! I wonder if I could trouble you further and strike whilst the iron is hot. In column V is a Percent Complete value, I'd like this to adjust upwards whenever one of the values is filled in. So for columns E to I column V (%complete) should go up by 10 for each row ticked. For J to Q to go up by 5% and likewise for T and U. So if all of the cells have values the %complete would be 100%. I'd like anything 100% to be hidden as it signifies a complete order. I'd be very grateful if you could accommodate this. Many thanks.

[torachan]

I have had to add an extra cell clearance button for the date cells, the percentage counter depends on these buttons to add/subtract.
Therefore do not edit the date/tick areas by using the on sheet delete commands as the counter will lose sync.
On 100% completion the item is moved to the 'orders completed' sheet and removed from the 'work in progress'
I have done this as I cannot predict the behavior of the sheet during sorting/filtering with an every growing quantity of randomly placed hidden rows.
torachan.

[rbeech23]

Thanks Torachan, that is excellent work, I can't thank you enough

[rbeech23]

Hi Torachan, I wonder if I could trouble you with the spreadsheet you sent. I'm testing changes which populate the order processing sheet and need to clear the contents during testing. When I select the entire row I get a tick or date box presented which remains despite selecting. Is it possible to amend so that if the row is selected I'm allowed to clear the contents ? Thanks

[torachan]

The three small pop-up forms have their 'X' closure disabled, this was done so that the code moved the form in each case.
this also ensured the percentage completion calc was performed, I have removed the 'disable' so that you can dismiss the forms while you delete rows.
You can do this in your present app by finding the five lines of code in each form and placing an apostrophe in front to disable (code turns green).
Capture.JPG

[rbeech23]

Hi Torachan, thanks for the quick reply. After commenting out the five lines I get a Remove Date button when I select the row to delete. It removes the A cell row value and I'm able to then remove the line but when it's the first row to delete it places a tick in Order Reference. Apologies to be a pain if you wouldn't mind taking a look, image attached

Thanks

[torachan]

Have you disabled the code in all three forms (frmDATE, frmNODATE, & frmTICK).
As the entire row is selected the forms will appear one after the other - using the small 'X' (upper right corner now dismisses the form) this action will have to be done once for each form (do not click the button on the form).
It works OK in the attached file - although not ideal I will look at this when I have five minutes to see if I can code a better solution.

[rbeech23]

Thanks, I still have to select a few checkboxes but it's clearing okay after that so I'm happy. Once I get this properly running I'd hope I don't have to clear contents much anyway.
Appreciate your help again. Cheers, Rob

[rbeech23]

Hi Torachan, me again ! I've bought a new computer which has office 365 but is 64 bit. I'm reading on the forums the date picker doesn't work for 64 bit which might explain why I'm getting an error with DTPicker1. I've tried registering mscomct2 but nothing shows up in Excel. Do you have an up to date version that could be used in the OrderProcessing worksheet ? Many thanks in advance

[torachan]

This is the price of progress, 64 bit does not offer an equivalent.
There are a number of add-ins developed, the best is one by Trevor Eyre but it is code heavy.
For the interim I have inserted my own simple version of date selector.
The days/months/years are altered by the up/down spinner - click on the day/month or year - it will highlight light blue - then adjust-click green button to insert date.
When I get five minutes I will see if I can insert Trevor Eyres datepicker if I can code it to move with the cell choice.
torachan.

[torachan]

Trevor Eyre's datepicker incorporated - not tested fully but appears to function OK following active cell.

[torachan]

alternative - prettier version

[rbeech23]

Excellent stuff Torachan, many thanks again