Displaying a ‘tip of the day’ every time a user opens a workbook can be quite useful. This can be done quickly and easily using Excel VBA.
- First we create our tips. We have created 10 tips to be displayed
- Next we hide the list of tips by hiding the column that contains them.
- Since we have 10 tips and wish to display a unique tip every time the user opens her workbook we generate a random integer between 1 and 10 using the RND function along with the highest (upperbound) and the lowest (lowerbound) values of 10 and 1. Of course you can have all the rows filled with tips. Then you’ll have to define upperbound, lowerbound and ‘tipOfTheDay’ variables as long to be able to accommodate more than 2 million tips. An integer can accomadate only 32767 tips.
- Once the random number has been generated you can use a message box and display the tip
- To avoid that the same tip is generated every time you can initialize the RND function by using the ‘RANDOMIZE’ statement whic uses the system timer to initialize the RND values
- Have a look at the code below and also watch the video to see how the tip of the day is displayed using Excel VBA
The complete VBA code:
Private Sub Workbook_Open()
Dim upperbound, lowerbound, tipOfTheDay As Integer
upperbound = 10
lowerbound = 1
tipOfTheDay = Int((upperbound – lowerbound + 1) * Rnd + lowerbound)
MsgBox Range(“O” & tipOfTheDay).Value, vbOKOnly, “Tip of the Day”
Watch the video: