As mentioned in my last post, I have added a new feature to our CompTraX tool. Download Version-2 of the CompTraX tool (Click here)—so that you can explore it while reading this post.
Now let’s discuss the enhancement that I have made in this version. One of the limitations in Version-1 of the CompTraX tool was the fact that there was limited space to display text in the ‘Company Overview’, ‘Recent News’, and ‘Key Pipeline’ sections (highlighted in red in picture below).
What this meant is that, if one were to write large paragraphs in these sections, all of that would not fit in. This was obviously not acceptable from a user’s point of view—no one likes missing text. So, to overcome this limitation, I came up with a VBA based solution.
The solution itself was a two-step approach:
Step 1: Calculate, with the help of formulas, if the input text is small enough to fit in the space allocated for it. If yes, display the full text and if not, then curtail the text up to a certain limit (say 500 characters) and append a text that prompts the user to click on the cell/Range containing the article (‘click for more’; what we usually see on web pages)
To do this, I used the LEN, LEFT & IF functions. A generic version of the formula that I have used would be:
IF (LEN(Some_Text)>500,LEFT(Some_Text, 480)&”………click for more”, Some_Text)
[For help on these formulas, visit the Bare Essential Formulas section in Dashboard 101 tutorial]
Step 2: If the user clicks the relevant cell/range (one containing company profiles, news item and pipeline), display a pop up window containing the full text. The pop up window is created using 4 different shapes and some VBA to show/hide them, as needed.
You can find the VBA code that goes behind this enhancement in the ‘Display Sheet’ on the VBA interface of the workbook ( with appropriate comments to make it easy to understand for beginners)
For those of you who are new to VBA, we are soon planning to start a new series called ‘Dashboard 102′ which will cover a lot of basic concepts of VBA.
As always, feel free to contact us at email@example.com for any queries that you may have.
Keep Excelling !