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!

 

[MickG]

It's not clear what the Range address is of the cells you want checkboxes in.?????
It would appear that for each "Checkbox" you need 3 cells :- One for the "Number",the next for the Linked cell(Hidden) and another for the "CheckBox".!!!

 

[sibley14]

Hi Mick G, thanks for coming back to me and sorry for the lack of info. You are right, I would need and currently have
three cells for each checkbox. 1) the checkbox itself, 2) the linked cell (hidden), 3) the order number.

Since my order numbers stretch across 8 columns though it does make the sheet quite a lot bigger than it was.

The other solution i was thinking of was to turn the order number into a 'button' that when clicked would highlight/strikethrough to show as used. Although this is beyond my current capabilities.

Perhaps you could suggest a solution if possible? Either of the above would be fine for now.

Its a multi use sheet shared between 3 people so we all need to be able to show that we have used an order number. A check box or button are the best ideas that i have come up with so far.

Appreciated your help and ideas.

Thanks

 

[MickG]

You would only see 16 columns if you hid the linked cell columns.
If you did this what is the "Range Address" of the cells You would have Numbers in.??????

 

[sibley14]

Hi MickG,

The range address for the numbers and the check boxes only is A2:P49 (if i have understood what the range address is correctly).

 

[MickG]

If your going to use 3 columns thats 24 columns in all. Then hide all Linked cell columns,reducing the visible columns to 16. ???
"A" = Number, "B" = Linked cell , "C" = CheckBox :"D" = Number etc.
Down to row 49, Does that sound what you want ??????

 

[sibley14]

Yes that sounds great, it's actually what i have so far. So i have a total of 16 columns. Mine are set out in a slightly different order to your suggestion

"A" = Checkbox, "B" = Linked Cell (Hidden), "C" = Number and repeat.

 

[MickG]

Try this:-

Sub ChBox()
'This code Places the Check Boxes in the sheet
Dim cboxLabel As String
Dim linkedColumn As String
Dim cellRange As String
Dim myCell As Range
Dim myBox As Object
Dim rng As Range
'This is the Range with "CheckBoxes"
 Set rng = Range("A2:A49,D2:D49,G2:G49,J2:J49,M2:M49,P2:p49,S2:S49,V2:V49")
With ActiveSheet
    For Each myCell In rng
      With myCell
        Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
          Width:=.Width, Left:=.Left, Height:=.Height)
 
        With myBox
          .LinkedCell = myCell.Offset(, 1).Address '
          .Caption = "CB" & myCell.Row - 1
          .Name = "checkbox_" & myCell.Address(0, 0)
          .OnAction = "Tick"
        End With
    End With
  Next myCell
End With
End Sub

'This is the "Tick" code,that Formats the Numbers.
'Place this code in a Basic code Module (Not a Worksheet Module)
'Code:

Sub TicK()
Dim nRng As Range
If ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value = 1 Then
Set nRng = Range(ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.LinkedCell).Offset(, 1)
nRng.Interior.Color = 10806983
With nRng.Font
    .Color = -16776961
    .FontStyle = "Bold"
    .Strikethrough = True
End With
End If
End Sub

 

[sibley14]

Thanks a bunch MickG, i'm not familiar with code in excel. So can you give me the steps to input the above, where/how etc.
Assume that i have to open the developer tab>and then Macro or VBA?

Sorry - new territory for me.

 

[MickG]

Installing Code:-
Nb:- In first bit of code check the variable "Rng" is showing the ranges you want to fill with "Checkboxes, alter to suit !!!!
Then
1) Copy first bit of code
2) Activate (open) the sheet you want the "Checkboxes" in.
3) Right click the sheet Tab, Click "View Code", Code window appears.
4) At top of code window select "Insert", "Module" ,. New code window appears. Paste code into code window.
5) Close Code window.

6) Copy second bit of code.
7) Return to "Checkbox" sheet.
8) Right Click sheet tab, Select "View Code", Vbwindow appears.
9) From top of code window Select "Insert", "Module", New code window appears.
10) Paste second "Code" into Code window.
11) Close code window.

12) Select "Developer" Tab from Top of Active sheet (Checkbox sheet).
13) Select "Macro" from Developer Tab. Macro dialog box appears.
14) Select the "Macro", "ChBox" from the Dialog box Window.
15) Click "Run" from the right hand side of the dialog Box.
16) Active sheet will fill with "Checkboxes" in desired range.

Hopefully I've not missed any thing.
If you muck it up call back and I'll give you some code to delete all the checkboxes, so you can start again.