MsgBox with user-inputted variable

Question

I am writing a script in Excel VBA that runs a search in a country database considering user-inputted criteria. The search is ran out of a UserForm that considers the user search from three search fields. Apart form the "Country", the user can also narrow the search by mentioning the "Category of Information" and the "Subcategory of Information" it is interested in. All of these fields are ComboBox that are linked to lists. Some examples of Categories and subcategories are "Geography", "Economic Indicators", "Media", "Population Statistics", among others. Depending on the criteria provided by the user, the script will either return the search results - if there are any matches with the database - or, alternatively, a MsgBox advising that the search found no matches. I was wondering if the text presented in the MsgBox is fixed or if it can be dependent on the user-inputted variables.

To clarify let's take, as an example, a user that is looking for information about the US and runs a search with only this criteria filled. The database has information on the US and returns all information available. Despite all the data, the user specifically wants information concerning media in the US and repeats the search with these two criteria. However, the database has no information specifically on the US and Media. In this case, the script returns a MsgBox that, as per my code at the moment - which is working fine -, just says that "The database has no information that matches this search".

My question is: can the MsgBox return a message that is dependent on the user search, i.e., in the example, return something like "There is no information regarding Media in the US"? Thank you very much for your help.

This is the code that runs the search:

country = Sheets("Results").Range("D5").Value
Category = Sheets("Results").Range("D6").Value
Subcategory = Sheets("Results").Range("D7").Value
finalrow = Sheets("Database").Range("A200000").End(xlUp).Row

    For i = 2 To finalrow

        'If the country field is left empty
        If country = "" Then
            Sheets("Results").Range("B10:J200000").Clear
            MsgBox "You must select a country in order to search the database. Please do so in the drop-down list provided."
            Sheets("Results").Range("D5").ClearContents
            Sheets("Results").Range("D6").ClearContents
            Sheets("Results").Range("D7").ClearContents
            Exit Sub

        'If the country field is filled in and there results from the search made
        ElseIf Sheets("Database").Cells(i, 1) = country And _
            (Sheets("Database").Cells(i, 3) = Category Or Category = "") And _
            (Sheets("Database").Cells(i, 4) = Subcategory Or Subcategory = "") Then

                'Copy the headers of the table
                With Sheets("Database")
                .Range("A1:I1").Copy
                End With
                Sheets("Results").Range("B10:J10").PasteSpecial

                'Copy the rows of the table that match the search query
                With Sheets("Database")
                .Range(.Cells(i, 1), .Cells(i, 9)).Copy
                End With
                Sheets("Results").Range("B20000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

        ElseIf Sheets("Database").Cells(i, 1) = country And _
            (Sheets("Database").Cells(i, 3) <> Category) Then
            MsgBox "The database has no information that matches this search."
            Sheets("Results").Range("D5").ClearContents
            Sheets("Results").Range("D6").ClearContents
            Sheets("Results").Range("D7").ClearContents
            Exit Sub

        End If

    Next i

Victor Moraes Victor Moraes · Accepted Answer · 2016-11-22T15:35:41

You are in control of the MsgBox so all you need to do is properly handle your logic to change the messages accordingly.

One very easy way to go would be to simply change this line:

MsgBox "The database has no information that matches this search."

With the values from your variables, like you mentioned:

MsgBox "There is no information regarding " & Category & " in the " & country & "."

Supposing Category contains "Media" and country contains "US" at the moment of the search, this will output "There is no information regarding Media in the US." as you expected.

If you want different patterns for your messages depending on the exact value entered by the user, you would have to work with more If...Then...Else to properly handle them. Here I'm just using a set phrase and changing the hardcoded values with parameters.

MsgBox with user-inputted variable

1 Answers