Link checkbox (form controls) with cell

[craig.penny]

Hi all,

I have several checkboxes in a worksheet where I've linked them with cells. When I check or uncheck the box the cell changes to true or false respectively. But the problem I'm having is that I want this to go both ways. So if the user changes a cell to true then I want the linked checkbox to show a check.

I appreciate any thoughts or suggestions and, as always, thanks very much!

It occurred to me that I didn't exactly ask a question here. The question is: how do I get a forms control checkbox on a worksheet to change in response to a cell being changed.

 

[MickG]

Hi, Try this:-

[COLOR=navy]Sub[/COLOR] CheckBox1_Click()
 [COLOR=navy]Dim[/COLOR]  Cl [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
     Cl = ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.LinkedCell
[COLOR=navy]If[/COLOR] ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.Value > 0 [COLOR=navy]Then[/COLOR]
    Range(Cl).FormulaR1C1 = "P"
        [COLOR=navy]With[/COLOR] Range(Cl).Characters(Start:=1, Length:=1).Font
            .Name = "Wingdings 2"
            .FontStyle = "Regular"
            .Size = 12
        [COLOR=navy]End[/COLOR] With
[COLOR=navy]ElseIf[/COLOR] ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.Value < 0 [COLOR=navy]Then[/COLOR]
    Range(Cl).FormulaR1C1 = " " ' [COLOR=seagreen]For a Cross Try the Letter "O"[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[MickG]

H, This is the same code, but set out a bit better !!

[COLOR="Navy"]Sub[/COLOR] CheckBox1_Click()
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range, Cl [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Cl = ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.LinkedCell
[COLOR="Navy"]With[/COLOR] Range(Cl).Characters(Start:=1, Length:=1).Font
      .Name = "Wingdings 2"
      .FontStyle = "Regular"
      .Size = 12
 [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] ActiveSheet.Shapes("Check Box 1").OLEFormat.Object
    [COLOR="Navy"]If[/COLOR] .Value > 0 [COLOR="Navy"]Then[/COLOR]
        Range(Cl).FormulaR1C1 = "P"
    [COLOR="Navy"]ElseIf[/COLOR] .Value < 0 [COLOR="Navy"]Then[/COLOR]
        Range(Cl).FormulaR1C1 = "O"
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[craig.penny]

MickG,

Thanks so much for replying. I struggled with it for a bit but I think I've got it now. Thanks for the "OLEFormat.Object.LinkedCell" I never would have known that!

Best Regards