Userform in Switched on mode after Clicking Calculate Win button and automating monthly totals

M on the road for the day. Will get to check out on the computer later at night.

Till which year is added after 2024 for all the keno tabs and lotto workbook.

Till which year is added after 2024 for all the keno tabs and lotto workbook.

I'm sorry but I'm not sure what that means, so all I can say is that the Find Wins userform Years listbox will add the new year every year.

The form is modeless so you can work on the sheet whilst it is loaded. In the OK button this line hides the form Me.Hide so remove it.

I've added code to maximise or minimise the form which is useful when working like this


29252151c-3.xlsb

@ roy

The form is modeless so you can work on the sheet whilst it is loaded.

Not sure what you meant by modeless?

In the OK button this line hides the form

when you say this line which line were you referring to 

Me.Hide so remove it.

Not sure what that meant, 

I've added code to maximise or minimise the form which is useful when working like this

Thanks for adding it, but not sure how it would help as I can clearly see the Year, Month and Time Slots in below snapshot

@martin.

Requirements have been met.  Brilliant Job.  I not done thorough testing but looks good so far with whatever testing I have done.

The userform stays switched on and I can rapidly continue working on new sets of draws.

Thanks for adding a clear button and Monthly totals and highlight , as that is definitely helping speed up testing.

Happy New Year 2023.

Same to you and thank's for the nice testimonial.

In the attached workbook I:

1. Removed "All" from the userform since it didn't do anything
2. Added an error check for no data in month as in the case right now in this workbook for 2023
3. Corrected a bug where if there were no matches in row 3, the entire row was deleted including Year and Month

29252337a.xlsb

thank's for the nice testimonial. 

welcome

Yes you are right the All doesn't do anything.

Thanks for correcting the bug.

Win Calculator tab :  3pm draw not showing monthly totals and other draw picks also not showing monthly totals.  2 examples below.

The draw was a 3 pick draw : 1  2  3 in below picture

But on checking another 3 pick draw of 1pm which 10  12  13,  I noticed some had monthly totals but some were missing in below picture

Not sure why it is missing out some monthly draw totals.

I have not updated the workbook as it is the same 29252337a  workbook that I downloaded, which I was testing with more examples.

You said that you wanted the UserForm to stay on top so you could perform multiple tasks. The line Hi9de Me hides the userform, by removing the line it will stay visible.

Please look at the spacing between December and November. Which do you prefer?
1)
2)

Corrected.

I chose #1 because it is consistent with keeping a blank row between months.
29252337b.xlsb

You're right #1 is consistent.  Thanks, Will retest just waiting for server update.

@Roy 

Thanks for taking the time to look at the question. 

Have you tested 2022?

Yes I did  more testing now and 2022 is ok.

But previous years are still showing draws that  do not have wins like in below picture.

LIke the 2022, could the rest of the years also show only winning draws, because at the moment it is not .

I think the non winning draws are showing up for 2013 , 2012 and backwards.

You might wonder why I am checking so much backwards in time.

The reason for that was I was looking  for a winning ($1000)  6 pick draw for  70,  71, 72, 73 , 74 ,  75, but since I could not find it in the year 2022, I kept on testing and checking backwards, if the above 6 pick hit in the previous for 10 am. only.

I cannot believe that the above 6 pick draw did not hit in the last 10 yrs. from 2012 to 2022 for 10 am draw only

So in order to cross check that , I opened the Main tab and selected draw period from 1 Jan 2012 to Dec 2022,  but it is taking very long to process , just wondering if the process speed can be improved ?

could the rest of the years also show only winning draws, because at the moment it is not .

That's my fault. I neglected to consider that in previous years there were less than 4 draws per day.

 $90 figure incorrectly popped up in below picture.  $90 not required.

I selected 2022 and then Jan to Dec and 10am and tested out a 4 pick draw 70 71 72 73

While the Total $45 is correct,   $90 has popped up at the bottom of the Column P data in the picture.

I'll look at that problem soon, but first I want to talk about the problem you had where $0.00 values showed up when you tried to find winners for 2012.

That is happening because there are no times in the WNR and to compensate for that the code for the Wins Calculator tab assumes that there are always 4 draws per day. The oldest one is then assumed to be 10 AM, the next oldest one 1 PM, etc. In actuality (as you know) looking backward in time there are only 2 draws per day starting in Sunday 24 August 2014 and only 1 draw per day starting in Monday 10 May 2004 and so that scheme falls apart.

Given that

1. there are no times in the WNR, and
2. the changes occur mid-month and the Wins Calculator tab does things by month, and
3. the time of day for those early draws are different, and
4. at some future date a 5th draw might be added

the only thing I can think of to do is to create code that adds the times of the draws to column Y in the WNR each time a Keno file is downloaded from the Keno server and each time a manual draw is added. I can do that but it will take some time.

Please let me know what you think.

I've looked at the recent problem and I can't reproduce it.
It looks to me like the $90 is the total of everything above it including the subtotals. Please make sure you are using the 'b' version of the workbook and if so, please try doing it just for one month and then doing the full year again and let me know of the problem persists.

the only thing I can think of to do is to create code that adds the times of the draws to column Y in the WNR each time a Keno file is downloaded from the Keno server and each time a manual draw is added. I can do that but it will take some time.

That is a good suggestion. Yes take your time.

Instead of the Y column , would it possible to add the Draw times to a new Column before the A column in below picture .

It will be easy for me to cross refer Draw number and times if they are in adjacent columns.

No, there's too much existing code that depends on the balls being in their existing columns.

ok. I did not realise it is complex than that.  I leave it you then.

ok . I will try to see if the $90 is popping up again. If it does I will upload the workbook

You didn't actually attach your updated file but these are what I see in version b of my workbook. Which winning draws are missing?

And...

In the attached workbook:

1. You should no longer see the $0.00 results
2. Draw times now appear in column Y of the WNR
3. Among the many changes were some that might resolve the missing December 2022 wins

I tested the following:

1. Downloading a new Keno file
2. Selecting years and months in the Wins Calculator tab where there were 4, 2 and 1 draws per day
3. Manual draws

29252337c.xlsb

Sorry did not realise I had not uploaded the file.

Anyway after server updating to 3rd Jan and manually updating 4th and 5th Jan,  the results of the Wins Calcuator seems to match yours.  thanks

I have yet to test for backward years ,  just tested for 2022 with pick of 70 71 72 73 74 75

Since I have no access to desktop computer, I was trying the workbook on Office 365, but the buttons are not clickable, the cursor stays on the cell behind the button 

I found several solutions that talk about rebooting and/or disconnecting the laptop from the dock.

I do not have a laptop dock. 

What was the solution about rebooting.?

Just simply rebooting your PC.

29252337c 4 resolve.xlsb   

Getting error when clicking on Calculate win in what if tab.

Also I was sure you had changed the date format for nz, but is as follows

I'm embarrassed to have to ask but do you know how the data gets to the What If tab?

No worries. In the What if tab, when you click the Calculate Win tab, it brings up a userform where you can type in the draw numbers

 Correction :  the What if tab , depends on the draw typed in D:M of the Record tab.

When any draw row of 10,1,3 or 6 is clicked , then click on what if in the Record tab and that opens up What if tab.

but comes up with this dialog inspite of selecting the cell of the row

Now Jan 23 cannot load in What if tab, due to the above error

the runtime error 91 popped up with the default draws loaded.

It seems to work properly now.
29252337d.xlsb

Thanks. I did not get to test as I am sick got COVID fever , sore throat and very bad cough 

I was trying to use the number palette in the Main tab in Office excel 365 on laptop.

However the following error popped up

I remembered to click on debug and saw the following highilight

So unable to use the number palette.

Workbook updated manually in Office 365 on laptop  till yesterday attached.

29252337d.xlsb 28 Jan 3pm.xlsb

Each of the gray buttons on the userform has a name. For example the button that shows 11 is named cmd11, and the button that shows 52 is named cmd52, etc. When the userform is shown those button names are stepped through one by one starting with cmd1 and ending with cmd80. Unfortunately when I created that userform I named the 64 button cnd64 instead of cmd64 and so the error was generated when the code couldn't find the cmd64 button.

In the attached workbook I've corrected that and made sure that all the other buttons are all named correctly.
29252337e.xlsb

Gotcha.  the e workbook worked thanks

In the Wins Calc tab, I tried to paste the number generated from the Main tab Show Palette, but all the nine numbers got pasted into one cell.   

So the request was if it was possible to instead have another number generator that can be integrated into the above userform in the Wins Calc tab, as typing the numbers in the above blank cells is getting time consuming.

I got a overflow error message in picture below

On clicking debug following is the display

In the Wins Calc tab, I tried to paste the number generated from the Main tab Show Palette, but all the nine numbers got pasted into one cell.

The selections from the palette in the Main tab show up in the Typed Nums. I can add a button at the bottom of the Find Wins userform that will copy each of the Typed Nums to that userform. Would that be acceptable?

If not then the creation of a palette in the Wins tab is non-trivial and I need you to ask a new question.

If not then the creation of a palette in the Wins tab is non-trivial and I need you to ask a new question.

I have posted the new question.