Check Box Highlight Cell Conditional Formatting problem

[sibley14]

Hope someone can help,

i have a sheet of order numbers that we use. e.g #1 to #320. this is across a whole sheet starting from Cell A2>G41 cascading downwards.

I want to assign a checkbox next to each numbered cell, that when ticked will highlight the numbered cell and colour it green with red text and strikethrough. This will show the number as used.

Without using a macro i have figured out how to add a text box, hide the true/false column and conditional format so that the numbered cell does as above.....however to do it this way i have to click on each check box and assign it manually to each cell.

Ideally i want a method to cut out the time consuming process of assigning each check box to each cell.

I am not used to using macro's but with the right instructions i would have a go willingly. Happy to send over the sheet or screen shots if anyone can help.

Thank you in advance!

 

[sibley14]

MickG thanks a bunch - using a blank sheet i have followed your instructions and the code inputs the check boxes in the right columns so it looks like the right layout.

When i click the check box it highlights green the correct column where the order number will be placed. A couple of things i hope you can help to modify

*Can we remove the code that labels the check box CB1 etc.The Check boxes don't need a label.
*When the checkbox is ticked can it strikethrough the order number text and change the font to Red as well as fill the cell green as it does.
*When the checkbox is unticked can it return the cell to normal (clear with black order number text) This will allow us to untick a box and recycle the order number.

Finally once all of the order numbers have been allocated we will change the first number on the sheet and using a formula we will calculate the remaining numbers so that we can start over again. So we would need an option to clear all text boxes (when playing around i managed to add a code in using a shortcut to clear them so i can probably figure this part out again).

Just to express my thanks again for the time and energy you've put in to help me here.

 

[MickG]

Use code below to Delete "Checkbox Captions and Set Values to "False". Alter "Rem mark as required.

[COLOR=Navy]Sub[/COLOR] MG04Mar09
[COLOR=Navy]Dim[/COLOR] CB [COLOR=Navy]As[/COLOR] CheckBox
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] CB [COLOR=Navy]In[/COLOR] ActiveSheet.CheckBoxes
CB.Caption = ""


'[COLOR=Green][B]Remove "Rem" mark to Use Line below to set all "Checkbox values to "False[/B][/COLOR]
'CB.Value = False
[COLOR=Navy]Next[/COLOR] CB
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Alter "TickBox" code as shown below to:-

Sub TicK()
Dim nRng As Range
Set nRng = Range(ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.LinkedCell).Offset(, 1)
If ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value = 1 Then
nRng.Interior.ColorIndex = 4
With nRng.Font
    .ColorIndex = 3
    .FontStyle = "Bold"
    .Strikethrough = True
End With
Else
nRng.Interior.ColorIndex = xlNone
With nRng.Font
    .ColorIndex = 1
    .FontStyle = "General"
    .Strikethrough = False
End With
End If
End Sub

Regards Mick

 

[sibley14]

Thanks Mick, Shall i enter these as new modules along with the previous code above? or just concentrate on adding these in the same way as we did before?

Cheers

 

[MickG]

Overwrite the original "Tick" Sub, and Enter as new Sub " MG04Mar09"

 

[sibley14]

Hi Mike, i have almost everything working perfectly thanks to you.....just one last piece of advice if possible. I've managed to input the following code via a module to clear the check boxes. It clears the check boxes but it does not clear the cells filled green or with the strike through. So i am left with cleared check boxes but green/strikethrough text. To remove this i have to tick and then untick the box and it resets it to blank. However imagine having to do this for over 300 cells. No thanks!

Can you help with this final request?

Ideally when we have used the last order number we want to reset the sheet and then re-use it by changing the numbers (i have a formula to update the numbers which we can do).

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sub clearcheck()
Dim sh As Worksheet
ForEach sh In Sheets
OnErrorResumeNext
sh.CheckBoxes.Value =False
OnErrorGoTo0
Next sh
EndSub

Alternatively we could add code to reset the sheet (ticks/fills) when all ticks have been checked.....what do you think?</code>

 

[MickG]

From your code I assume you have checkboxes in lots of sheet !!!
Try this:-
This should clear the checkboxes and Change the Colour, Font etc.

Sub clearcheck()
Dim sh As Worksheet
Dim rng As Range
For Each sh In Sheets
Set rng = sh.Range("A2:A49,D2:D49,G2:G49,J2:J49,M2:M49,P2:p49,S2:S49,V2:V49")
On Error Resume Next
sh.CheckBoxes.Value = False
rng.Offset(, 2).Interior.ColorIndex = xlNone
With rng.Offset(, 2).Font
    .ColorIndex = 1
    .FontStyle = "General"
    .Strikethrough = False
    .Bold = False
End With
On Error GoTo 0
Next sh
End Sub

 

[sibley14]

Hi MickG,

just wanted to give you some feedback on the sheet you designed for me. Everything works great and it's made a real difference here.

The only minor issue i would say is that since this is a shared file in a google drive folder, effectively of the three people using it, we can potentially all be in it at the same time. If we each ticked a number and exited it would save the file based on the last person to exit/save it - potentially this could cause an issue with the file not updating the desired information. (does that make sense).

I wondered if there was a simple way to make the sheet only accessible to one person at a time, but keep it as a shared file.

I guess the complication comes from it being in google drive and google drive works by taking a carbon copy of the file data from your computer. So in effect we are each accessing our own file and then that is it pushing the data to the google drive file.

What are your thoughts since you are the genius out of us both

thanks again for everything!

 

[MickG]

I sorry to say I don't think I have an answer for you.
I should try starting a new thread with your basic problem. hopefully someone will be able to help you.!!
Regrds Mick

 

[sibley14]

Hi MickG, hope you are well.

After your sterling work helping with this thread and giving me a solution i wondered if you might be able to help again. The sheet has been working fine until i upgraded my excel from 2007 to 2013 recently (i know!) compatibility issues forced my hand.

Anyway when i open the sheet i get this error message:
Excel was able to open the file by repairing or removing unreadable content.

Removed records: object from xl/printerSettings/printerSettings1.bin part (print options)

Click to view log file listing repairs:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error091680_02.xml</logFileName>
<summary>Errors were detected in file 'C:\Users\Adam\Google Drive\Order Sheets Macro.xlsm'</summary>
-<removedRecords summary="Following is a list of removed records:">
<removedRecord>Removed Records: Object from /xl/printerSettings/printerSettings1.bin part (Print options)</removedRecord>
</removedRecords>
</recoveryLog>

Any ideas how i can overcome this and stop the sheet from being repaired :-|

I'll also post this as a new thread to see if anyone can help