Checkboxes & VBA

[mosiki]

Hi

I have a userform with 31 checkboxes named Played1, Played2......Played 31.

There is also a command button named "Update".
There is also a checkbox named "Team".

I have 20 worksheets named "Arsenal", "Aston Villa"...."Wigan Athletic".

The checkbox named "Team" will contain one of the team names upon opening i.e. on running userform1.show the userform will open and the textbox named "Team" will contain a team name which is also the name of a worksheet.

I will click certain checkboxes from Played1......Played31, so that some will have a true value and some false.

When I click the "Update" command button I would like to transfer this information to a worksheet, the worksheet that is named per the textbox named "Team".

Eg. I open the userform and the textbox named "Team" is displaying "Arsenal".

I click some of the checkboxes to have a value of True, leaving others as false. When I click the "Update" command button I want these checkbox values to add 1 to each cell in the range B1:B31 on the worksheet named "Arsenal" if the value is true, and add zero to each cell in the same range if false.

So if Played1.value = true then add one to cell B1, if played1.value = false add zero to cell B1. (if cell B1 showed 4, then it would change to 5 if value Played1.value = true and update was clicked)

If played2.value = true add 1 to cell B2, if played2.value = false then add zero to cell B2...........and so on for each checkbox.

Played1......Played31 corresponds to the worksheet named the same as the "Team" textbox, range B1:B31.

I cant seem to construct a dynamic code with a loop that will update this same range for each worksheet. The worksheet being updated will depend on the textbox "Team".

Sorry for this longwinded post but I just want to provide as much clarity as possible.

Thanks

Mosiki

 

[mikerickson]

This should do it.

Dim aRange as Range, i as Range

Set aRange = ThisWorkbook.Sheets(Team.Text).Range(B1:B31)

For i = 1 to 31
    With aRange.Cells(i, 1)
        .Value = Val(CStr(.Value)) - CLng(Me.Controls("CheckBox" & i))
    End With
Next i

 

[mosiki]

Thanks, its coming up with data mismatch, highlighting "i", should I set this to

i as integer rather than i as range ??

Mosiki

 

[mikerickson]

Yes, I should have. Sorry.

 

[mosiki]

Thanks a lot, works great. One more QU.

After I click update and the data is entered on to the worksheet I want the macro to show this particular worksheet

Eg. worksheets(team.text).show

Obviously the above doesn't work

??

thanks

Mosiki

 

[jeffreybrown]

Try .select

 

[mosiki]

Doh! Now its obvious.

Cheers

Mosiki