It occurred to me the other day that visibility in spreadsheet design is not always the best. This may be due to several reasons, the fact is key data may not be readily accessible when needed - it could be in hidden rows or columns, not visible on the current screen or perhaps in another sheet or workbook.
This is a possible solution using Input Messages from Data Validation combined with a Selection Change event. An example workbook is attached for your reference here. And below is an image of a "Dynamic Comment" in action. (By "Dynamic Comments", I mean comments that update automatically. Select the cell where they show, and the latest data will be updated instantly)
A bit of explanation on how it is set up. Here is the example code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Cells(1, 1) = False Then Exit Sub
With Target
If .Column = 2 And _
.Row > 2 And _
.Row < ActiveSheet.UsedRange.Row + _
ActiveSheet.UsedRange.Rows.Count Then
.EntireColumn.Validation.Delete
With .Validation
.Add Type:=xlValidateInputOnly
.InputTitle = "" ' Optional
.InputMessage = "Customer: " & Cells(Target.Row, 2) & Chr(10) & _
"Invoice Number: " & Cells(Target.Row, 14) & Chr(10) & _
"Item Ordered: " & Cells(Target.Row, 6) & Chr(10) & _
"Quantity: " & Format(Cells(Target.Row, 12), "#,##0") & Chr(10) & _
"Amount: " & Format(Cells(Target.Row, 16), "$ #,##0.00") & Chr(10) & _
"Total Sales: " & Format(Application.Sum(Range("TotalSales")), "$ #,##0.00")
End With
End If
End With
End Sub
1. Automatic Update Mode vs. Edit Mode
First, you will notice that if Cells(1,1) (Range A1) is False, the subroutine is exited. This added to make it possible to edit the worksheet without the SelectionChange event interfering with Undo. The Forms Toolbar checkbox you can see is linked to Range A1, the font is not visible as I have changed the color to White but you can see whether the code is enabled or not by looking at the Checkbox. When checked, A1 is TRUE (Automatic Update Mode), otherwise it is FALSE (Edit Mode). If you are just referring to data rather than editing (or you feel comfortable without Undo), you might want to just leave it in Automatic Update Mode all of the time.
2. Set the Range
I've limited the range to just once cell in one column (In this case Column B). It might be tempting to add more columns or rows and there is nothing to stop you, I prefer to limit the validation to keep the file size down.
3. The Input Message
This is where it all happens. I've made a string to join data from several cells. The input message in this case is just an example. It probably would not be necessary to show data from cells that are already visible. So you can use the "comments" to refer to where you can't see... as mentioned above, hidden rows or columns, not visible on the current screen or in another sheet or workbook, even outside of Excel (for example, the Registry). Not only that, you can use VBA to make calculations to add to the string and also format it as you like.
A few things to keep in mind,
There is a limit of characters that can be used in the Input Messages (254 by my testing)
I've used Chr(10) to force linebreaks. It doesn't always work as expected, but you can add an extra row of characters at the bottom to help. The number of of characters used in this case should exceed the maximum number of characters used in any other row.
An easy way to get the Column numbers is to go to Tools, Options, General, then check R1C1 reference style to show the numbers in the Column Headers. Uncheck when finished.
Why not use regular comments? I experimented using them - I found this way to work better.
No comments:
Post a Comment