Check value of CheckBox in Excel

[IjumpOverU]

How would I determine if the check box that triggered an event is selected or not. I thought if I used Sheet2.OLEObject("CheckBox1").Objects.Value this would work, however it does not recognize "Objects" as valid??

Help!

 

[dyarwood]

Could you use

Sheets("Sheet2").combobox1.value

This should work

 

[IjumpOverU]

I tried this as well. For some reason when ever I enter the (index) portion of any command it will not recognize anything after that. I have no idea why!!

Thanks for the help!

 

[cLFlaVA]

Remove the plural?

Sheet2.OLEObject("CheckBox1").Object.Value

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[IjumpOverU]

I get the same result that the objects after the (index) is not recognized.

 

[PHV]

Are you sure the control name is CheckBox1 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[IjumpOverU]

Quite sure at least from what I can tell. Here is the bit of code that does not seem to want to work. The OLEOject is not recognized in this case it only sees the OLEObjects object.

Sub CheckBox1_Click()
Dim TotalUsers As Integer
Dim TotalPrice As Double
TotalUsers = Range("B61").Value

If Sheet2.OLEObject("CheckBox1").Object.Value = 0 Then
If TotalUsers = 0 Then
TotalPrice = 0
ElseIf TotalUsers <= 5 Then
TotalPrice = Range("C5").Value
End If
End If
End Sub

 

[PHV]

And what about this ?
If Sheet2.OLEObjects("CheckBox1").Object.Value = 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dyarwood]

Try this

Sub CheckBox1_Click()
Dim TotalUsers As Integer
Dim TotalPrice As Double
TotalUsers = Range("B1").Value

If CheckBox1.Value = False Then
If TotalUsers = 0 Then
TotalPrice = 0
ElseIf TotalUsers <= 5 Then
TotalPrice = Range("C5").Value
End If
End If
MsgBox (TotalPrice)

End Sub

the checkbox works with values false and true

 

[IjumpOverU]

This gives me an object required error at the CheckBox1.Value row. Did I add the checkbox incorrectly? Sorry I am a bit new to the Excel part of VB. I just brought up the forms toolbar and dropped a new checkbox onto the sheet.

Thanks Again!

 

[dyarwood]

Is this code behind the same sheet as the checkbox is on? Also look at the properties of the checkbox and make sure it is checkbox1 as its name.

 

[IjumpOverU]

It shows up under a module not the sheet itself. Why would that happen?

 

[dyarwood]

Have you used the macro record button?

To get this to work open up your VBA window and on the left double click on the sheet where the checkbox is stored.

With this function in a module the Sub CheckBox1_Click will not know where the checkbox is.

 

[TonyJollans]

Hi IjumpOverU,

This all seems rather complicated.

If you are in the Click event (which is in the sheet's module) you should be able to reference the checkbox directly ..

[blue]Sub CheckBox1_Click()
    :
    :
    If CheckBox1 = True Then
        [green]' Do your stuff[/green]
    End If
    :
    :
End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Loomah]

I just brought up the forms toolbar and dropped a new checkbox onto the sheet.

It [the code] shows up under a module not the sheet itself

If the quotes are acurate try creating the checkbox using the controls toolbox. Much less hassle!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[TonyJollans]

Hi IjumpOverU,

Oops, I missed that - the checkbox is off the Forms toolbar. Go with Loomah and make it easy on yourself.

Or try this (rather generic) ..

[blue]If ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value = xlOff Then ...
[green]' xlOff (value -4146) or xlOn (value 1)[/green][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Loomah]

Just seen my own post. Serius case of Planet Loomah spelin convenshuns!!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​