Excel VBA Checkbox..... Pulling my hair out

[cizzett]

Ok So I have checkboxes I use in If statements thorughout my workbook and after adding one final it seems no matter what I do it will not recognize the value

I have declare the variable for the checkbox as normal

Dim RnFinalCB As CheckBox: Set RnFinalCB = Ws.Shapes("RnFinalCB").OLEFormat.Object

 If RunFinalCB.Value = False Then
Call Prep
End If

No matter what I do as long as this in in the code it skips the call and moves past wether the checkbox is true or false doesnt matter.

Any Ideas why? or how to resolve?

As I said I use this same thing on other checkboxes in my workbook and it works fine. So I am stumped.

 

[MickG]

Perhaps "Typo"

RunFinalCB.Value

 

[cizzett]

No I intentionally left the U out because I renamed the checkbox hoping it just didnt like the naming or something, no idea why it wont recognize this.

In the same code at the end I set it to make the checkbox value True and it will do that no problem so I know it sees the checkbox just don't know why it wont recognize false at before the call.

 

[cizzett]

SO I tweaked it to a not statement and that seems to work, for some reason it would not recognize the false status of the checkbox.

[COLOR=#333333]Dim RnFinalCB As CheckBox: Set RnFinalCB = Ws.Shapes("RnFinalCB").OLEFormat.Object[/COLOR]
 If Not RunFinalCB.Value = True Then
Call Prep [COLOR=#333333]End If[/COLOR]

 

[shg]

If this is a checkbox from the forms toolbar, the states are xlOn (1) and xlOff (-4146), not True and False.

 

[MickG]

I Don't understand your Code, You do not have the Worksheet ("Ws") or the Checkbox "RunFinalCb" declared or set.
I Imagine you do not have "Option Explicit", else you would hit an error.

The code below works for me !!!

[COLOR="Navy"]Sub[/COLOR] MG10May22
[COLOR="Navy"]Dim[/COLOR] RnFinalCB [COLOR="Navy"]As[/COLOR] CheckBox
[COLOR="Navy"]Dim[/COLOR] Ws [COLOR="Navy"]As[/COLOR] Worksheet
 [COLOR="Navy"]Set[/COLOR] Ws = ActiveSheet
 [COLOR="Navy"]Set[/COLOR] RnFinalCB = Ws.Shapes("RnFinalCB").OLEFormat.Object
    [COLOR="Navy"]If[/COLOR] RnFinalCB.Value = xlOn [COLOR="Navy"]Then[/COLOR]
        Call prep
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Or perhaps simpler Like:-

[COLOR="Navy"]Sub[/COLOR] CheckBox5_Click()
[COLOR="Navy"]If[/COLOR] ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value = xlOn [COLOR="Navy"]Then[/COLOR] Call prep
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[cizzett]

OMG Thats totally why, told ya'll it was something stupid