+ Reply to Thread
Results 1 to 6 of 6

Linking command buttons to option buttons

  1. #1
    Registered User
    Join Date
    02-13-2012
    Location
    Windhoek
    MS-Off Ver
    Excel 2007
    Posts
    3

    Linking command buttons to option buttons

    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.

    Please Login or Register  to view this content.
    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?
    Attached Files Attached Files
    Last edited by sealtite01; 02-20-2012 at 12:44 PM.

  2. #2
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Linking command buttons to option buttons

    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.

  3. #3
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Linking command buttons to option buttons

    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:

    Please Login or Register  to view this content.
    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.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-13-2012
    Location
    Windhoek
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Linking command buttons to option buttons

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

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

    Re: Linking command buttons to option buttons

    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

  6. #6
    Registered User
    Join Date
    02-13-2012
    Location
    Windhoek
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Linking command buttons to option buttons

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1