Check Box Value Error "object Required" Error 424

[kaylarossj]

This is the First Macro i've ever tried to create. There is two but they are both giving the same error. Any help is appreciated!!!

Sub L2LTrue()


If L2LCheckBox.Value = True Then


'L2L True Macro


Range("E20").Select
ActiveCell.FormulaR1C1 = "=R[38]C[5]-R[39]C[1]-R[-12]C[-3]"
Rows("28:65").Select
Selection.EntireRow.Hidden = False
Rows("28:43").Select
Selection.EntireRow.Hidden = True


Else


' L2LFalse Macro


Rows("27:61").Select
Selection.EntireRow.Hidden = False
Rows("44:59").Select
Selection.EntireRow.Hidden = True
Range("E20").Select
ActiveCell.FormulaR1C1 = "=R[22]C[4]-R[22]C[1]"
Range("G30").Select


End If


End Sub






Sub RentTrue()


If RentCheckBox.Value = True Then




' RentTrue Macro


Range("G30").Select
ActiveCell.Value = "20"


Else


' RentFalse Macro


Range("G30").Select
ActiveCell.Value = "0"


End If


End Sub

 

[Norie]

How did you create the checkboxes and where is this code located?

 

[kaylarossj]

I used the "Check Box" button on the developer tab

When i go to VB Editor The code shows up under a module I named Checkboxes under the worksheet I'm working in.

 

[Norie]

If you right click one of the checkboxes which of Assign Macro... or View Code an option?

If it's the former the buttons are Forms buttons, the latter they are ActiveX.

For either you need to select the appropriate option to goto the right place to put the code.

What happens when you do that?

 

[kaylarossj]

Assign Macro

When i clock assign macro i Can see the "L2LTrue" macro. when i click edit I can see the code i posted above. however the first line "Sub L2LTrue()" is highlighted red.

 

[Norie]

Sounds like Forms buttons then and you can't directly reference them, you need to refer to them via the Shapes collection of the sheet they are on.

For example if the checkbox named 'L2LCheckBox' is located on 'Sheet1'.

Sub L2LTrue()
Dim chk As Object

    Set chk = Sheets("Sheet1").Shapes("L2LCheckBox")
    
    If chk.OLEFormat.Object.Value = 1 Then

    'L2L True Macro

        Range("E20").FormulaR1C1 = "=R[38]C[5]-R[39]C[1]-R[-12]C[-3]"
        Rows("28:65").EntireRow.Hidden = False
        Rows("28:43").EntireRow.Hidden = True

    Else

    ' L2LFalse Macro

        Rows("27:61").EntireRow.Hidden = False
        Rows("44:59").EntireRow.Hidden = True
        Range("E20").FormulaR1C1 = "=R[22]C[4]-R[22]C[1]"

    End If

End Sub

 

[kaylarossj]

Great That worked. Also thanks a lot for helping with the cleaning up of the code by taking out the selection pieces.

The only this is i'm getting a strange error. When i click the checkbox named Rent Increase it says there is no End If... but there is.

Sub L2LTrue()
Dim chk AsObject


    Set chk = Sheets("2 year rent increase calcs").Shapes("L2LCheckBox")


    If chk.OLEFormat.Object.Value = 1 Then


'L2L True Macro


        Range("E20").FormulaR1C1 = "=R[38]C[5]-R[39]C[1]-R[-12]C[-3]"
        Rows("28:65").EntireRow.Hidden = False
        Rows("28:43").EntireRow.Hidden = True


Else


'L2LFalse Macro


        Rows("27:61").EntireRow.Hidden = False
        Rows("44:59").EntireRow.Hidden = True
        Range("E20").FormulaR1C1 = "=R[22]C[4]-R[22]C[1]"




EndIf


EndSub






Sub RentTrue()
Dim chk AsObject






If RentCheckBox.Value = True Then


    Set chk = Sheets("2 year rent increase calcs").Shapes("RentCheckBox")

    If chk.OLEFormat.Object.Value = 1 Then


' RentTrue Macro


        Range("G30").Value = "20"


Else


' RentFalse Macro


        Range("G30").Value = "0"


EndIf


EndSub

 

[kaylarossj]

Oh i had an extra bit of the old code in there.

Thanks so much for your help!! works perfectly now!!