Linking command buttons to option buttons

**sealtite01** · 02-13-2012, 05:25 AM

Hi all , I am completely new to VB so please be patient. I am trying to make a simplified cricket scoring sheet in Excel 2007 that fits all the functions on one spreadsheet page. I have created command buttons that each represent a possible scoring entry in cricket i.e one button for a single run , one for 2 runs , one for 6 runs and so on. I have created a list of batsman names on the left and one for bowler names on the right of my spreadsheet. The theory works as follows : When I select a batsman's name with the option button on the left and the same way a bowler's name on the right , that should mean that that bowler is bowling to that batsman , and all entries should be made next to that batsman and bowler's names. The idea is to tell the spreadsheet next to which name to make entries as , they change all the time as the batsman rotate ends and overs complete.

I have used this entry to get the numbers to tick over when a command button is pressed :

This button represents a single run scored and one ball bowled.

Private Sub CommandButton1_Click()
            mycount = Range("j19,k19,o19") + 1
            Range("j19,k19,o19") = mycount
            mycount = Range("p19") + 0.1
            Range("p19") = mycount

As you can see this command changes the numbers in a specific range of cells and not dependent upon the specific option button that is ticked next to a name.(See the attached file) . Any ideas on how I can fix this?

**swoop99** · 02-13-2012, 06:18 AM

Ja, met flurgles.

Welcome to the forum. Could you please put [ code ] tags around your macro above? The mod's are kinda hot on that forum rule and if we attempt to help you without them our posts will be deleted.

Also, I can't open .xlsm files but if you repost the scoresheet at an .xls file I'll have a look for you.

**swoop99** · 02-17-2012, 06:22 AM

OK, sorry for delay in replying and ignore the PM I sent cos it doesn't cover everything. In order to run this in an older version of excel I had to add .value to the end of all your range statements. However, the main issue is that your macro's have no way to reference which batsman / bowler is in play.

To fix this, I've added macro's to all the selection dots next to the batsman / bowler names which write the row value to cells C37 and M37 (displayed in white text so you can't see it).

Then I've replaced your 1 run macro with:

Private Sub CommandButton1_Click()
            
            Dim Batsman, Bowler As Integer
            Batsman = Range("C37").Value
            Bowler = Range("M37").Value
            
            Range("j" & Batsman).Value = Range("j" & Batsman).Value + 1
            Range("k" & Batsman).Value = Range("k" & Batsman).Value + 1
            Range("o" & Bowler).Value = Range("o" & Bowler).Value + 1
            Range("p" & Bowler).Value = Range("p" & Bowler).Value + 0.1
            
End Sub

As you can see, you don't need the "mycount" variable, you can just reference the cells you want to change without using a variable. The cell that gets updated depends on what value is found in the cells C37 and M37. I'll leave the rest of the editing to you as you've got a lot of buttons on that thar spreadsheet.

Tot siens.

**sealtite01** · 02-17-2012, 12:10 PM

Thank you a billion , it works great. Just have a lot of changes to make now but at least I know what to change.

**Winon** · 02-17-2012, 12:47 PM

Hello sealtite01,

Very impressive work you have done here!

@swoop99,

Goeie naand,

I don't know how you managed to make head or tails from this but, you most certainly managed to return an amazing bit of "trickery"!

Knap gedaan

Totsiens

**sealtite01** · 02-20-2012, 11:25 AM

Yes , swoop 99 have done a great job and saved me a couple of months trying to figure this one out. I'll attach the finished sheet for all to have a look at . Kind of simple and logical when it's all done , but it sure couldn't have happened without swoop99's magic.Thanks again.