Link to home
Start Free TrialLog in
Avatar of simon
simon

asked on

Creating editable number lists and ability to autopopulate to row 2 of the MG tab via shortcut key

Requirements


Creating editable number lists using a number palette or another suitable method which can then be locked for maintenance, after edit is complete


And autopopulate the required number lists to  Row 2,  AJ:BC of the MG Tab via a shortcut key or assigning a number shortcut.


I wished to autopopulate a custom number list that I have as below and wish to be able to add more to the list and then autopopulate that to MG tab , row 2 of AJ:BC

A general examples of a number list.

20 21 30 31 40 41
7 8 9 18 19 20
5 20 35 55 60 75


and then in future would like to add more numbers to the customised number lists


"a" workbook attached 

29254705a Working Book 18 Feb 23.xlsb


Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Before I start, please see if this workbook works for you.
29254705d.xlsb
Avatar of simon
simon

ASKER

after updating the draw manually in your "d" workbook  till today's date  and selecting draw period , it simply freezes, meaning the workbook does not accept the cursor into any cell and if I clicked manual draws tab and hit by draw, then runtime error 1004 pops up like before.


Did the "d" version that I uploaded work before you made the updates?
Avatar of simon

ASKER

No I updated the "d" workbook manually and then selected draw period after which there was run time error

Avatar of simon

ASKER

I downloaded another copy of "d" workbook and without updating manually tested and still it freezes and does not accept the cursor in the cell, so same issue as explained before

I think you need to have a professional look at your PC because I think there is something wrong with it. Until then there's nothing I can do.
Avatar of simon

ASKER

However the "a" workbook works fine.


Maybe you could use the "a" workbook.


What might be wrong on your side of the PC if you use the "a" workbook ?



Hi,

To create editable number lists and autopopulate them to Row 2, AJ:BC of the MG Tab via a shortcut key:

You can use a number palette or another suitable method. Once the edit is complete, you can lock the list for maintenance. To autopopulate the number lists to Row 2, AJ:BC of the MG Tab, you can use the following oneliner code:

Range("AJ2:BC2").Value = Array(20, 21, 30, 31, 40, 41) 'Replace with your number list

Open in new window


You can assign this code to a shortcut key for easy access. If you want to add more numbers to the list in the future, you can simply modify the code accordingly.


Cheers
Avatar of simon

ASKER

Thanks for the code, however I don't know where to place the code in the workbook for it to work as I am an excel novice, that is why I had uploaded the workbook

Hi,

To use the code to autopopulate the number lists to Row 2, AJ:BC of the MG Tab in your workbook, follow these steps:

- Open the workbook and press ALT + F11 to open the Visual Basic Editor.
- In the Visual Basic Editor, select Insert -> Module to create a new module.
- Paste the code provided by the previous answer into the new module.
- Close the Visual Basic Editor and return to the workbook.
- Go to the MG tab and select the cell in Row 2, Column AJ.
- Press the shortcut key you assigned to the code or run the code manually by selecting - - - Developer -> Macros -> [Your macro name] -> Run.

The number list should be autopopulated to Row 2, AJ:BC of the MG Tab. You can modify the code by replacing the numbers in the Array with your customized number list.


Cheers
Avatar of simon

ASKER

@ martin


I tested out the "d" workbook on the laptop, it looks like it has an issue.


Following is the error message that I got when selecting the draw period in the main tab and clicking ok


User generated image

I don't think it is the PC as on the laptop also it is showing error as in above picture

Avatar of simon

ASKER

 Go to the MG tab and select the cell in Row 2, Column AJ.

As soon I followed your above step, then an error popped up below.





User generated image



Avatar of simon

ASKER

Press the shortcut key you assigned to the code 

how do I assign a shortcut key ?

My apologies. I'm experiencing the same problems as you so I'm starting again with the "a" version. Is it true that you want to store sets of numbers somewhere and be able to add/modify/delete a set, and also be able to choose one of the sets and have the contents transferred to AJ:BC of the MG Tab?

Are you okay with doing that by way of a userform rather than a shortcut key?

This assumes what I asked about in my previous comment was okay.

In the attached workbook you will find a new tab called "MG Sets". In that tab you can add sets, modify sets, delete sets, and modify data in the sets. In the MG tab Monitored Numbers section you will find a new button called "Select Set" (the "Row Selection..." button has been moved to the Continuous section).

When you click the new "Select MG Set" button you will see a new userform that contains a combobox, and to choose a set, click the down-arrow in the combobox, select the entry you want and then click Okay.
29255123.xlsb
Avatar of simon

ASKER

That is perfect.

Lost the highest ranked numbers


However  I lost the highest ranked numbers in row 2 {AJ:BC)


In the MG tab, in the default mode the highest ranked numbers autopulation is lost ,  so what I see now is only two switches.


First is  the latest draw which populates if I click the best bets or the other which is select set numbers, but the one that is missing is the highest ranked numbers.



Avatar of simon

ASKER

The default mode is now the "selected set" in row 2

but when I click the restore it is missing the restore of the highest ranked numbers.


Avatar of simon

ASKER

Also a runtime error 91 pops up


I did the following


Created a set 11 12 13 14 15 16 in MG sets

and then clicked the select set and that worked fine.

But when I click on the duplets button in the mg tab.


the runtime error 91 pops up in below picture

User generated image


Scenario 1:
The 3rd button says Best Bets
If you click the button in that state the numbers in AJ2:BC2 are stored in a hidden tab and AJ2:BC2 are replaced with the best bets from the latest draw

Scenario 2:
The 3rd button says Best Bets
If you click the 4th Select Set button the numbers in AJ2:BC2 are stored in a hidden tab and AJ2:BC2 are replaced with the selected values. One change in this workbook is that the combobox will appear blank but the dropdown arrow will still show you the selectable sets.

Scenario 3:
The 3rd button says Restore MN.
If you then click the Select Set button the 3rd button is changed back to Best Bets and AJ2:BC2 are replaced with the selected values.
29255123a.xlsb
Scenario 4:
The 3rd button says Restore MN.
Clicking it restores the numbers from the hidden tab.
Avatar of simon

ASKER

User generated imageThe above is the highest ranked MN numbers (6,61...15 13) when draw period is selected.

And after clicking the best bet button, it populates the latest draw which is fine.

Even the select set button is working fine.

But the original set of MN numbers 6 61..............15 13 does not come back again on clicking restore MN button.


This test is based on the latest draws updated till yesterday from the lotto server site.


And also error 91 still pops up in below picture


User generated image





Avatar of simon

ASKER

I tested it using latest lotto file update for the workbook updated till yesterday 6pm


10748b0c-b08f-11ed-aadb-005056819165.xlsx

But the original set of MN numbers 6  61..............15  13 does not come back again
Click the Best Bet button again.

I'll look into the error.
Avatar of simon

ASKER

Click the Best Bet button again.

Yes I did, but the MN numbers does not restore.


It toggles only between two states, instead of three



1) Latest draws

OR

2) Selected draw set.


the third toggle currently not happening  is restoring MN numbers



It toggles only between two states, instead of three
It is not meant to have 3 states, just two, Bets Bets and Restore MN.

The problem with the duplets will take some time to fix because either by design, or lack of forethought on my part, it currently only works if all 20 numbers in row 2 are filled in.
Avatar of simon

ASKER

ok I tried multiple times , but the MN numbers are not getting restored.


User generated image

61 .......38  were MN numbers when I selected the draw period. when I clicked the best bets button, it switched to the latest draws which is correct and when I clicked restore MN button it returned to the MN numbers.


But the moment I clicked the select number set and tested it out and then tried to go back to the MN numbers (61..38), it was never there .


The MN numbers are not getting restored.


And yes you are right the duplets and triplets  have stopped working.

These are pictures from my workbook where the most recent draw is Sunday 19 February 2023 6:00 PM.

Best bets in place.
User generated image
Selected set , 8 , 9 etc.
User generated image
Clicked Best Bets
User generated image
This workbook now shows duplets and triplets for the selected sets.
29255123b.xlsb
Avatar of simon

ASKER

The duplets and triplets work now. thanks


Picture 1 (top ranked numbers does not come back)



These are pictures from my workbook where the most recent draw is Sunday 19 February 2023 6:00 PM.


When the draw period is selected,  the MN below is what comes up first which are the top ranked 20 numbers


1)  Picture one - the top ranked numbers displayed

User generated image

2)   Picture two-  the most recent draw of 19Feb 6pm is displayed

User generated image


Selected set in below picture.


3) Picture three, clicked on select set.

User generated image

4)  Picture Four

Clicked on Best bets and got back picture two below

User generated image

5) Picture 5

Clicked on Restore MN Numbers, but got the selected set (21 22 23 24 25 26) again instead of picture 1 which are 20 MN numbers 6 61 57 24 4 79 78 45 23 20 17 10 7 53 511 38 30 18 15 13.


Picture 5

Got the select set numbers instead of restoring MN numbersUser generated image



So my point is after I click on the Select set button,

I see the select set numbers,

But now when I click on the MN restore , it does not display the below

the MN numbers 

6 61 57 24 4 79 78 45 23 20 17 10 7 53 511 38 30 18 15 13.




I just realized that my implementation of "best bets" in your question prior to this one was flawed in that all it does is to copy the numbers from the most recent WNR draw. So based on that I think we should get rid of the "Best Bets" button and just have three buttons which would be Duplets, Triplets and a button which initially says "Select Set" but after clicking it it would say "Restore" and when clicked in that state, it would bring back what was previously in AJ2:BC2.
Avatar of simon

ASKER

ok

I'm sorry but that won't work either because it would not allow you to choose a second "Select Set".

It's my fault but I've gotten confused gotten confused about what you want to see in AJ2:BC2, so let me ask you this - when you Select Draws on the Main tab what are the numbers that appear in AJ2:BC2?
Avatar of simon

ASKER

After selecting draws on the Main tab , the 20  top ranked numbers appear which populates by default which is what always happen and is correct.


And then I would click on the duplets or triplets and the green highlights would show first at row 4 , then row 5, row 6 and so on.


I am not sure why you called the button "Best Buttons"


Perhaps it could be called "Latest"  to refer to the last draw.


So if I clicked on the "latest button" it would show the last draw and the other name could be "default"


So the toggle button could be between "latest" and "default".


While the "Select Set"  button allows me to choose the set of numbers from the MG sets tab.


So for clarity.


The two states with the toggle button are "latest"and "default"


or you could rename default as "restore"


But after I click the select set button and now try to go back to "default" state .


It does not do that.



Toggling should work between "latest"  ,  "default" or "restore" and "select set" states


So whichever state I click I should able to get back to one of the above 3 states.













After selecting draws on the Main tab , the 20  top ranked numbers appear which populates by default which is what always happen and is correct
But isn't that in columns O2:AH2? I'm interested in AJ2:BC2.

I am not sure why you called the button "Best Buttons"
I didn't; I called them "Best Bets". I'd like a name that refers to the contents of AJ2:BC2 after Select Draws. Are you saying that that state should be called "Default"?

Would you be okay if there were five buttons in Monitored Numbers section, namely Duplets, Triplets, Default, Latest and Select Set?
The function of the last 3 would be:
Default: When clicked the numbers that originally appear when Select Draws is done would show up.
Latest: When clicked the numbers from the latest WNR draw would appear.
Select Set: When clicked you would (as now) be able to select one of your sets and have those numbers show up.
Avatar of simon

ASKER

But isn't that in columns O2:AH2? I'm interested in AJ2:BC2. 


No that one is different.  That one is Keno Number Frequency provided by the lotto server.  Everytime I have to update the Get top 20

Are the 5 buttons I proposed okay? If not then what functionality is missing?
Avatar of simon

ASKER

I didn't; I called them "Best Bets". I'd like a name that refers to the contents of AJ2:BC2 after Select Draws. Are you saying that that state should be called "Default"?

"Default"  state only because the top 20 numbers that you have created ( is the best and most populous numbers that you generated) occurs only when the Draw period is selected in the main tab.

Okay I understand that but what about my proposal for  Duplets, Triplets, Default, Latest and Select Set?
Avatar of simon

ASKER

Would you be okay if there were five buttons in Monitored Numbers section, namely Duplets, Triplets, Default, Latest and Select Set?
The function of the last 3 would be:
Default: When clicked the numbers that originally appear when Select Draws is done would show up.
Latest: When clicked the numbers from the latest WNR draw would appear.
Select Set: When clicked you would (as now) be able to select one of your sets and have those numbers show up.


yes that is correct and ok


And at the moment what is happening is in your last workbook, that after clicking "Select set" button,  if I wish to  check the default 20 numbers those numbers are missing meaning they don't come back.

 

ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of simon

ASKER

This one works. thanks


I usually use the main tab time filter for the 1 to 80 


The selected draw period was 1 Jan 23  to  22 Feb 23


So I was watching for the number 11  53  which came 6 times in the MG tab for 3 pm and 6pm time filter.


Based on the time filter in the mg tab, I get correct results of the number of duplets  11  53  highlighted which is 6 times.


But when I want to to check for the same times 3 and 6pm using the time filter ,  the pop up message  says there are no 11   53  duplets that has come more than 4 times which is not correct.


So not sure why the 1 to 80 time filter is unable to detect that 11  53 has come more than 4 times.


In addition I get an error , when applying the time filters for 1 to 80 tab in below picture


User generated image



Debug picture


User generated image





My workbook doesn't have any draws after Feb 19 and when I tried to replicate your problem using 1 Jan 23 to 19 Feb 23 it worked as expected, so please upload your current workbook.
Avatar of simon

ASKER

Current workbook till 22/2/23 upto 1pm updated


29255123c 22 Feb 23.xlsb

I opened the 22 Feb workbook, went to the Main tab, opened the Time Filter, selected 3 PM and there was no error. Did you do something different from that?
Avatar of simon

ASKER

No same as you did. Most likely it would have happened for shorter draw period selection, but  I will test it out again after updating to the latest draw and will let you know if issue comes again.