Check boxes

[Ernst Guckel]

I have a few check boxes on a worksheet that I need the cell link property to
be relative.

I have tried the following but it does not seem to work. Is there any way
around this?

=ADDRESS(MATCH(K8,'Raw Data'!BM11:BM378,0)+10,66)

Any ideas would be great.

Thanks,
Ernst.

[Dave Peterson]

If those checkboxes are from the Forms toolbar, maybe you could just do the
assignment in code.

A nice thing about those Forms Checkboxes is that they can all have the same
macro assigned to it.

I'm not sure how your formula works into it, but this code puts true/false into
the cell to the right of the topleftcell of the checkbox.

Option Explicit
Sub CBXClick()
Dim CBX As CheckBox
Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
CBX.TopLeftCell.Offset(0, 1).Value = CBool(CBX.Value = xlOn)
End Sub

If you use checkboxes from the controltoolbox, you can do a similar thing. But
the code will go into each checkbox's click procedure.

Option Explicit
Private Sub CheckBox1_Click()
Me.CheckBox1.TopLeftCell.Offset(0, 1).Value = Me.CheckBox1.Value
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Ernst Guckel wrote:
>
> I have a few check boxes on a worksheet that I need the cell link property to
> be relative.
>
> I have tried the following but it does not seem to work. Is there any way
> around this?
>
> =ADDRESS(MATCH(K8,'Raw Data'!BM11:BM378,0)+10,66)
>
> Any ideas would be great.
>
> Thanks,
> Ernst.

--

Dave Peterson

[Ernst Guckel]

> If those checkboxes are from the Forms toolbar, maybe you could just do the
> assignment in code.
>
> Option Explicit
> Sub CBXClick()
> Dim CBX As CheckBox
> Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
> CBX.TopLeftCell.Offset(0, 1).Value = CBool(CBX.Value = xlOn)
> End Sub

Efectivly what I am trying to do is... I have a weekly figures page that
has 5 check boxes per day to check off whether a deposit has been made at the
bank. When we check it off I want to store the status of such deposits long
after the week has changed. I have a database to store the deposits and
true/false values but the range is relative to the date of the week being
displayed.

Would it not be more practical if I were to change anything in code to then
just change the cell link value of each checkbox every time the dates on the
worksheet change?

Thanks,
Ernst.

[Dave Peterson]

I think I'd add a button from the forms toolbar that did all the work for all 5
checkboxes.

Then after the checkboxes are completed (checked or unchecked), you could hit
the button and populate that other range.

If the checkboxes are named "Check box 1", "check box 2", ..., "check box 5",
then it'll make life a little easier.

Option Explicit
Sub testme01()

Dim toWks As Worksheet
Dim iCtr As Long
Dim NextRow As Long
Dim CBX As CheckBox

'nice headers in Sheet2
'column A=date/time
'column B:F values of checkbox 1 to 5
Set toWks = Worksheets("sheet2")

With toWks
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With toWks.Cells(NextRow, "A")
.Value = Now
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
End With
For iCtr = 1 To 5
Set CBX = ActiveSheet.CheckBoxes("check box " & iCtr)
toWks.Cells(NextRow, 1 + iCtr).Value = CBool(CBX.Value = xlOn)
CBX.Value = xlOff
Next iCtr

End Sub

I put the current date/time in column A, but you could even pick that value up
from another cell on that worksheet.

Ernst Guckel wrote:
>
> > If those checkboxes are from the Forms toolbar, maybe you could just do the
> > assignment in code.
> >
> > Option Explicit
> > Sub CBXClick()
> > Dim CBX As CheckBox
> > Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
> > CBX.TopLeftCell.Offset(0, 1).Value = CBool(CBX.Value = xlOn)
> > End Sub
>
> Efectivly what I am trying to do is... I have a weekly figures page that
> has 5 check boxes per day to check off whether a deposit has been made at the
> bank. When we check it off I want to store the status of such deposits long
> after the week has changed. I have a database to store the deposits and
> true/false values but the range is relative to the date of the week being
> displayed.
>
> Would it not be more practical if I were to change anything in code to then
> just change the cell link value of each checkbox every time the dates on the
> worksheet change?
>
> Thanks,
> Ernst.

--

Dave Peterson