Spiff up my Lottery workbook

Topic

New Reply

I want to make a possibly simple modification to the attached Excel 2003 Workbook, a version of which I use in administering the large lottery pool at http://www.ussrankin.org/lottery. I had to strip quite a bit out of the attached version, since the full version is far too large to upload here.

I’m confident that I could figure out how to do the modification, but my higher Excel skills are rusty, and there would be a lot of tears involved. I’m hoping that an expert Lounger can do it with his eyes closed.

Look at sheet wk1 of the workbook. Button 985 at the top left runs Macro1, which puts a copy of the NewWk sheet into the leftmost position at the bottom of the workbook. I click this button when I need to create a new sheet, which happens late every Friday night.

Then I manually rename the new sheet ‘wkN’ where N is one more than the integer in cell L1 of the sheet on which Button 985 is pushed. The highest possible N is 14 (wk15 would never be reached).

I also manually put N into cell L1 of the new sheet. This updates a lot of fields on the new sheet, and gets it ready to accept data for the new week.

I’d like to automate those two manual tasks, so that clicking Button 985 not only creates the new sheet, but also renames it and puts the proper number into cell L1.

That is the main part of my need. Now for a refinement:

I’d also like Button 985 to be visible or active only during the time when I might need it. That time begins any time I update the sheet after 11:00 PM on the Friday shown on the sheet, and ends when I create the new sheet.

So what should happen is this:

When the leftmost worksheet is updated after 11:00 PM on Friday, Button 985 becomes visible and active.

When Button 985 is clicked, it should become invisible/inactive. Clicking it will create and name a new worksheet, and the new week number should be placed into cell L1. Button 985 on the new sheet should be invisible/inactive until the new sheet is updated any time after 11:00 PM Friday.

It’s not really necessary, but probably Button 985 on the NewWk sheet should always be visible, so as not to confuse those who look at NewWk.

This is all very slick, and I have a feeling it’s easy to do for somebody with the proper Excel skills. If you’d like to help, have at it!

PS – FYI, it’s just a matter of time before this application migrates to Excel 2010, but I’m in no hurry to do that. If there’s some major benefit to doing it now, please let me know.

Reply | Quote

Replies

All easy enough to do.. Have you tried recording this action to see how it works? What do you want to do with original 3 wk in the lower left corner? BTW, playing the lotto is NOT cost effective. I once ran a spreadsheet for a couple of years and decided to quit trying to win.

Reply | Quote

I got it to where it is by recording it. Renaming the new sheet based on an incremented number in L1 in the old sheet doesn’t seem to be recordable straightforwardly, nor does putting an incremented number in L1 of the new sheet. But as I admit, my skills are rusty.

We know all about the odds in the lotteries. It’s outlined in our Rules and Procedures page HERE.

More… I’ve fooled around with trying to pass a number from one sheet to a new one. Whatever is on the clipboard seems to disappear as soon as you create the new sheet.

Reply | Quote

OK. Post your current file with your current efforts and I’ll take a look.

Reply | Quote

It’s already up there. Look at the last line in the first post above.

I missed your question about the original wk3 sheet. The sheets just keep piling up. Each one is a record of one week’s history. When the pool is over after 13 or 14 weeks, I start a new workbook and save the old one.

PS – It’s not in our Rules & Procedures, but I’ve read somewhere that you’re more likely to be eaten by a shark than to win the Powerball or Mega Millions. 🙂

Reply | Quote

Taking another look at this it seems that you really should design this around a database and have only one fancy sheet where a mouse click will populate the week desired. Is this a commercial project?

Reply | Quote

I developed one once where you used conditional formatting to light up winners. And, if your lotto allows you can even use a macro to go get the winning numbers and place where needed. Your formula for counting winners could be simpler such as
‘=SUMPRODUCT(COUNTIF(B15:G15,LottoWinners))
and then the CF to light it up

Reply | Quote

The application does everything I need it to do. It has been evolved over five or six years, and has been heavily tested under real world conditions. All I’m trying to do is to implement a cool improvement that will save about 2 minutes a week.

Reply | Quote

Lou, You have a lousy attitude. “Don’t confuse me, my mind is made up”
However, the NON paying customer is always right so see attached.
It also has a macro to fetch the winning numbers on the winning number sheet

Reply | Quote

I’m just trying to be polite to a fellow who, in a response to my request for assistance:

[*]Says that what I want is easy
[*]Asks if I’ve tried recording something that isn’t really recordable
[*]Asks about a wk3 that doesn’t exist on my sample file
[*]Tells me that my whole project isn’t cost effective
[*]Asks me to post a file that is already posted
[*]Suggests a complete redesign of a system of which he has seen only a tiny piece
[*]Tells me I have a lousy attitude when I suggest that his proposal might not be so desirable
[*]In a somewhat impolite manner refers to non-paying customers
[*]Finally posts a file with a macro that seems to address part of my need, but unfortunately doesn’t work. The file also includes unmentioned, unexplained and unwanted changes to the sample file.

Sheesh! (But I suppose one gets what one pays for.) 🙂

The non-working code does contain a few helpful lines. If sheet NewWk is renamed as wk0 and a zero is plugged into its cell L1, this code will create new, properly renamed sheets:

Sub NewSheetWithIncrementedWkNumber()
Dim mn As Integer
mn = Mid(ActiveSheet.Name, 3, 2) + 1
Sheets(“wk0”).Copy Before:=Sheets(1)
[INDENT]With ActiveSheet[/INDENT]
[INDENT][INDENT].Name = “wk” & mn[/INDENT][/INDENT]
[INDENT][INDENT].Range(“L1”) = mn[/INDENT][/INDENT]
[INDENT]End With[/INDENT]
End Sub

I’ve uploaded a revised version of the sample file. Now that there’s a working version, I’ve rethought the stuff about the date and time. I don’t really need it, but I need something else:

Once it is pushed, Button 985 should become inactive. (If it is pushed again, it will try to create a second worksheet with the same name, which results in an error.)

Reply | Quote

Sheesh! (But I suppose one gets what one pays for.) Sure do. I didn’t ask to help you…….

You said you were going to update Friday nite at 11. Did you wait or change the variables or comment out the if /end if to check That it does work. I see that you decided to use my “non working code”. Sorry about adding improvements and a way to fetch the winning numbers.
I did show you code you could incorporate to hide or show the shape.

I was a USAF officer and now I remember why it is harder to get in the Air Force than the naby
‘=========
Option Explicit
Sub visbileyesno()’identify the shape name.
ActiveSheet.Shapes(“Button 17”).Visible = True ‘False
End Sub

Reply | Quote

Best might be if you just stopped helping.

Reply | Quote

You said
“If sheet NewWk is renamed as wk0 and a zero is plugged into its cell L1, this code will create new, properly renamed sheets:”

The code has no absolutely NO relationship to getting anything from the existing range(“l1”). Read it again. It gets the number from the sheet NAME.
I will now CEASE helping on this.

Reply | Quote