Get the value of a checkbox's linked cell...?

[BuschMan]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am writing some VBA code in Excel 2007 and am stuck on a property with the standard form check box (not OLEObjects).<o></o>
<o></o>
I would like to check and find out what the value is of the linked cell for a checkbox...<o></o>
<o></o>
Like, if a user clicks the checkbox, it changes the linked cell value to true or false... But I cannot figure out how to "lookup" or "go and get" this true or false value through code...<o></o>
<o></o>
Anyone got any pointers on this one...?<o></o>
<o></o>
Thanks,<o></o>
"BuschMan"

 

[jim may]

Under Format Control - the Control Tab enter into the Linked Cell reference box, Say B2

Then in your code the immediate window type:

? Range("B2").value

 

[Jaafar Tribak]

You may want to try this :

MsgBox CBool(WorksheetFunction.Max(ActiveSheet.Shapes("Check Box 1").ControlFormat.Value, 0))

where ChecKbox1 is the name of the checkbox on the activesheet. change these as required.

Regards.

 

[BuschMan]

Hi Guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for the quick reply...<o></o>
<o></o>
I am able to find out what the linked cell is, but cannot get the value... (See the code below)<o></o>
<o></o>
For example, is I change "Selection.LinkedCell" to "Selection.LinkedCell.Value" I get an error... Make sense...?<o></o>
<o></o>
Also, for reference, I also listed the code below that I am using to add the checkboxes to the document...<o></o>
<o></o>
Thanks,<o></o>
"BuschMan"<o></o>
<o></o>

Sub CreateDocument()
 
Dim i As Integer
 
  For i = 2 To 6
 
  ActiveSheet.CheckBoxes.Add((Rows(i).Left) + 5, (Rows(i).Top + 5), 100, 25).LinkedCell = (Cells(i, 3).Address)
 
  Next i
 
End Sub

Sub FindOutValue()<o:p></o:p>
<o:p></o:p>
Dim CB As Shape<o:p></o:p>
<o:p></o:p>
  For Each CB In ActiveSheet.Shapes<o:p></o:p>
<o:p></o:p>
      CB.Select<o:p></o:p>
<o:p></o:p>
      MsgBox Selection.LinkedCell<o:p></o:p>
<o:p></o:p>
  Next CB<o:p></o:p>
<o:p></o:p>
End Sub<o:p></o:p>

<o></o>
<o></o>

 

[Jaafar Tribak]

Try this :

Sub FindOutValue()
 
  Dim CB As CheckBox
 
  For Each CB In ActiveSheet.CheckBoxes
      MsgBox CBool(WorksheetFunction.Max(CB, 0))
  Next CB
 
End Sub

 

[mikerickson]

Since the checkboxes are linked to the cell, you don't need to reference the cell as well as the control. Here are two syntax's you might use.

Dim CB As Object
For Each CB In ActiveSheet.Shapes
    MsgBox Range(CB.ControlFormat.LinkedCell).Value & " from cell"
    MsgBox CBool(CB.ControlFormat.Value And 1) & " from checkbox"
Next CB

 

[BuschMan]

Hi Guys,

Thanks for the help... That worked perfect...!

So, now that I can figure out what the value of the linked cell is, how can I find out if the linked cell is visible or not...?

What I'm trying to do is have a checkbox for each row, and upon using the autofilter to hide some rows, I want to check to see if the linked cells are visible and if not, then hide the corresponsing checkboxes.

Let me know if anyone has any ideas on how to do this.

Thanks,
-"BuschMan"

 

[mikerickson]

Dim CB As Object
For Each CB In ActiveSheet.Shapes
    With Range(CB.ControlFormat.LinkedCell)
        CB.Visible = Not (.EntireRow.Hidden Or .EntireColumn.Hidden)
    End With
Next CB

 

[Jaafar Tribak]

In order to get mikerickson's code to run automatically when filtering the linked cells , I suggest that you add a formula with a SubTotal Function and place the code in the Worksheet Calculate Event handler.

For example:

Linked Cells : A1;A2;A3;A4 and A5 on Sheet1

in a remote cell on the sheet or better still, in a hidden sheet , place the following formula in a cell : =SUBTOTAL(9;Sheet1!$A$1:$A$5)

Then, place the code in the hidden sheet Calculate Event handler as follows :

Private Sub Worksheet_Calculate()
 
Dim CB As Object
For Each CB In Sheet1.Shapes
    With Sheet1.Range(CB.ControlFormat.LinkedCell)
        CB.Visible = Not (.EntireRow.Hidden Or .EntireColumn.Hidden)
    End With
Next CB
 
End Sub

Regards.

 

[mikerickson]

That's sweet.

Good call.