Display Message When Condition Met But Without Selecting a Cell

MartinS

Active Member
Joined
Jun 17, 2003
Messages
479
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
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:
Capture.PNG

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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Isthis what you want?? Put this code in the worksheet code :
VBA 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
 
Upvote 0
Isthis what you want?? Put this code in the worksheet code :
VBA 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
 
Upvote 0
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
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top