Help with Card Game Spreadsheet

[mkostin48]

Hi All,

I had come to this forum in the past to help me get a spreadsheet going for my weekly card night. It has since been working great but seems my group is hard to please. I was hoping I could get some assistance with updates.

Here are some of the suggestions I was told to try and get work into the sheet.

1. Instead of typing in Numbers have check boxes
-Players would be a drop down menu with 2-10 as an option
-Bid would be check boxes with 2,3,4 as options
-Win , would be check boxes with Yes or No
-Bidder would require a check box to be checked instead of typing in 1 above the persons name who is bidding
-Also need a check box to say who is in the hand and who is not (Some guys use the bathroom a lot, slows game play)

2. When someone Wins a Bid their score in that column should be shown circled; when someone loses a bid their score in that column should be shown with { } brackets around it


Thank you all for your assistance, you went above and beyond to create this spreadsheet for me and I truly appreciate it!

Thanks,
Michael

[BadlySpelledBuoy]

Hi Michael,

Not sure putting a circle around the winning bid is a particularly simple thing to do. Would it be possible to identify the win in a different way? Highlight the cell perhaps or make the font a different colour/style?

The rest should be straightforward. I shall have a look at it now.

BSB

[mkostin48]

We can identify the win or lose in any way as long as its clear. the brackets are just how they have been doing it for years on a piece of paper.

[mkostin48]

The payers: section could actually be removed if possible, we can have it count the amount of check boxes next to players who are taking part in the hand.

Let me know what you think.

Thanks,
MTK

[BadlySpelledBuoy]

Should the players count be the number of people in the game, regardless of if they're not in the hand? Or just a count of the people in the hand?

BSB

[mkostin48]

It is just the count of the people in the hand.

Info about the game:
1. Someone bids 2
2. The person who bid 2 "WINS"
3. Every other player (Who was in the hand) has to pay 2 so the winner.
3A. If the player who bid "LOSES" they have to pay every player who was in the hand 2.

[BadlySpelledBuoy]

The math for calculating winnings etc. all works from what we did on this before doesn't it?

BSB

[mkostin48]

Yes it works great, we have been using it for months now.
The check boxes are going to help alot because I use my surface pro, so everything is touch screen.

[BadlySpelledBuoy]

Have a look at the attached. Should take care of the first half of the changes. Let me know if it works as expected (or not!).

Need to have a think about part 2 but that will have to wait until tomorrow as it's bed time here and I've got to be up for work at 5am.

BSB

[BadlySpelledBuoy]

Sorry, forgot to explain that the radio buttons (the little round ones) are where you select the bidder. It will only let you have one selected at a time.
The check boxes (the little square ones) are to select who is in or out of the hand.

BSB

[mkostin48]

Buttons are perfect!

Calculation is off though. When a player is out, their score needs to stay the same but drop down to the next line.

Looks really good so far! Can't thank you enough BSB!

Good night.

Thanks,
MTK

[BadlySpelledBuoy]

Maybe this version will fix that issue? (fingers crossed!)

I shall take a look at part 2 tomorrow.

BSB

[thatandyward]

you could do something like this...

Please Login or Register  to view this content.

I added a section of code to your UpdateScores sub. I wasn't sure what constituted winning for each row, so I guessed it was the max value, obviously change that criteria to suit when assigning WinRng if it's something else.

also not sure what you're doing to reset the sheet so I wrote a quick sub to delete all the ovals on the sheet

Please Login or Register  to view this content.

which you can either just run as a macro, or call if from whatever code you use to reset the sheet

[mkostin48]

Working well BSB.

Just have to get the score to drop down when players are not in the hand.

[BadlySpelledBuoy]

Working well BSB.

Just have to get the score to drop down when players are not in the hand.

I thought I'd fixed that in my last post.

Commuting at the moment but will have a look as soon as I get home.

BSB

[mkostin48]

Take your time, i'm on the east coast, just about lunch time at work for me.

It was not bringing the score down when you un-check the box and update the scores.

Thank you!

[BadlySpelledBuoy]

Slightly tweaked version attached.
Let me know if this one acts as you expect. Seems to for me.

BSB

[mkostin48]

Works perfect!!! Any idea how to show if the person who bid "Wins" Highlight number in green. If person who bids loses highlight number in yellow.

Your awesome BSB!!!

Thanks,
MTK

[BadlySpelledBuoy]

No probs mate. Happy to help.

I need to know more about the game to get that last part sorted.
Can you explain how it works a little more? How do you define who wins and who loses each hand?
Can there be only one winner per round?

BSB

[mkostin48]

1. Dealer gives each player 6 cards

2. Go around the table and bid. (Highest bidder will start the hand)

3. The bids are related to the following.
- High Card (Ace)
- Low card (2)
- Jack
- Game (Total of points, 10=10, jack = 1, queen=2, king =3, ace=4; all other number cards do not count)

4. If they bid 2 and DO NOT (LOSE) get at least 2 of the following options listed above, they then pay each other player $2

5. If they DO (WIN) get at least 2 of the options listed above then everyone must pay the bidder $2

There is only 1 winner per round.
High card, Low card and Jack must be in the same suit as the first card thrown down.
Game (Total point) can be any suit.

We can only play the game with 8 players, but we allow outside spectators to decide whether or not they want to be involved in a specific hand. That is why we have the check boxes. The outside player thing is really just made up but it makes for a lot of fun.

I tried finding a video on youtube but I could not find anything. It is technically Cut Throat setback, in which we have added a couple tweaks.

[mkostin48]

here is a picture after playing 2 hands

[mkostin48]

Shown above:

Hand 1: Steve bid 2 and was a WINNER (everyone paid steve 2)
Hand 2: Fred Bid 2 and lost (He paid everyone 2)

[BadlySpelledBuoy]

OK, makes sense now. Thanks.

So from Excel's point of view, you are telling it who is the bidder and whether or not they won a given hand and THEN updating the scores.
In other words, in round 1 Steve was the bidder and you had Win = Yes selected so the relevant cell under his row would be green. Correct?
But in Fred's case he was the bidder but didn't win so the relevant cell under his row should be yellow?

Do you want the text to change colour or the cell background?

BSB

[mkostin48]

Just changing the cell background would be good enough.

Took me years before I could learn the game, and they all say I still know nothing lol!

[BadlySpelledBuoy]

How's this? The cell background will change green/yellow depending on a win/loss respectively.

I've also made it change the font colour in these cells as the white text doesn't show up well against a yellow cell background.

I've taken the liberty of adding a reset button to easily put everything back as it was ready for your next game.
I've put that button near the update scores button but you may wish to move it elsewhere. I've added an "Are you sure?" prompt to the button just in case you hit it accidentally and it will only clear the decks if you hit yes on the pop up.

Feel free to get rid of that button if you have a different method of resetting the workbook.

BSB

[mkostin48]

Oh that's awesome!! Is there a way to get a button to just delete the most recent row? I could throw that next to the reset button.

[BadlySpelledBuoy]

Like this?

BSB

[mkostin48]

Exactly, I'll show the group on Wednesday and let you know how amazed they are!

Doing it on my surface pro with touch screen should make this really seamless.

Thanks for everything BSB! You are the master!

-MTK

[BadlySpelledBuoy]

Far from a master, but thanks

Hope they appreciate all the effort you've put in!

Give us a shout if there are further developments needed.

Have fun.

BSB

[mkostin48]

haha, It will be pretty clear if they ask me about VBA that I had help

I was playing with it yesterday on my surface pro and had a slight issue pressing the radio buttons and check boxes. Any chance we can make those larger?

Thanks BSB!

[BadlySpelledBuoy]

I'm not sure there is an "easy" way to resize the radio buttons and check boxes.

One method would be to adjust the zoom level. Zooming in will make the buttons look bigger, then you can make the font size smaller and adjust row heights and column widths to get it all back looking something like it does now.
Problem is, that doesn't always look great as things start to look a bit pixelated.

Another option would be to use toggle buttons instead. With a little bit of code you could have toggle buttons work as a group to replicate the radio buttons and another set work independently to act as the check boxes.

Got to eat my dinner right now but I'll put something together to show you what I mean after that. Unless of course you don't like that idea at all....

BSB

[BadlySpelledBuoy]

Here's an example of the same thing but using toggle buttons instead of radio buttons and check boxes.

The code that makes it all work was thrown together for the sake of demonstration and could be written much better with a class module.

It works though and the buttons are easily resized to suit your needs.

BSB

[mkostin48]

That works perfectly with my surface pro touch screen! Now I don't have to keep my mouse on the playing field.

Everyone loved it on wed. during our card game!!

Their only suggestion was when someone wins they want the screen to flash or say something. If Bill wins it should say something like "That most certainly must be an error"
Its that kind of group lol

Thanks for making this awesome spreadsheet!

-MTK

[BadlySpelledBuoy]

You can actually make Excel talk so this is not as crazy an idea as it first seems.

I've never written VBA to accomplish that so it's an interesting challenge!

I shall have a look into it when I get home

BSB

[mkostin48]

Sorry for the delayed response. Been pretty hectic here at work lately.

Excel talking would be crazy!

If it could read the name of the person and whether or not they won and respond accordingly that would be awesome!!

Steve:
Win = "No Way"
Lose = "Suckaaaaa"

Mike:
Win = "To be expected"
Lose = "God damn it Rick"

Bill:
Win = "Double check that unlikely outcome"
Lose = "You know there was a 90% chance of that"


Something like that would be hilarious!!!

Thanks,
MTK

[mkostin48]

Hey all (BSB),

Had someone else keep score yesterday to get input on the program. They were having trouble setting the bid. Apparently fat fingers is a common issue.

I was wondering if its possible to change the drop down menu to some toggle buttons?

Below is an example of what I was thinking, not sure how to write that code though :/

Bid 2,3,4 (Only one selected at a time). The spades button when clicked would double the bid. So if 2 is selected as well as the spades button, the bid is actually 4.

The only other problem they had was not being able to tell which toggle button was selected. When a toggle button is depressed could it change backcolor to say Blue?

Thanks again!!!!

MTK

[BadlySpelledBuoy]

Hi. Been out of town for a couple of days with no laptop so not had a chance to look at the speaking thing yet.

On my way home now so will look into that and this new change as soon as I'm there. Watch this space!

BSB

[mkostin48]

No rush, got another week until the next card night. Hope it was a fun vacation and not work!

[BadlySpelledBuoy]

Hi MTK,

Apologies, this project got completely buried under some other work I've been doing this week

Anyway, attached with the button amendments as requested. I'm not 100% sure I've got the calculations correct for the "Spades" button so if you could check that and let me know then I'll amend as necessary.

I'm off out to the beach with the kids now but will look into the Excel speaking thing later today.

BSB

[mkostin48]

Calculations are perfect!

Any chance we can change the default font to 20 and Bold. Also can the background be solid blue instead of the dotted pattern?

I noticed spades button could use a picture. Is it possible to have the attached picture instead of the word "spades" ?


Thank you soo much!!!

-MTK

[BadlySpelledBuoy]

You don't want much in life do ya?!?!?

Leave it with me.

If I'm ever in CT you're buying me a beer and a steamer!

BSB

[mkostin48]

Hahaha, as many as you want!!!!

[BadlySpelledBuoy]

OK, I'll admit I got distracted by friends visiting with multiple bottles of high quality wine so I've not had as much time to spend on this tonight as I hoped.

I've changed the "spades" button over to the image provided and altered the font size on the buttons. You may wish to tinker about with that a little yourself as the "Out" now just shows as "O" and that's with the font set to 18 rather than the 20 you wanted.
To change that yourself, open the file and press Alt then type LDM to put it in "Developer Mode". You can then right click on the buttons and select "Properties" and tinker to get it the way you want.

I've also had to, as a quick fix, make the buttons red when not selected and the blue you disapprove of when selected. As it turns out this shading rather than a solid colour is part of these toggle buttons and is not easily changed.
If I had the time tonight I'd switch all the buttons for command buttons rather than toggle buttons. This would allow far better control over colours, but would take a bit of code rewriting so I'm afraid it's a job for another day.

Let me know how you get on and if you make changes then post your latest version back here and we'll pick up from there.

BSB

[mkostin48]

That is awesome! Works perfectly and all buttons are easily touch screen accessible!

I have another project that might be a little more interesting than this one if you are interested. It is something I was thinking about last night that would help my wife out at work. She works at a fortune 500 company and they are having issues with integration between software. She doesn't know we would be designing and creating it but it would definitely get me some kudos (and you when your in CT, haha).

I'm not even sure my idea is possible or if it would even work, I would have to get some test cases from her. If your interested ill PM you with some info about it.
It involves pulling data from other spreadsheets, sorting through that data and pulling all necessary information into 1 centralized spreadsheet.