MsgBox Random Title Generator

I have a xlam file that users open within their current worksheets that adds a new custom ribbon, with approximately 12 buttons of assorted macros for them to format and perform certain calculation tasks in their worksheets. I am a complete novice at VBA, and am trying new things out. I have recently made the buttons somewhat idiot proof, so if the users click them in inappropriate cells, a msgbox will come up and instruct them how to proceed.

So for each button, I have made custom msgboxes depending on what needs to happen, and sort of as an Easter egg, I'm using funny titles on each box. Well, I thought it would be better if instead of programming each box separately, I could have a list of titles, and each box would randomly pick a title to use when activated. Is there a simple way to do this?

Hi Winon,

Yes, that should be fine. I have 12 different scenarios of errors that could occur, so I figured if I can generate a list of 25 or more funny titles, hopefully the randomness would be good enough that it would cycle through most of them without repeating too much.

Originally Posted by shg

I would focus on getting your code to work flawlessly, and hold off being clever until it's rock solid.

I've been working on this project for over 6 months and finally rolled it out into Beta testing last week. So far, it's running as planned with no complaints. So, I've been adding new features and optimizing codes for the next version.

Hi Winon,

Thanks for the effort to help, but this isn't quite what I'm looking for. Since the users have their own workbooks open, I can't use Vlookup, and write any code into any specific cells on an excel sheet, therefore I must do this solely in VBA. The XLAM file does not have any actual sheets to work with, it's just full of modules that tie into a custom ribbon filled with macros. So in the case of the msgbox I'm working with, Here's an example:

msgbox1.png

So the title that says "Oops!" I want to have this randomized with a list of 25+ different sayings.

Here's a sample snip-it of the code I have that generates this:

If Range("C3").Value = "Sheet Formatted" Then
        MsgBox "This function has already been performed", vbExclamation, "Oops!"
        Exit Sub
    End If
    
    If Range("L1").Value = "New Export" Then
        Call ProceedFormat
            Else
                MsgBox "Wrong format selected. Please check your worksheet type", vbExclamation, "Oops!"
    End If

Thanks @shg! I had an idea in my mind, but wasn't sure how to actually make this work. I haven't really played with Functions before.

Your code seems to work great, however... I put my own title words in the split (14 total so far) and tested this out for 5 solid minutes. All words showed up on my msgboxes, except the first word in the list. To experiment, I changed the + 1 at the end of the code to a + 0 to see what would happen, and with a second test, all words showed up except the last word in the list now. So was wondering what that + 1 (or +0) actually does and if there's a way to reassure all words in my list will appear at some point?

Thanks again for your help!

My logic is probably wayyyy off, but I figured if +1 gets all values except the first in the list, and +0 gets all values except the last in the list, I must go in between, so I tried +0.5, and it seems to work. All 14 of my values have now shown up during my testing.

Well I thank you again for your help. As stated, complete and total novice here. only training (if you wanna call it that) I've had in VBA is through excel's Macro recorder and then weeding through some codes line by line to see what they do, and let me determine if some lines are un-necessary, and I of course turn to this forum and google to learn new stuff and figure out how to optimize my macros. I'm not good at programming and don't understand much of it at all, but it's been fun doing this stuff and learning as I go.

Originally Posted by shg

I will give you one hint: Using 0.5 makes the first and last items half as likely as those in the middle of the array.

Ugg, OK, not nearly what I thought it was doing. Other values I tried such as 2-9 sometimes the box would come up, other times I got an "Out of range" error. Anything 10 and up wouldn't work at all. I'll ponder through this tonight and see if anything comes to me.

Originally Posted by shg

Good job. When you fix my mistake, you will never make it yourself.

Well, pondered through this a little yesterday, and a good part of this morning, and I just don't really have any idea what I'm doing. I thought I was on to something with the 0.5 until you told me what that impacts. So after a bunch of Google searching on int rnd functions, I'm getting many results that are for generating numbers, which isn't what I'm doing. I've also seen things about lowbound and highbound statements, I see there's a Ubound statement in this code, but not Lbound, and anything I tried to put together ended up not working . So back to the last value of the code, I tried - 0 instead of + 0 and that seemed to give me everything in my list except the last item. - 1 gives me an "out of range error".
I'm not quite understanding what this last part of the code is doing, so I really have no idea how to proceed.

Originally Posted by shg

The Split function returns a zero-based array.

Rnd generates a decimal number uniform in the interval [0,1) (i.e., it includes 0 and excludes 1), so n*Rnd generates a number between [0,n)

Int() returns the whole number just <= its argument.

So to generate a random number uniform in the range of say [0,10], how would you combine those three functions?...?

Sorry, this is all greek to me. I never had any schooling or any training on this kind of stuff. based on what you've said here, this is how I concluded one of my thoughts that I would need to make it be + (total number of my items) but that's not the case either.

I do thank you for your help and willingness to teach me, but I'm just not catching on.

Sorry, habit I guess. Wow, so it's just wrapping extra parenthesis around the second half of the code huh? See, I was wayyy off.

Thanks again!

shg, I stumbled on this thread yesterday and the concept intrigues me, as I am just now beginning to work with defining "custom" functions...

I just wanted to ask before I screw up my code in my project, does this portion of code:

Function RandTitle() As String
  Static avsTitle As Variant
  
  If IsEmpty(avsTitle) Then avsTitle = Split("Darn,Drat,EGAD!,Holy Cow!,Shoot!,Not so fast!,Oops!,Slow down!,That won't work", ",")
  RandTitle = avsTitle(Int(Rnd() * (UBound(avsTitle) + 1)))
End Function

need to be placed in its' own section of the full code like a sub, or does it go inside the sub you want to use it in? I'm assuming it is separate, and only needs to be inserted once, is that correct?

Also, if I wanted to use this "trick" on more than one message box with a different list of random titles for each, would I just duplicate the code with different variable names, like so?

Function SecondRandTitle() As String
  Static avsTitle2 As Variant
  
  If IsEmpty(avsTitle2) Then avsTitle2 = Split("Nope,Nice Try,Close but no cigar,Better luck next time", ",")
  SecondRandTitle = avsTitle2(Int(Rnd() * (UBound(avsTitle2) + 1)))
End Function

And finally, is it possible with the above code to use a comma within one of the random title options, for instance if you wanted to use "I don't think so, Scooter!" as one of the options? If so, how would you handle that?



(Mods - Sorry if this should be a separate thread, it seemed like it would be better to continue the discussion here for relevancy... If I was wrong, let me know please.)

Cool, thanks!

Hi Shg,

Is there a way to get a more randomized way to do this? I'm noticing that every time a session is started, whenever error messages are pop up, it always generates the titles in the exact same order.

Great, thanks so much!