[Formula] Alternate between two value sets

[synergeticink]

MOD: Can you please move this to the VBA Q/A section. Sorry, wrong section.

Good morning,

I have a userform with one textbox and one button. When I click the button I would like the show a name in the textbox.

The names will alternate between two list of names. One list will have one name, and the other two names.
However, List B will alternate independently.
Ideally I would like to see Frank and Bill alternate, while Jon alternates between them. So lets say that I click the button 8 times, it should show Jon 4 times (50%), and Frank and Bill 2 times(25% each).

Example:

List A:
Jon

List B:
Frank
Bill

Trying to google help on this one is difficult haha

[Bernie Deitrick]

Use a public counter and increment it each time you need a new value:

Please Login or Register  to view this content.

[synergeticink]

Does this timer run independently in saved memory? I'm not familiar with public counters. Rather then "MsgBox strName" i'm assuming I could use the following code to input the new name, or something to that extent?

UserForm1.TextBox1.Text = strName

If it does keep it in memory, would excel keep track of that counter even if I save and close the document and reopen? Trying to think of situations in which it might not work. #thinkingahead

[Bernie Deitrick]

There is no timer - the code runs whenever you use code to run it - like a commandbutton click event. The counter does not retain its value when the file is closed: you could write the value to a cell and read it from there every time it is changed, if that is important.

And yes, you would use code like

UserForm1.TextBox1.Text = strName

in place of the message box.

[synergeticink]

Awesome! Worked perfectly. Thank you!