info box pop

mamun.ges · Feb 15th 2022

Hi, Hope All are fine.

I am having a problem creating a popup message box with conditions.

The fact is if the Target Cell range (AI9:AI1200) is 0 then it shows a specific message

if the Target Cell range (AI9:AI1200) is 1 then it shows another specific message.

If error then shows another message.

I am using the below code, please help me to change this code with the above condition.

Code

Sub Date_Info()
On Error GoTo ErrorHandle
Dim x, w
Dim y, z As Integer

Dim MyNum, MyNum1, MyNum2 As String
 MyNum = Range("P3").Value
 MyNum1 = Range("Q3").Value
 MyNum2 = Range("R3").Value


Set x = CreateObject("WScript.Shell")
y = x.Popup("           Bill Amount:= " & MyNum _
 & vbNewLine & "To Audit              : " & MyNum1 _
 & vbNewLine & "Note Approved Date : " & MyNum2, 6, MyNum)

Set x = Nothing

 Exit Sub
ErrorHandle:
'MsgBox " No Data Found ", 8, "Info?"
Set w = CreateObject("WScript.Shell")
z = w.Popup("Sorry, No Data Found.", 3, "Date Info?")

End Sub

Display More

I anything is not understandable then please tell me I will try to explain better.

A sample file will be enough for me to understand better.

Any help will be appreciatable.

Thanks and best regards.

Justin Doward · Feb 17th 2022

Hi MA,

The code you have provided does not really do anything like what you are requesting, is there a reason you are using a combination of system popup and msgbox rather than just using msgbox?

Do you want a message to be displayed when a cell changes or do you want a macro to run through the ranges and popup/msgbox on all values?

mamun.ges · Feb 17th 2022

Thank you Justin.

I solved the problem.

Thanks Again.

**royUK** · Feb 18th 2022

It's good that you worked out what it was. To close this thread, please either add your answer and accept it as an answer: this could help others (recommended) or remove the post (not recommended)

mamun.ges · Feb 18th 2022

Thanks, Royuk.

I like your recommendation and will follow it in the future too.

I solve the problem by the below code. Hope it helps others.

Code

Sub Date_Info()
On Error GoTo ErrorHandle
Dim x, w
Dim y, z As Integer
Dim MyNumber As Integer
MyNumber = Range("AI3")
Dim MyNum, MyNum1, MyNum2, MyNum3 As String
 
 MyNum = Range("P3").Value
 MyNum1 = Range("Q3").Value
 MyNum2 = Range("R3").Value
 MyNum3 = Range("S3").Value
 
 If MyNumber = 0 Then

Set x = CreateObject("WScript.Shell")
y = x.Popup("           Name:= " & MyNum _
 & vbNewLine & "    --------------------------------------------" _
 & vbNewLine & "Entry Date          : " & MyNum1 _
 & vbNewLine & "Exit Date : " & MyNum2, 10, MyNum3)

Set x = Nothing

ElseIf MyNumber = 1 Or 2 Then

Set x = CreateObject("WScript.Shell")
y = x.Popup("           Name:= " & MyNum _
 & vbNewLine & "    --------------------------------------------" _
 & vbNewLine & "Entry on Building Date    : " & MyNum8 _
 & vbNewLine & "Exit of Building Date    : " & MyNum2, 10, MyNum3)
 
 End If

 Exit Sub
ErrorHandle:
Set w = CreateObject("WScript.Shell")
z = w.Popup("Sorry, No Date Found.", 3, "Date Info?")

End Sub

Display More

Thanks again for the support.

**royUK** · Feb 18th 2022

I still do not understand why you do not use Excel's Messagebox feature.

mamun.ges · Feb 18th 2022

Hi, Royuk,

Can you give me any idea how to do it with the excel message box feature.

How to write the same code with the excel message box.

Thanks again.

**royUK** · Feb 19th 2022

I've used a Select Case but you can use the IF statement.

I don't know why you declare MyNum1, etc as Strings, I would expect them to Integers.

Code

Dim MyNum as String, MyNum1 as String, MyNum2 as String, MyNum3 As String

The above is not the correct way to declare the variables, they will not all be Strings only the last one. See my code for the correct way.

Code

Sub Date_Info()
 
    Dim MyNumber As Integer
    MyNumber = Range("AI3")
    Dim MyNum As String, MyNum1 As String, MyNum2 As String, MyNum3 As String
    
    MyNum = Range("P3").Value
    MyNum1 = Range("Q3").Value
    MyNum2 = Range("R3").Value
    MyNum3 = Range("S3").Value
    
    Debug.Print MyNumber
    Select Case MyNumber
        Case 0
            MsgBox "Name:= " & MyNum _
            & vbNewLine & " --------------------------------------------" _
            & vbNewLine & "Entry Date : " & MyNum1 _
            & vbNewLine & "Exit Date : " & MyNum2 & ", 10, " & MyNum3
            
        Case 1, 2
            MsgBox " Name:= " & MyNum _
            & vbNewLine & " --------------------------------------------" _
            & vbNewLine & "Entry on Building Date : " & MyNum _
            & vbNewLine & "Exit of Building Date : " & " MyNum2 & ", 10, " & MyNum3"
            
            Case Else: GoTo ErrorHandle
            
    End Select
    
    Exit Sub
ErrorHandle:
    MsgBox "Sorry, No Date Found.", vbInformation, "Date Info"
End Sub

Display More

mamun.ges · Feb 19th 2022

Hi, RoyUK,

Thank You for the support & always appreciate your good work.

**royUK** · Feb 24th 2022

Pleased to help.

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

mamun.ges · Feb 24th 2022

Thanks royUK.

Always the way this forum helps us.

In my code message box auto shut after 10 seconds but In your code It needs to close manually.

Though it does not cause me a lot, if it is possible that the message box will close after 10 seconds then It will be fantastic work.

Thanks again and best regards.

info box pop

Participate now!

Share

Tags