dislay messgage

[Trevor3007]

hi,

Can some canny person provide me with the applicable VBA based on the following criteria?:-

when the date in cell B2 is the same as the date in D2 a popup message stating ' end of period, new date required in next input'

would also prefer the text to show in bold red & font 16.

Many thanks in advance for your time & assistance with my issue.

KR
Trevor3007

 

[Yongle]

The basic message box cannot be made to look the way you want
- create a simple userform to match your desired "look"
- trigger the userform to show when values in the 2 cells match
- close the userform with an OK button

What should trigger the message?
- do both values change?
- does one value change once per period and the other every day?
- how are the values changed? (manually?, automatically?, by formula?)
- which value is which?
- which action or chage should trigger the userform to show? opening the workbook? when one of the values changes? (which one?) something else?

Create a simple userform and make it look the way you want. It requires
- a simple caption
- a label to hold the message
- a command button for the user to unload the userform

 

[My Aswer Is This]

I have a script here that would do what you want having a Popup message like you wanted.
My script would popup a Text Box with your message.

But I would also need to know the same questioned asked in post 2.

This script will run as a module script put in a button if you want to test it.
But to have it popup automatically would require those questions answered.

Sub My_Message()
    'Modified  9/9/2018  1:29:11 PM  EDT
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 435, 90.75, 177.75, 102).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 0)
    Selection.Left = ActiveCell.Left
    Selection.Top = ActiveCell.Top
    ShapeName = Selection.Name
    
   
    With Selection.ShapeRange.TextFrame2
        .TextRange.Font.Size = 16
         .TextRange.ParagraphFormat.Alignment = msoAlignCenter
        .TextRange.Characters.Text = "End of period" & Chr(13) & "New date required in next input"
        .TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
        .TextRange.Font.Bold = True
        .AutoSize = msoAutoSizeShapeToFitText
    End With
End Sub

 

[Trevor3007]

The basic message box cannot be made to look the way you want
- create a simple userform to match your desired "look"
- trigger the userform to show when values in the 2 cells match
- close the userform with an OK button

What should trigger the message?
- do both values change?
- does one value change once per period and the other every day?
- how are the values changed? (manually?, automatically?, by formula?)
- which value is which?
- which action or chage should trigger the userform to show? opening the workbook? when one of the values changes? (which one?) something else?

Create a simple userform and make it look the way you want. It requires
- a simple caption
- a label to hold the message
- a command button for the user to unload the userform

Hi & thanks ,

hope my answers will solve

What should trigger the message?​

a date inputted manually in B2 if it matched the date already in e2 would trigger ( presently there is a =if in e2 to trigger this)

- do both values change
No... its just to advise.

- does one value change once per period and the other every day?​

yes... the date in b2 changes weekly, but E2 date is triggered via other dates within the worksheet

- how are the values changed? (manually?, automatically?, by formula?)​

B2 = manually , E2 auto(via formula)

- which value is which?
- which action or change should trigger the userform to show? opening the workbook? when one of the values changes? (which one?) something else?​

when e2 = b2

many thanks in advance
KR
Trevor3007

​

 

[Trevor3007]

I have a script here that would do what you want having a Popup message like you wanted.
My script would popup a Text Box with your message.

But I would also need to know the same questioned asked in post 2.

This script will run as a module script put in a button if you want to test it.
But to have it popup automatically would require those questions answered.

Sub My_Message()
    'Modified  9/9/2018  1:29:11 PM  EDT
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 435, 90.75, 177.75, 102).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 0)
    Selection.Left = ActiveCell.Left
    Selection.Top = ActiveCell.Top
    ShapeName = Selection.Name
    
   
    With Selection.ShapeRange.TextFrame2
        .TextRange.Font.Size = 16
         .TextRange.ParagraphFormat.Alignment = msoAlignCenter
        .TextRange.Characters.Text = "End of period" & Chr(13) & "New date required in next input"
        .TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
        .TextRange.Font.Bold = True
        .AutoSize = msoAutoSizeShapeToFitText
    End With
End Sub

many thanks & hope below will answer your question:-

What should trigger the message?
a date inputted manually in B2 if it matched the date already in e2 would trigger ( presently there is a =if in e2 to trigger this)

- do both values change
No... its just to advise.

- does one value change once per period and the other every day?
yes... the date in b2 changes weekly, but E2 date is triggered via other dates within the worksheet

- how are the values changed? (manually?, automatically?, by formula?)
B2 = manually , E2 auto(via formula)
- which value is which?
- which action or change should trigger the userform to show? opening the workbook? when one of the values changes? (which one?) something else?

when e2 = b2

KR
Trevor3007​

 

[My Aswer Is This]

If you will be manually changing the value in Range("E2") then try this.
Script will run if B2 and E2 are the same.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E2")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = [B2].Value Then
'Modified  9/9/2018  5:05 PM  EDT
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 435, 90.75, 177.75, 102).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 0)
    Selection.Left = ActiveCell.Left
    Selection.Top = ActiveCell.Top
    ShapeName = Selection.Name
    
   
    With Selection.ShapeRange.TextFrame2
        .TextRange.Font.Size = 16
         .TextRange.ParagraphFormat.Alignment = msoAlignCenter
        .TextRange.Characters.Text = "End of period" & Chr(13) & "New date required in next input"
        .TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
        .TextRange.Font.Bold = True
        .AutoSize = msoAutoSizeShapeToFitText
    End With
End If
End If
End Sub

 

[Yongle]

@My Aswer Is This
how does user close the textbox ?
(A message box would have a button to click on)

 

[My Aswer Is This]

If the textbox is still selected then press delete key.
If textbox is not still selected then select textbox and press delete key.

 

[Yongle]

@Trevor3007
my event macro would be tiggered exactly like the solution by @My Aswer Is This
But instead of the code creating the textbox insert

UserForm1.Show