+ Reply to Thread
Results 1 to 8 of 8

Msgbox with message based on multiple variables / if statements?

  1. #1
    Registered User
    Join Date
    07-15-2008
    Location
    Ann Arbor
    Posts
    12

    Msgbox with message based on multiple variables / if statements?

    I have a worksheet where I need to validate the header content. If the headers don't match what is expected, then I need the macro to exit and show a message box with what headers were incorrect. For example:

    If Range("A1").Value <> "Material" Then
    a = 1
    End If

    If Range("B1").Value <> "Subcomponent" Then
    b = 1
    End If

    If Range("C1").Value <> "Quantity" Then
    c = 1
    End If

    etc.

    If B1 and C1 are incorrect, I want a message box to appear before the macro exits that reads:

    "Header for column B should be 'Subcomponent'

    Header for column C should b 'Quantity'"

    Is there any way to get a single message box to display text based on the values assigned to a, b, c, etc?

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Msgbox with message based on multiple variables / if statements?

    Try this:
    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    07-15-2008
    Location
    Ann Arbor
    Posts
    12

    Re: Msgbox with message based on multiple variables / if statements?

    What I mean is for the message box contents to be dynamic, depending on which header checks didn't validate. I'll have over a dozen checks in place and want the user to know which failed. Something like:

    Msgbox
    if a = 1 then "Header for column A should be 'Material'" & vbNewLine
    if b = 1 then "Header for column B should be 'Subcomponent'" & vbNewLine
    if c = 1 then "Header for column C should be 'Quantity'" & vbnewLine

    I could have each message appear on its own box (if a = 1 then Msgbox "Header for column A should be 'Material'", if b = 1 then Msgbox "Header for column B should be 'Subcomponent'", etc.), but I'd like for them all to appear in one message. Is that possible?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Msgbox with message based on multiple variables / if statements?

    Yes, however, depending on how many combinations you have, is how many lines of code you will have to write. If you have a dozen checks then I think that you have a combination of approximately 4000 variations. This may not be the most efficient way. Someone else may have a better solution for you.

  5. #5
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Msgbox with message based on multiple variables / if statements?

    Hi,

    This is simple enough but how many headers do you actually have. Could you give us a list so we don't have to keep recoding it to accommodate for new headers.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  6. #6
    Registered User
    Join Date
    10-04-2012
    Location
    US
    MS-Off Ver
    Word, Excel, Access all 2007 & 2010. Outlook 2010
    Posts
    56

    Re: Msgbox with message based on multiple variables / if statements?

    I think you may be able to apply this to your header checking okay. You had the right idea but you just needed the logic of manipulating the string rather than worring about how many strings would need to be combined. Try this.

    Please Login or Register  to view this content.
    Cheers!
    Last edited by kcarley; 01-30-2013 at 12:05 AM.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Msgbox with message based on multiple variables / if statements?

    Please Login or Register  to view this content.
    Ben Van Johnson

  8. #8
    Registered User
    Join Date
    10-04-2012
    Location
    US
    MS-Off Ver
    Word, Excel, Access all 2007 & 2010. Outlook 2010
    Posts
    56

    Cool Re: Msgbox with message based on multiple variables / if statements?

    #adamsad1

    Do you have any questions on how to use these solutions?

    I like the idea #protonLeah "Ben" has given too. It avoids the need to hard-code the expected results. I would suggest changing the following line:
    Please Login or Register  to view this content.
    To BE:
    Please Login or Register  to view this content.
    Or this doesn't work out of the box. It is a great idea though.

    Enjoy!
    K

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1