Solved: conditional delete of shape(s)

[ndendrinos]

Hello, Had to reduce the size of the file to post it here so most of the cards are gone.
The deal & quit buttons work ... if one dbl clicks under a card range C2:G2 the word "Hold" will paste. For those of you unfamiliar with poker that means you want to keep these cards and exchange the ones that are not marked "Hold" (cell is blank) for fresh ones.
Any "drawn" cards will have to start from B6 and on (no dups)
Tried to conditionally delete the shapes but failed.

The code to delete all the shapes was not in answer to my post ... I borrowed it and it works.... maybe it could be altered to serve the purpose here.

IMPORTANT: started part 1 of this project on MrExcel's board and got help ...Part 2 was not addressed, or if it was I did not understand it.(It can be found under "Poker 1of2"
Maybe by uploading the file here will make things easier.
Thank you

[ndendrinos]

Worked on code "draw" and got it to leave the pics "marked" HOLD alone and delete the ones with nothing in the cell below....

One more thing to do and I'm done ... To "Draw" I have to add that starting with the value in B6 and downward ... the macro has to fill any missing cards.

This one I don't think searching forums or the internet will produce rsults ... maybe someone here can help out

Thanks

[ndendrinos]

Trying still and have a question:
how can I replace the zero in " If c.Value = 0 Then" with Shapes



[vba]Sub fillempty()

For Each c In Worksheets("Sheet1").Range("C1:G1").Cells
If c.Value = 0 Then
c.Select

ActiveSheet.Shapes(Range("B6").Value).Copy
ActiveSheet.Paste
End If
Next


End Sub[/vba]

[ndendrinos]

All above questions have now been addressed (by yours trulyI may add) and this is the last sticky point.

In this code :

[VBA]Sub fillempty()

For Each c In Worksheets("Sheet1").Range("C1:G1").Cells
If c.Value = "" Then
c.Select

ActiveSheet.Shapes(Range("B6").Value).Copy
ActiveSheet.Paste
End If
Next

[E4].Select
End Sub[/VBA]

What needs to change is the range for the secong empty cell from B6 to B7
for the third empty cell / B8 ... the fourth / B9 and finally for the fith empty cell B10.

Here is the book the way it stands now ... I take credit for the montage of the codes ... BUT they work !!!

[mdmackillop]

I've added a Public Variable NextCard to store the row number of the next card to be used.
I took the liberty of tidying up the code to get rid or repetitive elements, and also changed your HOLD to a single click with toggle type change.
In a couple of tests, Draw did not work as expected in that a Held card changed if you run a second draw, so keep an eye on it.
Things would run quicker if only the range containing shapes was checked for deletion, but I'm not sure if this is possible.

[ndendrinos]

Malcom, thank you very much for taking the time to look at and solve the problem here.The single click to "Hold" is very welcome as well as the "tidying up the code to get rid or repetitive elements" ... that one made me chuckle but hey while I know zilch about coding I am very pleased by the effort I've put in this little project (close to a week) .. Comparing the original to your revised version I will learn something and that's what it's all about.
Before I mark this "solved" and if I may, one clarification of your "Draw did not work as expected in that a Held card changed if you run a second draw, so keep an eye on it."

Not sure what you mean ... not sure if you play poker either so I will say it: One can only change cards once in this game. Does that mean then that there is no problem after all? or am I misunderstanding your remark ?
Thank you again,
Nick.

Edited: I understand now ... the problem arises if you Deal the cards, Draw fresh ones and Deal again without using "Quit" ... I will try to eliminate the button "Quit" then and start the code "Draw" with calling "Quit" ans see what happens.
2nd edit:
It works BUT it is really slow ... I left the Quit button there after all to close teh game ... so now I have Call Quit at the top of the "Deal" code.

[mdmackillop]

HTH,
As well as being generally more efficient, you'll find shorter code is also easier to maintain or amend. You only have to make a change once. So if you find you need to clear the cards more than once, move that routine into its own sub and call it as required. One thing: add some comments to make clear what's happening. Makes life easier for us and yourself when you revisit in in a few months.

[mdmackillop]

Have a look at this in Help
Group Method (ShapeRange Object)

By saving the names of your shapes in an array, you should then be able to delete them without checking the position of evey shape. This will be of added benefit when you have all 52 cards.

[ndendrinos]

Thank you for the last words Malcom ... looked at grouping shapes and understood zero
but that's OK last week I had no idea about shapes at all.
I have an idea that might speed up things.
Instead of drawing 5 cards I will draw 10 (2 in each cell in [C1:G1]... one can only see 5 because the "hidden: ones are perfectly aligned with the visible ones.
I tested it and it is as fast as drawing 5

Now back to "DRAW" ... instead of fetching cards from B column all that's needed is a condition like this:
For each empty cell in [C2:G2] delete shape show above it.
The difficulty is to keep the shapes unnamed in the code ...
Can this be done?

[mdmackillop]

A slight variation, writing the pasted card names to the sheet.

[ndendrinos]

Hello Malcom ... this latest revision you've posted has problems ... not sure why but it does.I have revised mine too and got it to work by cheating a bit.

The right way :
if one would hold the first card then the second card should be replaced by the sixth in B:B If one would choose to hold the first and last card then the second card should be replaced by the sixth in B:B, the third with the seventh and the fourth with the eighth.

To gain back speed I decided that since the cards are randomized I might as well cheat and do this :

If C2 is empty replace C1 with the sixth, If D2 is empty replace D1 with the seventh ... and so on.,,,

Tried to replace my Hod (Dbl click) with your single click with toggle type change and failed... I'll keep at it because I prefer the single click.

Here is the latest ... probably needs tidying up still ... that too I will try and fix.
Thank you again

[ndendrinos]

Well I did solve the "single click" by using this:

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("C2").Address Then _
Call test
If Target.Address = Range("D2").Address Then _
Call test
If Target.Address = Range("E2").Address Then _
Call test
If Target.Address = Range("F2").Address Then _
Call test
If Target.Address = Range("G2").Address Then _
Call test
End Sub


Sub test()

ActiveCell.FormulaR1C1 = "HOLD"
Range("E4").Select


End Sub
[/VBA]

Shows you how badly one can write code BUT it woks.... and upon drawing fresh cards the "Holds" stay on and that's OK ( same as casino poker machine )