Building a dynamic User Form that updates an Index

[jtotman]

Hi! This is my first time posting on this forum, but I will try to be as clear as possible -- I am brand new to VBA so I appreciate any help that you all can provide.

I have been working on some code that consolidates data from separate workbooks into one workbook. Then, using the index and match functions, I classify each row of data. That data is then sorted into a separate page using sumifs.

The issue I am running into is that the indexes that I am using are routinely updated. As a result, when the data is classified, there are often "#N/A" showing up which then eliminates the effectiveness of the sumifs function. What I have done, is made an If statement in VBA that goes through the data classification column and finds every NA and copies the new correlated code to the bottom of the relevant index.

What I would like to do is make a User Form that then goes through the new data added to the index and allows the user to classify each new code using a combo box. I am envisioning a pop-up that says "What is code AB123 classified as?" and then gives the user the option via a combo box (there are only two possible classifications). Is there a way to make this dynamic so that it will go line by line and open a user form for each code that does not have a correlating classification?

Thank you for any help that you can provide

 

[mikerickson]

If there are only two options, there isn't any need for a Userform, a MsgBox is sufficent.

Select Case MsgBox("Yes, this is code A." & vbcr & "No, it is code B.", vbYesNoCancel)
    Case vbYes
        MsgBox "its A"
    Case vbNo
        MsgBox "its B"
    Case vbCancel
        MsgBox "you canceled"
End Select

 

[jtotman]

If there are only two options, there isn't any need for a Userform, a MsgBox is sufficent.

Select Case MsgBox("Yes, this is code A." & vbcr & "No, it is code B.", vbYesNoCancel)
    Case vbYes
        MsgBox "its A"
    Case vbNo
        MsgBox "its B"
    Case vbCancel
        MsgBox "you canceled"
End Select

Thank you! Would you mind explaining the basics of how the Message Box works?

Also, I have a similar issue that I was hoping to figure out myself once I solved this, but it has three options. Do you have any suggestions for that?

 

[mikerickson]

The Button argument specifies how many buttons the MsgBox shows (and give some control over what they say).
vbYesNoCancel specifies that the box is to have three buttons, Yes No and Cancel.

The value returned by the MsgBox function is determined by which of those buttons is pressed. vbYes, vbNo, or vbCancel. Which is good for choosing between two options (plus the "cancel I didn't mean it" option).

If you are dealing with three options (plus Cancel), that is more than the MsgBox's maximum of three buttons and a userform is needed. (Or eliminate the Cancel option, which makes for a confusion user interface)

 

[jtotman]

Thank you very much! This worked well for me. I ended up using it for when I needed to choose from three options as well - selecting cancel resulted in a value (that way there is no way to avoid classifying each row of data in the index).

Thank you again!