Check Boxes.. ?

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
I have inserted a check box in SHeet1.
Now, when i tick the check box i want that it should display this formula in Cell B1 = C1*D1
and if i untick the check box it should display this
Cell B1 = F1 * G1.

is tht possible???
also it would be for the entire column.

Please Help me.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Using a Checkbox from the Controls ToolBox, I wouldn't use the whole Column though, but limit it to the amount of data available.

Option Explicit

Code:
Private Sub CheckBox1_Click()
    Dim cl     As Range
    Dim rng    As Range
    With Sheet1
        Set rng = Columns(2)
    End With
    For Each cl In rng
        If Me.CheckBox1.Value = True Then
            cl.FormulaR1C1 = "=RC[1]*RC[2]"
        Else: cl.FormulaR1C1 = "=RC[4]*RC[5]"
        End If
    Next cl
End Sub
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
just read Joe's question, if you are considering a Checkbox for each row, i would simply have a value to check in another column & use an If statement.

So

=IF(A2="Y",C2*D2,F2*G2)
 

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
but now i have a problem.... i have a huuuuuge formula to be followed how do i incorporate that out here??? :(
heres the formula
P10 = Date
F2 = (=Now())
IF($P10="","",IF($F$2>$P10,"LATE",IF(($F$2+7)>$P10,"1Wk",IF(($F$2+14)>$P10,"2Wk",IF(($F$2+21)>$P10,"3Wk",IF(($F$2+28)>$P10,"4Wk",IF(($F$2+35)>$P10,"5Wk","6WK")))))))
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
I'm not completely familiar with check boxes in excel, but if they are anything like access try this code:

Code:
Private Sub CheckBox1_Click()

If (checkbox1.value = true) then
Cells(1,2).value = cells(1,3).value * Cells(1,4).value
Else
Cells(1,2).value = cells(1,6).value * Cells(1,7).value
End If

End Sub
 

joefrench

Active Member
Joined
Oct 4, 2006
Messages
357
So your checkbox could try to multiply a formula "F2 = (=Now())" by the value of G2?

Probably gonna be a problem.
 

Forum statistics

Threads
1,181,100
Messages
5,928,058
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top