Check boxes macro

**Colhawk** · 10-04-2012, 05:39 AM

Hi all,

I have a problem trying to get a macro to work which is attached to a check box.

the background is that it is for a monthly bonus sheet. Once the site reaches a certain level of recycling then the staff may be entitled to a bonus. The manager will enter the recycling rate in a specific cell at the bottom of the spreadsheet and their is a table on a separate tab which lists the different percentages and how much bonus they get.
What I have is a tick box next to each individual (there may be reasons why certain staff dont get the bonus that month) and when the tickbox is ticked I want it to look up a percentage figure from cell B22 to work out how much bonus should be paid.

I have managed to get this to work but what I also would like that when the checkbox is unticked then the bonus figure is deleted.

Like I said, I have managed to get it to work for once the checkbox is ticked but cant get it to do the deleting bit!

**Colhawk** · 10-09-2012, 11:56 AM

is that a no one????

**jaslake** · 10-09-2012, 01:36 PM

Hi Colhawk

Please attach a file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not).

**Colhawk** · 10-10-2012, 11:33 AM

Hi,

Thanks for the reply jaslake. I have uploaded a copy of the spreadsheet that I use.

**jaslake** · 10-10-2012, 01:13 PM

Hi Colhawk

Try this...let me know of issues

Sub CheckBox19_Click()
    Dim oCheck As Object
    Set oCheck = Sheet1.CheckBoxes("Check Box 19")
    If oCheck.Value = 1 Then
        Range("D2").FormulaR1C1 = _
                "=+IF(R[20]C[-2]="""","""",VLOOKUP(R[20]C[-2],'Payment list'!R[1]C[-3]:R[31]C[-2],2))"
        Range("D3").Select
    Else
        Range("D2").Value = ""
    End If
End Sub

**Colhawk** · 10-16-2012, 06:49 AM

Hi jaslake,

Thanks for that solution. It works well for the first 3 people on the timesheet but upon the fourth person when I click the check box it comes up with an error saying "Runtime error 1004. The item with the specified name wasn't found"

I have checked that the checkbox is definitely named Check Box 22 (even cut and paste this in to make sure it was right) but still cannot seem to see why it isnt working, the line it has problems with is coloured in red below.

Sub CheckBox22_Click()
    Dim oCheck As Object
    Set oCheck = Sheet1.CheckBoxes("Check Box 22")    
If oCheck.Value = 1 Then
        Range("D4").FormulaR1C1 = _
                "=+IF(R[18]C[-2]="""","""",VLOOKUP(R[18]C[-2],'Payment list'!R[1]C[-3]:R[31]C[-2],2))"
        Range("D5").Select
    Else
        Range("D4").Value = ""
    End If
End Sub

Thanks for any help you can give!

**bakerman2** · 10-16-2012, 03:31 PM

Assign macro to all your Checkboxes and make sure they are all aligned perfectly in each cell (you might need to increase the rowheight for a perfect fit)

Sub txtbxcheck()
    With ActiveSheet.CheckBoxes(Application.Caller)
        If .Value = 1 Then
            ActiveSheet.Cells(.TopLeftCell.Row, .TopLeftCell.Column + 1) = "=+IF(R[" & 22 - .TopLeftCell.Row & "]C[-2]="""","""",VLOOKUP(R[" & 22 - .TopLeftCell.Row & "]C[-2],'Payment list'!R[1]C[-3]:R[31]C[-2],2))"
        Else
            ActiveSheet.Cells(.TopLeftCell.Row, .TopLeftCell.Column + 1) = ""
        End If
    End With
End Sub