How I get CheckBox state on a Sheet.

I want put a lot of checkBoxs, SpinButtons... on a Sheet.

In VBA project, how I get these CheckBoxs state by its name?

Thanks.

Cactus

Sub findCheckBoxState()
Dim chkState As Boolean
chkState = ActiveSheet.OLEObjects("Checkbox1").Object.Value
MsgBox chkState
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"Cactus [ÏÉÈËÇò]" <[email protected]> wrote in message
news:%23Z5YkDG%[email protected]...
>I want put a lot of checkBoxs, SpinButtons... on a Sheet.
>
> In VBA project, how I get these CheckBoxs state by its name?
>
> Thanks.

Nick Hodge

The example can't work in my Excel 2000.



Sub Main()


i = 2
Sheet1.Range("A1").Value = "Name"
Sheet1.Range("B1").Value = "Link Type"
For Each obj In Worksheets("sheet1").OLEObjects

'In track, never go to here. I put a few CheckBox's on Sheet1.
Sheet1.Cells(i, 1) = obj.Name
If obj.OLEType = xlOLELink Then
Sheet1.Cells(i, 2) = "Linked"
Else
Sheet1.Cells(i, 2) = "Embedded"
End If
i = i + 1
Next

End Sub




> Cactus
>
> Sub findCheckBoxState()
> Dim chkState As Boolean
> chkState = ActiveSheet.OLEObjects("Checkbox1").Object.Value
> MsgBox chkState
> End Sub
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> [email protected]HIS
>
>
> "Cactus [ÏÉÈËÇò]" <[email protected]> wrote in message
> news:%23Z5YkDG%[email protected]...
> >I want put a lot of checkBoxs, SpinButtons... on a Sheet.
> >
> > In VBA project, how I get these CheckBoxs state by its name?
> >
> > Thanks.
>
>

Are you using controls from the Controls toolbar or the Forms
toolbar?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Cactus [ÏÉÈËÇò]" <[email protected]> wrote in message
news:%23wORUzT%[email protected]...
>
> Nick Hodge
>
> The example can't work in my Excel 2000.
>
>
>
> Sub Main()
>
>
> i = 2
> Sheet1.Range("A1").Value = "Name"
> Sheet1.Range("B1").Value = "Link Type"
> For Each obj In Worksheets("sheet1").OLEObjects
>
> 'In track, never go to here. I put a few CheckBox's on
> Sheet1.
> Sheet1.Cells(i, 1) = obj.Name
> If obj.OLEType = xlOLELink Then
> Sheet1.Cells(i, 2) = "Linked"
> Else
> Sheet1.Cells(i, 2) = "Embedded"
> End If
> i = i + 1
> Next
>
> End Sub
>
>
>
>
>> Cactus
>>
>> Sub findCheckBoxState()
>> Dim chkState As Boolean
>> chkState = ActiveSheet.OLEObjects("Checkbox1").Object.Value
>> MsgBox chkState
>> End Sub
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> [email protected]HIS
>>
>>
>> "Cactus [ÏÉÈËÇò]" <[email protected]> wrote in message
>> news:%23Z5YkDG%[email protected]...
>> >I want put a lot of checkBoxs, SpinButtons... on a Sheet.
>> >
>> > In VBA project, how I get these CheckBoxs state by its name?
>> >
>> > Thanks.
>>
>>
>

Chip Pearson

I mistake the two controls toolbar.

Thanks.


> Are you using controls from the Controls toolbar or the Forms
> toolbar?
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>

Does that mean you used the checkbox from the Forms toolbar?

If yes,

Option Explicit
Sub testme()

If ActiveSheet.CheckBoxes("check box 1").Value = xlOn Then
MsgBox "It's checked"
Else
MsgBox "it's not checked"
End If

End Sub



Cactus [ÏÉÈËÇò] wrote:
>
> Chip Pearson
>
> I mistake the two controls toolbar.
>
> Thanks.
>
> > Are you using controls from the Controls toolbar or the Forms
> > toolbar?
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >

--

Dave Peterson

Dave Peterson

Now I see that differ of the two toolbar.

by Controls toolbar, I can set a name for a CheckBox.

by Forms toolbar, get checkboxes by array.


Thanks


> Does that mean you used the checkbox from the Forms toolbar?
>
> If yes,
>
> Option Explicit
> Sub testme()
>
> If ActiveSheet.CheckBoxes("check box 1").Value = xlOn Then
> MsgBox "It's checked"
> Else
> MsgBox "it's not checked"
> End If
>
> End Sub
>
>
>
> Cactus [ÏÉÈËÇò] wrote:
> >
> > Chip Pearson
> >
> > I mistake the two controls toolbar.
> >
> > Thanks.
> >
> > > Are you using controls from the Controls toolbar or the Forms
> > > toolbar?
> > >
> > >
> > > --
> > > Cordially,
> > > Chip Pearson
> > > Microsoft MVP - Excel
> > > Pearson Software Consulting, LLC
> > > www.cpearson.com
> > >
> > >
>
> --
>
> Dave Peterson