Display Message When Condition Met But Without Selecting a Cell

[MartinS]

I have a column in a range (call that A) that has a validation applied (shows a list of items), column A also has conditional formatting based on another column (call that B) and there is a column which displays text when column B is TRUE, i.e.

IF(Z44,"See comment in table year header for further details","")

What I've been asked to do is to, when one or more items in column B are TRUE, pop-up a message in the 'Table year' header:

It's clearly too small to show the amount of text required:
"As a rule of thumb, for SAPS S2 tables onwards:

• For effective dates in the first half of the year - use the current year as the YoU
• For effective dates in the second half of the year - use the following year as the YoU"
Is there a way that a message can pop-up based on a formula, i.e. COUNTIF(Z44:Z51,TRUE)>0?
Thanks
Martin

 

[offthelip]

Isthis what you want?? Put this code in the worksheet code :

Private Sub Worksheet_Calculate()
inarr = Range("Z44:Z51")
 For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) Then
   msgtxt = "As a rule of thumb, for SAPS S2 tables onwards:" _
   & "• For effective dates in the first half of the year - use the current year as the YoU" _
  & "• For effective dates in the second half of the year - use the following year as the YoU"
   MsgBox (msgtxt)
   Exit For
  End If
 Next i
End Sub

 

[MartinS]

Isthis what you want?? Put this code in the worksheet code :

Private Sub Worksheet_Calculate()
inarr = Range("Z44:Z51")
 For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) Then
   msgtxt = "As a rule of thumb, for SAPS S2 tables onwards:" _
   & "• For effective dates in the first half of the year - use the current year as the YoU" _
  & "• For effective dates in the second half of the year - use the following year as the YoU"
   MsgBox (msgtxt)
   Exit For
  End If
 Next i
End Sub

Thanks, that's great and that's how I could have done it but my only concern is that, using VBA, the message will keep popping up if the result remains true. There is no other non-VBA way that I'm missing? Data Validation requires the user to select a cell, and there isn't any room to the right to show that amount of text.
Appreciate your response, I'll have to have a chat and discuss our options.
Thanks again
Martin

 

[offthelip]

You could change the vba so that the message only pops up when z41 to z51 changes by storing the current values in a public array and checking for changes

 

[MartinS]

You could change the vba so that the message only pops up when z41 to z51 changes by storing the current values in a public array and checking for changes

Thanks, but I went with a function in the end which works a treat.

Function TableYearMessage() As String
'Declare procedure level variables
Dim i               As Integer
Dim intCounter      As Integer
Dim rn              As Name
Dim rngDATableAll   As Range
Dim strReturnString As String
Dim strSheetName    As String
    'Ensure the function calculates on any change
    Application.Volatile
    'Get the sheet the function was called from
    strSheetName = Application.Caller.Worksheet.Name
    'Loop through the sheet level names
    For Each rn In Sheets(strSheetName).Names
        'Where specific name
        If rn.Name Like "*DATable*_All" Then
            'Get the associated range
            Set rngDATableAll = rn.RefersToRange
            'Count the number of cells that fail validation
            Select Case rngDATableAll.Rows.Count
                Case 47
                    For i = 34 To 41
                        If rngDATableAll.Cells(i, 20).Value Then
                            intCounter = intCounter + 1
                        End If
                    Next i
                Case 49
                    For i = 36 To 43
                        If rngDATableAll.Cells(i, 20).Value Then
                            intCounter = intCounter + 1
                        End If
                    Next i
            End Select
            'If one or more cells failed validation
            If intCounter > 0 Then
                'Define return message if one or more cells fail validation
                strReturnString = "As a rule of thumb, for SAPS S2 tables onwards:"
                strReturnString = strReturnString & vbLf & vbLf
                strReturnString = strReturnString & "• For effective dates in the first half of the year - use the current year as the YoU"
                strReturnString = strReturnString & vbLf
                strReturnString = strReturnString & "• For effective dates in the second half of the year - use the following year as the YoU"
                'Exit the loop as at least one range failed validation
                Exit For
            End If
        End If
    Next rn
'Return string to the function
TableYearMessage = strReturnString
End Function