CHECK BOX WITH FORMULA

[vijay.vizzu]

DEAR ALL,


I USED AROUND 160 CHECK BOXS IN MY SHEET. NOW I MANUALLY LINK THE DESIRE CELLS. CAN IT POSSIBLE TO USE FORMULAS IN LINK CELL BOX IN CHECK BOX. ANY IDEA TO SHORT OUT THIS PROBLEM. I WISH TO LINK THE CHECK BOX LINK WITH CELL ADDRESS.


THANKS IN ADVANCE

 

[Luke M]

First, please turn of your CAPS lock. It makes it hard to read, and gives the impression that you are angry/yelling.


I'm not exactly sure which of these you were asking, so I'll try and cover them all.

a) You can not use a formula in the check box dialogue, Linked Cell box

b) The cell you are linking to *could* have a formula in it, and if the result changes, the check box will change value. However, if you ever click on the check box itself, the formula will be replaced with a hard value.

c) If you are needing to link all the boxes, and the cells they are linked to are in some sort of pattern, you might be able to use a macro to build all the cell links.

 

[dan_l]

ehhhhhh.....


I'm in a rush, but it's something like:


ActiveSheet.CheckBoxes.Add(Left, Top, Width, Height)


And then you can link with ".LinkedCell = "

 

[vijay.vizzu]

Sorry Luke... in further, i will keep it in my mind. Dear Dan, can you explain the VBA in a two three examples, becoz i am not proficient in VBA. Please...


Thanks

 

[dan_l]

Sorry Vijay:


Sub sometest()

Dim l, t, w, h As Integer

dim somecell as string


l = 145

t = 16

w = 25

h = 17

somecell = "a1"

ActiveSheet.CheckBoxes.Add(l, t, w, h).Select

With Selection


.LinkedCell = somecell

.Value = 1

End With


End Sub


So basically: you'll want to build some sort of apparatus to get the l,t,w,h values just by pulling properties ranges. Once you have those properties, use the above code to add a checkbox and using the linked cell property.

 

[Luke M]

Hi Dan!

Just a shared tip about declaring variables, copied from:

http://www.cpearson.com/excel/DeclaringVariables.aspx

Pay Attention To Variables Declared With One Dim Statement


VBA allows declaring more than one variable with a single Dim statement. I don't like this for stylistic reasons, but others do prefer it. However, it is important to remember how variables will be typed. Consider the following code:


Dim J, K, L As Long

You may think that all three variables are declared as Long types. This is not the case. Only L is typed as a Long. The variables J and K are typed as Variant. This declaration is functionally equivalent to the following:


Dim J As Variant, K As Variant, L As Long

You should use the As Type modifier for each variable declared with the Dim statement:


Dim J As Long, K As Long, L As Long