Bowls Hub...It's all about the Bowls
AUTOMATICALLY COMPLETED WITH MATCH DETAILS (REQUIRES EASY FIXTURES)
COMPLETE WITH EASY TO MAKE GUILLOTINE INSTRUCTIONS (1 Wood board, 2 screws, 1 cutting guide - done in minutes)
I printed, cut & stapled nearly 800 sheets of A4 in one day... A full years fixture books for less the £8 of paper.
I came upon this problem because the comment indicators clashed in colour and placement with text and objects I had on my excel worksheet.
Looking online for a solution I discovered an array of complex workarounds. None of them were appealing and in fact, all of them were bodges because quite simply, you can’t manipulate that annoying triangle.
Actually though, if you just switch your mind from Excel mode to programming mode, the solution is easy.
With some very simple code you can create your own pop up dialogs. You have full control of what triggers the popup, what the trigger looks like, where the trigger is and also full control of where the popup should appear.
The example 'Right' shows a range of labels. Using the MouseMove event, hovering the mouse over any of these labels changes the label’s backcolour and displays the popup help comment. In this case the popup appears centralised beneath the help labels but it could appear beside the label or anywhere else you like.
The code for this is simple and so versatile it can be manipulated to almost any degree. You can for example limit the trigger point to any portion of the displayed label.
MOUSE MOVE EVENT
You’ll need this code in the ‘MouseMove’ event of every label that’s intended to trigger the comment.
Private Sub CmtLbl01_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As_
Single, ByVal Y As Single)
HlpAdj = 10
If Y > HlpAdj And Y < IndHlp01.Height - HlpAdj And x > HlpAdj And x < IndHlp01.Width - HlpAdj Then
If Not IndHlpDispLbl.Visible Then
CmtLbl01.BackStyle = fmBackStyleOpaque
CmtLbl01.BackStyle = fmBackStyleTransparent
IndHlpDispLbl.Visible = False
EXPLANATION: The mouse move event recognised that the mouse has moved over the label and so runs the code.
The important thing to note in this code is that X & Y from the mouse move event, refer to the label not the screen. So zero X is the very left of the label and zero Y is the very top of the label.
The variable HelpAdj adjusts the trigger point toward the centre of the label. This is essential otherwise the popup would not disappear as the mouse moved off the label.
Beyond that it’s just a question of making the popup visible or not and if you want, changing the back colour to highlight which help comment is being displayed.
ShowHlpTopic is a further subroutine that displays the comment in question. You’ll notice that I have passed to this routine the value 1 which is the number of the comment to be displayed. Below, I’ve used a simple Select Case statement to display the popup.
NB. The code below includes two commented out lines. You could use these or something like them to place the popup alongside the trigger. Remember you’d need to adjust the code so that you are sending and receiving the name of the triggering label.
Select Case HelpNum
'IndHlpDispLbl.Top = PassedControl.top+PassedControl.height
'IndHlpDispLbl.Left = PassedControl.Left+PassedControl.width
HelpCapt = "Any Comment" & vbCrLf & vbCrLf
HelpCapt = HelpCapt & "Comment Continued" & vbCrLf
IndHlpDispLbl.Caption = HelpCapt
IndHlpDispLbl.Visible = True
Planned next topic - Formula to return multiple items from a Range without the need for long and awkward array functions.