0

I am busy with an optimization spreadsheet which needs to display an information message, maybe similar to the Input Message for data validation (but I am open to suggestions). The problem that I would like to solve is eliminate user error. Normally the user will select an item from a drop down list (using data validation) and the user needs to refer to text books as to what value should be used for this item in the remainder of the calculations - this creates inconsistency and sometime user error.

My limitation is that I can not display this message in additional rows/columns cells as this spreadsheet is a company standard spreadsheet and many other sheets reference these specific cells.

This message will be used to display typical minimum, maximum and a recommended user defined value based on another cell value. The user may then choose a value from this informative message and use that in the calculations going forward, but if the user determines that the value chosen is no longer appropriate, then the user needs to go back and see the same min, max and avg. values as a guideline to choose from.

Below is an over-simplified version of what I want to achieve. The text that needs to be displayed in this informative message will be part a data table.

enter image description here

I tried to use custom text formats based on the cell value, but I could not manage to get this to work correctly - but I also suspect that this is not the correct tool. I also tried to change the data validation input message (similar to how the data validation can change based on a cell value) but I could not get this to work. I tried to crease additional columns to display the min, max and avg value in but this caused problem down the line.

I also think that a pop-up box may be a little frustrating as the user would have to click/close frequently - but this could be an option too. Or perhaps a VBA text box displaying text depending on the cell selected; the latter could be a good approach but I have no idea on how to start with this.

I guess a macro would have to continuously scan which cell is selected and the run a lookup function to display the text in associated with the item selected. https://stackoverflow.com/questions/71793403/adding-a-comment-in-a-cell-based-on-another-cells-value this is probably the nearest solution I could find, but I am still a little clueless.

IzakG
  • 13
  • 1
  • 5
  • like this ? when A1 in sheet1 is selected show a comment using the value of b1 in sheet2-- `Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" Then If Target.Comment Is Nothing Then Worksheets("Sheet1").Range("A1").AddComment Worksheets("Sheet1").Range("A1").Comment.Text Text:="Value of B1 is " & Worksheets("Sheet2").Range("B1").Value End If End If End Sub ` – k1dr0ck Feb 25 '23 at 04:27
  • Hi, this worked, thank you! I added some additional lines of code - but I am not done yet. I thought that this code would make the spreadsheet sluggish, so far I have not noticed any delay in anything. – IzakG Feb 27 '23 at 15:24

1 Answers1

0

This is a work in progress, but meets the needs of what I wanted. Thank you for the help k1dr0ck!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "F64:L73"
Dim InfoText As String
Dim TypeLookup As String
Dim LookupTable As Range

Dim TXTHELP As String

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target

            TypeLookup = Cells(ActiveCell.Row, 2).Value
            DNLookup = Cells(45, ActiveCell.Column)
               
            With Worksheets("Data_MinorLoss")
                Set LookupTable = .Range("A3:O8")
                
            End With
            
            InfoText = WorksheetFunction.VLookup(TypeLookup, LookupTable, 2, False)
            
                       
            'MsgBox InfoText
            
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub
IzakG
  • 13
  • 1
  • 5