Microsoft Excel Tutorial: Display an in-cell tooltip based on a calculation.
To download the workbook from today: Excel Cell Tool Tip Calculated On The Fly - Episode 2631 Sample Files - MrExcel Publishing
Rather than type the code, download the workbook and copy it from my workbook.
Welcome to episode 2631 of MrExcel's netcast! In this video, we will be discussing an amazing idea that was sent in by one of our viewers on YouTube. The idea is to have a cell tool tip that is calculated on the fly, meaning that the recommended value in the tool tip changes based on the data entered. This is a great way to make data entry more efficient and accurate.
In this old video from 2019, I showed how to create a cell tool tip. However, after receiving this suggestion, I knew I had to share it with all of you. So, let's dive into how to create a cell tool tip that is calculated on the fly. We will be using data validation, specifically the input message feature, to set up a tool tip that appears whenever we enter a specific cell. But, the real magic happens when we add a macro that changes the input message based on the data entered.
Before we get started, make sure your workbook is saved as an XLSM or XLSB file and that your macro security settings are set to allow macros. Then, we will go into the VBA editor and add some code that will run every time we change the selection in the worksheet. This code will check if the selected cell is one of the input cells we specified and if it is, it will delete the current validation and add a new one with a title and message that is calculated based on the data. This means that as soon as we enter the cell, the tool tip will change to display the recommended value. We will also show you how to use this feature for a repeating calculation, such as suggesting a bonus based on a quota. So, make sure to watch the full video to see this amazing tool tip in action. Don't forget to like, subscribe, and leave a comment down below with any questions or comments. Thank you for watching and we'll see you in the next episode of MrExcel's netcast!
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
Table of Contents
(0:00) Cell Tooltip from a Calculation
(0:20) Adding ToolTip using Validation
(0:40) Tour of the Sample Worksheet
(1:26) Save as XLSM
(1:39) Change Macro Security
(1:51) Open VBA Editor and Find Code Pane
(2:50) How Selection_Change Macro Works
(3:19) The Code to Change Input Message
(3:56) Demo of it Working
(4:52) Alternate code to look to cell to the left
(5:20) Using Intersect in VBA
(5:56) Demo of ToolTip based on cell to left
(6:35) Clicking Like really helps the algorithm
To download the workbook from today: Excel Cell Tool Tip Calculated On The Fly - Episode 2631 Sample Files - MrExcel Publishing
Rather than type the code, download the workbook and copy it from my workbook.
Welcome to episode 2631 of MrExcel's netcast! In this video, we will be discussing an amazing idea that was sent in by one of our viewers on YouTube. The idea is to have a cell tool tip that is calculated on the fly, meaning that the recommended value in the tool tip changes based on the data entered. This is a great way to make data entry more efficient and accurate.
In this old video from 2019, I showed how to create a cell tool tip. However, after receiving this suggestion, I knew I had to share it with all of you. So, let's dive into how to create a cell tool tip that is calculated on the fly. We will be using data validation, specifically the input message feature, to set up a tool tip that appears whenever we enter a specific cell. But, the real magic happens when we add a macro that changes the input message based on the data entered.
Before we get started, make sure your workbook is saved as an XLSM or XLSB file and that your macro security settings are set to allow macros. Then, we will go into the VBA editor and add some code that will run every time we change the selection in the worksheet. This code will check if the selected cell is one of the input cells we specified and if it is, it will delete the current validation and add a new one with a title and message that is calculated based on the data. This means that as soon as we enter the cell, the tool tip will change to display the recommended value. We will also show you how to use this feature for a repeating calculation, such as suggesting a bonus based on a quota. So, make sure to watch the full video to see this amazing tool tip in action. Don't forget to like, subscribe, and leave a comment down below with any questions or comments. Thank you for watching and we'll see you in the next episode of MrExcel's netcast!
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
Table of Contents
(0:00) Cell Tooltip from a Calculation
(0:20) Adding ToolTip using Validation
(0:40) Tour of the Sample Worksheet
(1:26) Save as XLSM
(1:39) Change Macro Security
(1:51) Open VBA Editor and Find Code Pane
(2:50) How Selection_Change Macro Works
(3:19) The Code to Change Input Message
(3:56) Demo of it Working
(4:52) Alternate code to look to cell to the left
(5:20) Using Intersect in VBA
(5:56) Demo of ToolTip based on cell to left
(6:35) Clicking Like really helps the algorithm
Transcript of the video:
What an absolutely great idea sent in on YouTube.
Can we have a cell tool tip that is calculated on the fly? This is an old video from 2019 where I showed how to do a cell tool tip and someone said, "Hey, can we have that recommended value appear in the tool tip that changes based on data"?
Let's just talk about how to do a cell tool tip. We're going to go to Data Validation.
So on the Data tab, out here on the far right, Data Validation, we're not going to change anything on the Settings tab.
We're just going to go to input message and type some sort of a title and then a message and click OK, all right, and that sets up a little tool tip that appears anytime we go into that cell. I don't know what their worksheet looks like.
He said that he had more than one box and the calculation was different for each one.
So I have an input cell here.
And based on the weekday and the name of the person that uses a couple of lookup tables over here and for Chris on a Friday says, "We suggest you order three of the fish". Then down here, two dice.
When we enter this cell based on the current value of those dice, it'll use a lookup table here and come up with that recommendation.
Our goal is anytime we enter one of these input cells, we want to run a macro that changes the input message on the fly.
It's likely that you've never run macros before. You have to look up here.
Your workbook is definitely currently saved as XLSX, and macros will not be saved in that.
So all the work you do from here on out will be useless unless you do file save as and change this to XLSM or XLSB. Don't skip that step.
And then Alt+T for Tom, M for macro, S for security.
Your settings currently are on the top one. You need to change to the second one.
Don't skip that step.
And then we'll go to VBA with Alt+F11, your VBA will probably start like this.
We want to go to View and Project Explorer, find the workbook that you have. My workbook's right here.
Open that workbook, the sheet where you want this to work.
So I have a sheet called Demo.
I'm going to right click on demo and say View Code.
This is a special code pane for something called Event Macros. We have two dropdowns up here.
We're going to change the left dropdown to worksheet.
And look at that, it automatically went to SelectionChange, which is what I want.
What happens is every time that we change this selection, this little macro will run and it gives us a variable, an object variable called Target.
And so in our case, we want to make sure that our code only runs when you're in one of those two cells.
Put up a little bit of code here that checks to see if the target addresses D7 and displays one message or if the target addresses F18 and displays a different message.
I just want to show you how this works.
So what happens is, as I move around, the spreadsheet is running that macro over and over and over again, but nothing is happening until I get to that cell. Then we get a message that we're in D7.
Click OK.
And then down here, as soon as I hit to that cell, we get a message that we're in F18.
So just so you see that it's a pretty cool little macro that will only basically do something when it hits one of the cells that we've specified in the VBA.
Let's go back to VBA, and I'll paste in some other code.
All right, so when we touch either one of those two cells, D7 or F18, we're going to delete the current validation.
We're going to add a validation called xlvalidateInputOnly.
Here, in the title, we're going to say "We Suggest".
And then the message is going to come from my calculation out in O4. That'll suggest that Chris should order four fish.
If they haven't did F18, we're going to suggest whatever value came out of M16.
Now, before you run this, it's best to close this window with an X, come back to Excel and then what we should see here. So let's change Chris to Andy.
And as soon as I hit that cell, "We suggest you order five of the fish".
Andy, there's the VLOOKUP for Andy.
"We suggest you order five of the fish". So as soon as I touch that cell, they changed the input message.
If I would choose someone else, Diane, and come to that cell, "We suggest you order two of the fish," and it's getting that from the VLOOKUP table, right?
So this is really cool.
What it means is that as soon as I enter that cell, they look at the calculation, they change the input message, and it will change on the fly.
I love this idea.
Down here, where we have the two dice right now, they're coming up to four, so it's going to suggest Hank.
When I enter the cell, Hank is suggested.
If I come out and press F9 to get someone else, there's five and four is nine. That should suggest Mike.
Mike is suggested.
What I'm hoping though is that we have some sort of a situation where we have a repeating calculation.
So here's quota and sales and percent to quota.
And over here, in the bonus, I want to suggest a bonus based on the quota to my left.
All right, so in this case, we're going to run this code every time that's between E2 to E25. Let's go take a look at the code I wrote for this.
This is on a worksheet called Bonus, so I come here to Bonus and View Code.
All right, now, rather than check for each individual cell, I use this cool function called Intersect.
So whatever cell we just selected and the range of E2 to E25, if there's no intersection between them, if it's nothing, then we just exit the sub.
Otherwise, no matter what cell we're in from E2 to E25, we take a look at the selected cell offset of 0 rows and -1 column, so in other words, the cell to the left.
If that value is less than 0, it's going to suggest no bonus.
If that value is less than 10%, a $50 bonus, less than 25%, a $100 bonus, and if they're more than 25%, it's going to suggest the $250 bonus.
Once we have that suggestion message there, then we delete the old validation, add inputOnly, put a title, put the message, and ShowInput = True.
Again, we'll close this with the X and then come back and you'll see, like here, it should say, "No bonus, no bonus, no bonus". That'll be the $250 bonus down here, 20%, a $100 bonus.
Let's see if there's someone, there's someone with a 1%.
When we select that, it suggests the $50 bonus.
What a cool idea to have a tool tip that appears and calculates on the fly as you enter this cell.
I wasn't sure that that macro would be able to run before the tool tip is displayed, but it is working great.
Thanks for sending that question in.
I always say in the videos, please like, subscribe, ring the bell.
And if you have any questions or comments, leave a comment down in the comment below.
When this comment came in two days ago, I was like, "Wow, that would be really cool if we can make it work". Well, I thank you for stopping by.
We'll see you next time for another netcast for MrExcel.
If you like these videos, please, down below, like, subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below.
Can we have a cell tool tip that is calculated on the fly? This is an old video from 2019 where I showed how to do a cell tool tip and someone said, "Hey, can we have that recommended value appear in the tool tip that changes based on data"?
Let's just talk about how to do a cell tool tip. We're going to go to Data Validation.
So on the Data tab, out here on the far right, Data Validation, we're not going to change anything on the Settings tab.
We're just going to go to input message and type some sort of a title and then a message and click OK, all right, and that sets up a little tool tip that appears anytime we go into that cell. I don't know what their worksheet looks like.
He said that he had more than one box and the calculation was different for each one.
So I have an input cell here.
And based on the weekday and the name of the person that uses a couple of lookup tables over here and for Chris on a Friday says, "We suggest you order three of the fish". Then down here, two dice.
When we enter this cell based on the current value of those dice, it'll use a lookup table here and come up with that recommendation.
Our goal is anytime we enter one of these input cells, we want to run a macro that changes the input message on the fly.
It's likely that you've never run macros before. You have to look up here.
Your workbook is definitely currently saved as XLSX, and macros will not be saved in that.
So all the work you do from here on out will be useless unless you do file save as and change this to XLSM or XLSB. Don't skip that step.
And then Alt+T for Tom, M for macro, S for security.
Your settings currently are on the top one. You need to change to the second one.
Don't skip that step.
And then we'll go to VBA with Alt+F11, your VBA will probably start like this.
We want to go to View and Project Explorer, find the workbook that you have. My workbook's right here.
Open that workbook, the sheet where you want this to work.
So I have a sheet called Demo.
I'm going to right click on demo and say View Code.
This is a special code pane for something called Event Macros. We have two dropdowns up here.
We're going to change the left dropdown to worksheet.
And look at that, it automatically went to SelectionChange, which is what I want.
What happens is every time that we change this selection, this little macro will run and it gives us a variable, an object variable called Target.
And so in our case, we want to make sure that our code only runs when you're in one of those two cells.
Put up a little bit of code here that checks to see if the target addresses D7 and displays one message or if the target addresses F18 and displays a different message.
I just want to show you how this works.
So what happens is, as I move around, the spreadsheet is running that macro over and over and over again, but nothing is happening until I get to that cell. Then we get a message that we're in D7.
Click OK.
And then down here, as soon as I hit to that cell, we get a message that we're in F18.
So just so you see that it's a pretty cool little macro that will only basically do something when it hits one of the cells that we've specified in the VBA.
Let's go back to VBA, and I'll paste in some other code.
All right, so when we touch either one of those two cells, D7 or F18, we're going to delete the current validation.
We're going to add a validation called xlvalidateInputOnly.
Here, in the title, we're going to say "We Suggest".
And then the message is going to come from my calculation out in O4. That'll suggest that Chris should order four fish.
If they haven't did F18, we're going to suggest whatever value came out of M16.
Now, before you run this, it's best to close this window with an X, come back to Excel and then what we should see here. So let's change Chris to Andy.
And as soon as I hit that cell, "We suggest you order five of the fish".
Andy, there's the VLOOKUP for Andy.
"We suggest you order five of the fish". So as soon as I touch that cell, they changed the input message.
If I would choose someone else, Diane, and come to that cell, "We suggest you order two of the fish," and it's getting that from the VLOOKUP table, right?
So this is really cool.
What it means is that as soon as I enter that cell, they look at the calculation, they change the input message, and it will change on the fly.
I love this idea.
Down here, where we have the two dice right now, they're coming up to four, so it's going to suggest Hank.
When I enter the cell, Hank is suggested.
If I come out and press F9 to get someone else, there's five and four is nine. That should suggest Mike.
Mike is suggested.
What I'm hoping though is that we have some sort of a situation where we have a repeating calculation.
So here's quota and sales and percent to quota.
And over here, in the bonus, I want to suggest a bonus based on the quota to my left.
All right, so in this case, we're going to run this code every time that's between E2 to E25. Let's go take a look at the code I wrote for this.
This is on a worksheet called Bonus, so I come here to Bonus and View Code.
All right, now, rather than check for each individual cell, I use this cool function called Intersect.
So whatever cell we just selected and the range of E2 to E25, if there's no intersection between them, if it's nothing, then we just exit the sub.
Otherwise, no matter what cell we're in from E2 to E25, we take a look at the selected cell offset of 0 rows and -1 column, so in other words, the cell to the left.
If that value is less than 0, it's going to suggest no bonus.
If that value is less than 10%, a $50 bonus, less than 25%, a $100 bonus, and if they're more than 25%, it's going to suggest the $250 bonus.
Once we have that suggestion message there, then we delete the old validation, add inputOnly, put a title, put the message, and ShowInput = True.
Again, we'll close this with the X and then come back and you'll see, like here, it should say, "No bonus, no bonus, no bonus". That'll be the $250 bonus down here, 20%, a $100 bonus.
Let's see if there's someone, there's someone with a 1%.
When we select that, it suggests the $50 bonus.
What a cool idea to have a tool tip that appears and calculates on the fly as you enter this cell.
I wasn't sure that that macro would be able to run before the tool tip is displayed, but it is working great.
Thanks for sending that question in.
I always say in the videos, please like, subscribe, ring the bell.
And if you have any questions or comments, leave a comment down in the comment below.
When this comment came in two days ago, I was like, "Wow, that would be really cool if we can make it work". Well, I thank you for stopping by.
We'll see you next time for another netcast for MrExcel.
If you like these videos, please, down below, like, subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below.
