none
Show or not show in msgbox. RRS feed

  • Question

  • In a vba code i have this line

    If IsEmpty(Range("F6")) Or IsEmpty(Range("F8")) Or ActiveSheet.Range("P48").Value = "." Or ActiveSheet.Range("P49").Value = "." Then
     MsgBox "HUSK AT INDSÆTTE KAMPNR" & vbCrLf & "" & vbCrLf & "TJEK OGSÅ HOLDKAPTAJNERNES NAVNE " & vbCrLf & "" & vbCrLf & "HVIS DE IKKE ER DER SÅ DOBBELTKLIK UD FOR LICENSNR PÅ HOLDKAPTAJNERNE", vbOK + vbInformation, "HUSK KAMP NUMMER"
              Exit Sub
         Else: End If

    My Question is to Split it up to do this .

    If all line are active in this Code it shoul show ALL. that's Work fine.

    Now i would like to have that only it show wich line the Function is not met

    Line 1 in msgbox is this (If IsEmpty(Range("F6")) Or IsEmpty(Range("F8")) ) MsgBox "HUSK AT INDSÆTTE KAMPNR"

    Line 2 in msgbox is this (Or ActiveSheet.Range("P48").Value = ".") "TJEK OGSÅ HOLDKAPTAJNERNES NAVNE "

    Line 3 in msgbox is this (Or ActiveSheet.Range("P49").Value = ".") "HVIS DE IKKE ER DER SÅ DOBBELTKLIK UD FOR LICENSNR PÅ HOLDKAPTAJNERNE"

    example 1 : if line 1 are ok and line 2 - 3 are not ok then show line 2 - 3

    example 2 : if line 2 are ok and line 1 - 3 are not ok then  show line 1 - 3

    example 3 : if line 3 are ok and line 1 - 2 are not ok then  show line 1- 2

    example 4 : if line 1 - 2 are ok and line 3 are not ok then show line 3

    and the same if it was line 1 - 3 then show line 2

    and the same if it was line 2 -3 then show line 1

    Hope that was enough info so far

    Your regards from Henrik


    • Edited by Henrik-1 Tuesday, November 11, 2014 9:55 AM
    Tuesday, November 11, 2014 9:44 AM

Answers

  • Hi Henrik,

    It is a pleasure to help.

    See if this code solves your problem:

    Public Sub ValidateCells()
        Dim message As String
        Dim IsConditionNotOK_1 As Boolean
        Dim IsConditionNotOK_2 As Boolean
        Dim IsConditionNotOK_3 As Boolean
        
        'test Conditions
        IsConditionNotOK_1 = Len(Range("F6").Value) > 0 Or Len(Range("F8").Value) > 0
        IsConditionNotOK_2 = Range("P48").Value = "."
        IsConditionNotOK_3 = Range("P49").Value = "."
        
        'if all Conditions are ok, exit sub and do not show any msgbox
        If IsConditionNotOK_1 = False And IsConditionNotOK_2 = False And IsConditionNotOK_3 = False Then Exit Sub
        
        'create message
        If IsConditionNotOK_1 Then message = "HUSK AT INDSÆTTE KAMPNR" & vbCrLf & vbCrLf
        If IsConditionNotOK_2 Then message = message & "TJEK OGSÅ HOLDKAPTAJNERNES NAVNE" & vbCrLf & vbCrLf
        If IsConditionNotOK_3 Then message = message & "HVIS DE IKKE ER DER SÅ DOBBELTKLIK UD FOR LICENSNR PÅ HOLDKAPTAJNERNE" & vbCrLf & vbCrLf
        
        'remove line breaks if there is an error in at least 1 Condition (vbCrLf and vbNewLine are actualy two characters long)
        If IsConditionNotOK_1 Or IsConditionNotOK_2 Or IsConditionNotOK_3 Then
            message = Left(message, Len(message) - 4)
        End If
        
        'show msgbox
        MsgBox message, vbOK + vbInformation, "HUSK KAMP NUMMER"
    End Sub
    

    Hope I understood your request.

    Just one thing, after you've set my thread as the answer, it was still showing as Unanswered. If this code solves your problem, please set it as the answer.

    Best regards,


    Marcelo Nogueira | CEO | Clarian Solutions | www.clarian.com.br | Excel Development & Trainning

    • Marked as answer by Henrik-1 Thursday, November 13, 2014 8:14 PM
    Thursday, November 13, 2014 7:53 PM

All replies

  • example 1 : if line 1 are ok and line 2 - 3 are not ok then show line 2 - 3

    example 2 : if line 2 are ok and line 1 - 3 are not ok then  show line 1 - 3

    example 3 : if line 3 are ok and line 1 - 2 are not ok then  show line 1- 2

    example 4 : if line 1 - 2 are ok and line 3 are not ok then show line 3

    and the same if it was line 1 - 3 then show line 2

    and the same if it was line 2 -3 then show line 1

    Hope that was enough info so far

    Well, I did not understand which Msg you would show on which condition, but I understand what you want to accomplish. :-)

    You can write a lot of IF THEN ELSEIF code, but a simple way it to calculate a binary based value from the boolean result of each expression. Sounds complicated, but it isn't, have a look:

    Sub Test()
      Dim State As Integer
    
      State = _
        IsEmpty(Range("F6")) * 1 + _
        IsEmpty(Range("F8")) * 2 + _
        (Range("P48") = ".") * 4 + _
        (Range("P49") = ".") * 8
        
      Select Case State
        Case -1
          Debug.Print "F6 empty, F8 filled, P48,P49 <> ."
        Case -2
          Debug.Print "F8 empty, F6 filled, P48,P49 <> ."
        Case -3
          Debug.Print "F6 and F8 empty, P48,P49 <> ."
        Case -4
          Debug.Print "F6,F8 filled, P48 = .,P49 <> ."
        Case -5
          Debug.Print "F6 empty, F8 filled, P48 = . ,P49 <> ."
        Case -8
          Debug.Print "F6,F8 filled, P48 <> .,P49 = ."
        Case Else
          Debug.Print State
      End Select
    End Sub

    The boolean FALSE is 0 and TRUE is -1 as Integer. If we consider only the two cells F6 and F8, then there four possibilities:

    F6 and F8 are empty, means -1 * 1 + -1 * 2 = -3
    F6 is filled and F8 is empty, means 0 * 1 + -1 * 2 = -2
    F6 is empty and F8 is filled, means -1 * 1 + 0 * 2 = -1
    F6 and F8 are filled means 0 * 1 + 0 * 2 = 0

    In the code above the State variable can have values from 0 up to 15, depending on the values of the cells. You have to complete the code according to your needs.

    Andreas.


    Tuesday, November 11, 2014 2:22 PM
  • I can see that you would have it to type in the cell if data is present
    the data obtained from other cells.

    I wish it was that a MsgBox should show what information was present
    either 1, 2 or 3 or all.
    if the data in Cell 1 is empty or not ok message "Missing Number"
    if data in cell 2 is empty or not ok message "Missing Text"
    If the data in cell 3 contains "." is it ok displayed text "Missing Name"
    and it all Must be in the same msgbox ......

    Hope this was helpful enough

    Your regards from Henrik-2


    • Edited by Henrik-1 Tuesday, November 11, 2014 3:51 PM
    Tuesday, November 11, 2014 3:38 PM
  • Hi,

    I tried to make one msgbox, unfortunately I have 3 messageboxes with the three messages.

    Have a look, maybe this is what you are looking for.

    Sub testen()

    Sheets("Blad1").Select
    Range("a1").Select

    If test1(antwoord1) = True Then MsgBox "Missing Number"
    If test2(antwoord2) = True Then MsgBox "Missing Text"
    If test3(antwoord3) = True Then MsgBox "Missing Name"

    End Sub

    ----------------------------------------------------

    Function test1(antwoord1) As Boolean

    If Range("a1") = "" Then test1 = True

    End Function

    ----------------------------------------------------

    Function test2(antwoord2) As Boolean

    If Range("a2") = "" Then test2 = True

    End Function

    ----------------------------------------------------

    Function test3(antwoord3) As Boolean

    If Range("a3") = "" Then test3 = True

    End Function

    Reshma

    Tuesday, November 11, 2014 9:05 PM
  • Hi Henrik,

    You just need difine a variable for the message and set the corresponding data. Using Select Case statement is better.

    For example (refer to Andreas’s code):

    Sub Test()
    
    Dim message As String
    
      Dim State As Integer
    
      State = _
    
        IsEmpty(Range("F6")) * 1 + _
    
        IsEmpty(Range("F8")) * 2 + _
    
        (Range("P48") = ".") * 4 + _
    
        (Range("P49") = ".") * 8
    
        
    
      Select Case State
    
        Case -1
    
          message= "F6 empty, F8 filled, P48,P49 <> ."
    
        Case -2
    
          message= "F8 empty, F6 filled, P48,P49 <> ."
    
        Case -3
    
          message= "F6 and F8 empty, P48,P49 <> ."
    
        Case -4
    
          message= "F6,F8 filled, P48 = .,P49 <> ."
    
        Case -5
    
          message= "F6 empty, F8 filled, P48 = . ,P49 <> ."
    
        Case -8
    
          message= "F6,F8 filled, P48 <> .,P49 = ."
    
        Case Else
    
          message= State
    
      End Select
    
    MsgBox message
    
    End Sub 
    

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 12, 2014 8:29 AM
    Moderator
  • I wonder why the MsgBox begin to show negative numbers when my points are met. when a given text which is preselected.

    immediately I can see that with negative numbers so does the macro.
    Yours sincerely Henrik-1
    • Edited by Henrik-1 Wednesday, November 12, 2014 10:36 AM
    Wednesday, November 12, 2014 10:27 AM
  • There my understanding for vba Stop.....

    I know how boolean Works but put it into something like vba No.

    So there i think ill give up on this ...

    Henrik-1

    Wednesday, November 12, 2014 12:49 PM
  • BOING a Spring hit  mee got

    Reshma Mahabir-Poeran version to Work with out ANY BOLEAN system However the Bolean was more like what i wont in same Msgbox

    Henrik-1

    Wednesday, November 12, 2014 12:55 PM
  • Hi,

    Can you be more clear? I don't know what you mean with above question.

    Reshma

    Wednesday, November 12, 2014 6:43 PM
  • hm there was a member in this Group WHO hat made a function in Bolean system

    When ive run that Macro the msgbox only show the numbers.

    but the macro you made reshma Works ok.

    But ive need it in the same msgbox not 3 diferents msgbox...

    What they REALY mean with the Settings in the bolean system IVE can't figure out at this moment.

    Henrik-1

    Wednesday, November 12, 2014 10:39 PM
  • Hello Henrik-1,

    Please check if the code below solves your problem:

    Public Sub ValidateCells()
        Dim message As String
        Dim IsLineOK_1 As Boolean
        Dim IsLineOK_2 As Boolean
        Dim IsLineOK_3 As Boolean
        
        'test lines
        IsLineOK_1 = Len(Range("F6").Value) = 0 Or Len(Range("F8").Value) = 0
        IsLineOK_2 = Range("P48").Value = "."
        IsLineOK_3 = Range("P49").Value = "."
        
        'if all lines are ok, exit sub and do not show any msgbox
        If IsLineOK_1 And IsLineOK_2 And IsLineOK_3 Then Exit Sub
        
        'create message
        If Not IsLineOK_1 Then message = "HUSK AT INDSÆTTE KAMPNR" & vbCrLf & vbCrLf
        If Not IsLineOK_2 Then message = message & "TJEK OGSÅ HOLDKAPTAJNERNES NAVNE" & vbCrLf & vbCrLf
        If Not IsLineOK_3 Then message = message & "HVIS DE IKKE ER DER SÅ DOBBELTKLIK UD FOR LICENSNR PÅ HOLDKAPTAJNERNE" & vbCrLf & vbCrLf
        
        'remove line breaks if there is an error in at least 1 line (vbCrLf and vbNewLine are actualy two characters long)
        If IsLineOK_1 = False Or IsLineOK_2 = False Or IsLineOK_3 = False Then
            message = Left(message, Len(message) - 4)
        End If
        
        'show msgbox
        MsgBox message, vbOK + vbInformation, "HUSK KAMP NUMMER"
    End Sub

    Hope I understood correctly what you need.

    Best Regards,

    Marcelo Nogueira

    CEO - Clarian Solutions

    www.clarian.com.br

    Excel Development & Trainning

    Wednesday, November 12, 2014 10:55 PM
  • AMAZING THAT was exactly what i want Thank you so much

    And Thanks Too all WHO hat giving any kind of solutions to this job

    THANKS AGAIN
    Your Regards From Henrik-1 Denmark

    Thursday, November 13, 2014 9:21 AM
  • Great news! Very happy to help! I would really appreciate if you could vote and set my thread as the answer to your question. It would help me and others to see that your question was answered. Thanks in advance! Best regards,

    Marcelo Nogueira | CEO | Clarian Solutions | www.clarian.com.br | Excel Development & Trainning

    • Marked as answer by Henrik-1 Thursday, November 13, 2014 9:49 AM
    • Unmarked as answer by Henrik-1 Thursday, November 13, 2014 9:49 AM
    Thursday, November 13, 2014 9:34 AM
  • Ups a Little to fast with Clapping my hands with this macro..

    a Little minor problem

        'test lines
        IsLineOK_1
    = Len(Range("F6").Value) = 0 Or Len(Range("F8").Value) = 0
        IsLineOK_2
    = Range("P48").Value = "."
        IsLineOK_3
    = Range("P49").Value = "."
        
    In IslineOK_2 i want it to Show Message When this sign was in cell P48 "." and the same in line

    In IsLineOK_3 i want it show Message when This Sign was in cell P49 "."

    as it is now when there came this sign in those 2 cells it shows No Message but if there came numbers in cell P48 and P49 it show message it should be Reverse

    Hope it was enough Message around my problem

    sorry to interrupt you Again with this

    Your Regards from Henrik-1 Denmark

    Thursday, November 13, 2014 7:14 PM
  • Hi Henrik,

    It is a pleasure to help.

    See if this code solves your problem:

    Public Sub ValidateCells()
        Dim message As String
        Dim IsConditionNotOK_1 As Boolean
        Dim IsConditionNotOK_2 As Boolean
        Dim IsConditionNotOK_3 As Boolean
        
        'test Conditions
        IsConditionNotOK_1 = Len(Range("F6").Value) > 0 Or Len(Range("F8").Value) > 0
        IsConditionNotOK_2 = Range("P48").Value = "."
        IsConditionNotOK_3 = Range("P49").Value = "."
        
        'if all Conditions are ok, exit sub and do not show any msgbox
        If IsConditionNotOK_1 = False And IsConditionNotOK_2 = False And IsConditionNotOK_3 = False Then Exit Sub
        
        'create message
        If IsConditionNotOK_1 Then message = "HUSK AT INDSÆTTE KAMPNR" & vbCrLf & vbCrLf
        If IsConditionNotOK_2 Then message = message & "TJEK OGSÅ HOLDKAPTAJNERNES NAVNE" & vbCrLf & vbCrLf
        If IsConditionNotOK_3 Then message = message & "HVIS DE IKKE ER DER SÅ DOBBELTKLIK UD FOR LICENSNR PÅ HOLDKAPTAJNERNE" & vbCrLf & vbCrLf
        
        'remove line breaks if there is an error in at least 1 Condition (vbCrLf and vbNewLine are actualy two characters long)
        If IsConditionNotOK_1 Or IsConditionNotOK_2 Or IsConditionNotOK_3 Then
            message = Left(message, Len(message) - 4)
        End If
        
        'show msgbox
        MsgBox message, vbOK + vbInformation, "HUSK KAMP NUMMER"
    End Sub
    

    Hope I understood your request.

    Just one thing, after you've set my thread as the answer, it was still showing as Unanswered. If this code solves your problem, please set it as the answer.

    Best regards,


    Marcelo Nogueira | CEO | Clarian Solutions | www.clarian.com.br | Excel Development & Trainning

    • Marked as answer by Henrik-1 Thursday, November 13, 2014 8:14 PM
    Thursday, November 13, 2014 7:53 PM
  • I'm not sure if this one helps ... maybe in the future.

    Andreas.

    Sub Test()
      Const Title = "HUSK KAMP NUMMER"
      Dim Messages, Msg As String
      Dim State As Integer, i As Integer
      
      Messages = Array( _
        "HUSK AT INDSÆTTE KAMPNR", _
        "TJEK OGSÅ HOLDKAPTAJNERNES NAVNE", _
        "HVIS DE IKKE ER DER SÅ DOBBELTKLIK UD FOR LICENSNR PÅ HOLDKAPTAJNERNE")
    
      State = Abs( _
        IsEmpty(Range("F6")) Or IsEmpty(Range("F8")) + _
        (Range("P48") = ".") * 2 + _
        (Range("P49") = ".") * 4)
        
      If State <> 0 Then
        For i = 0 To 2
          If (State And 2 ^ i) <> 0 Then
            Msg = Msg & Messages(i) & vbCrLf & vbCrLf
          End If
        Next
        MsgBox Left(Msg, Len(Msg) - 4), vbInformation, Title
      End If
    End Sub


    Thursday, November 13, 2014 8:19 PM
  • Now i can Clap my hands .. thanks to Marcelo de Moraes Nogueira

    It Works it Works 1 million Thanks for you Help Again

    Youre Regards From Henrik-1 Denmark

    The Danish Dart Union THanks for a great help

    And thanks to you Andreas Killer for trying to give a hint to how to do it (i hat stop used the version with the Bolean)



    • Edited by Henrik-1 Thursday, November 13, 2014 8:46 PM
    Thursday, November 13, 2014 8:24 PM
  • Hey

    Marcelo de Moraes Nogueira
         
    25           
    Points
    Top 30
    Marcelo de Moraes Nogueira        
             Joined  May 2011        
               

    3

                             

    You have help me alot with that Msgbos Macro..

    i have now tryet to when all 3 function are Good then msg don't show up that's ok

    but i need another function into it when all 3 are good go to another Macro

    i call "TurnIntoPDFFile1"

    Your code below

    Public Sub ValidateCells()
       
    Dim message As String
       
    Dim IsConditionNotOK_1 As Boolean
       
    Dim IsConditionNotOK_2 As Boolean
       
    Dim IsConditionNotOK_3 As Boolean
       
       
    'test Conditions
        IsConditionNotOK_1
    = Len(Range("F6").Value) > 0 Or Len(Range("F8").Value) > 0
        IsConditionNotOK_2
    = Range("P48").Value = "."
        IsConditionNotOK_3
    = Range("P49").Value = "."
       
       
    'if all Conditions are ok, exit sub and do not show any msgbox
       
    If IsConditionNotOK_1 = False And IsConditionNotOK_2 = False And IsConditionNotOK_3 = False Then Exit Sub
       
       
    'create message
       
    If IsConditionNotOK_1 Then message = "HUSK AT INDSÆTTE KAMPNR" & vbCrLf & vbCrLf
       
    If IsConditionNotOK_2 Then message = message & "TJEK OGSÅ HOLDKAPTAJNERNES NAVNE" & vbCrLf & vbCrLf
       
    If IsConditionNotOK_3 Then message = message & "HVIS DE IKKE ER DER SÅ DOBBELTKLIK UD FOR LICENSNR PÅ HOLDKAPTAJNERNE" & vbCrLf & vbCrLf
       
       
    'remove line breaks if there is an error in at least 1 Condition (vbCrLf and vbNewLine are actualy two characters long)
       
    If IsConditionNotOK_1 Or IsConditionNotOK_2 Or IsConditionNotOK_3 Then
            message
    = Left(message, Len(message) - 4)
       
    End If
       
       
    'show msgbox
        MsgBox message
    , vbOK + vbInformation, "HUSK KAMP NUMMER"

    'somehow i know my code should be here but cant figure it out

    end Sub

    Thanks again

    Tuesday, November 18, 2014 10:11 PM