Deselecting a check box if cell value changes

[SteveR]

Hi, I'm pretty good at Access but rarely use Excel and I need some assistance
please. I have several columns with a total at the bottom of each. At the
top there is a check box which copies the column and pastes to another sheet.
It erases the column in the second, destination sheet if the check box is
deselected. The problem is that if someone changes a value somewhere in the
column the cells in the destination sheet do not update, you would have to
deselect and reselect the check box. I want the deselect, select action to
happen if the contents of the total cell change.

Thanks for the help!

[JNW]

You could use the worksheet_change event for the specific worksheet. to get
there you go to the VBA window (alt+F11) and open the code for the sheet you
are working in. Then type something like the following:

Private Sub WorkSheet_Change()
Application.ScreenUpdating = False
OnUncheck 'name of sub that runs when checkbox is unchecked
OnCheck 'name of sub that runs when checkbox is checked
Application.ScreenUpdating = True
End Sub

"SteveR" wrote:

> Hi, I'm pretty good at Access but rarely use Excel and I need some assistance
> please. I have several columns with a total at the bottom of each. At the
> top there is a check box which copies the column and pastes to another sheet.
> It erases the column in the second, destination sheet if the check box is
> deselected. The problem is that if someone changes a value somewhere in the
> column the cells in the destination sheet do not update, you would have to
> deselect and reselect the check box. I want the deselect, select action to
> happen if the contents of the total cell change.
>
> Thanks for the help!

[SteveR]

I can't get this code to work. Here is the code for one of nine similar
check boxes:

Private Sub Chk2037_Click()
Dim cbCol As Long

cbCol = Sheet1.Chk2037.BottomRightCell.Row + 11

If Sheet1.Chk2037.Value = True Then
Sheet1.Columns(cbCol).Copy Destination:=Sheet2.Columns(cbCol)
ElseIf Sheet1.Chk2037.Value = False Then
Sheet2.Columns(cbCol).ClearContents
End If

End Sub

I want this code to happen if there are any changes to the cells in it's
column. Or I can have all nine events happen if there is a change anywhere
in the spreadsheet.

This is the code i put in for just the one column (check box)

Private Sub Worksheet_Change()
Application.ScreenUpdating = False
Chk2037 'name of sub that runs when checkbox is unchecked
'OnCheck 'name of sub that runs when checkbox is checked
Application.ScreenUpdating = True
End Sub

It gives me a complie error on the first line: "Procedure declaration does
not match descripion of event or procedure having the same name"

Thanks for the help.


"JNW" wrote:

> You could use the worksheet_change event for the specific worksheet. to get
> there you go to the VBA window (alt+F11) and open the code for the sheet you
> are working in. Then type something like the following:
>
> Private Sub WorkSheet_Change()
> Application.ScreenUpdating = False
> OnUncheck 'name of sub that runs when checkbox is unchecked
> OnCheck 'name of sub that runs when checkbox is checked
> Application.ScreenUpdating = True
> End Sub
>
> "SteveR" wrote:
>
> > Hi, I'm pretty good at Access but rarely use Excel and I need some assistance
> > please. I have several columns with a total at the bottom of each. At the
> > top there is a check box which copies the column and pastes to another sheet.
> > It erases the column in the second, destination sheet if the check box is
> > deselected. The problem is that if someone changes a value somewhere in the
> > column the cells in the destination sheet do not update, you would have to
> > deselect and reselect the check box. I want the deselect, select action to
> > happen if the contents of the total cell change.
> >
> > Thanks for the help!