Excel: Concatenation depending on ComboBox-Value

Hey guys, I have this project I’m currently working on and would need some advice on how to implement a certain concatenation. So I have a UserForm (frml_1) which serves as an input mask for the user. The user can choose options based on 4 ComboBoxes on this UserForm:

  • ComboBox cb_Workers to choose a worker ==> data in worksheet “Workers”
  • ComboBox cb_Case to choose a case ==> data in worksheet “Cases”
  • ComboBox cb_Class to choose a class ==> data in worksheets “MR1 to MR5”
  • ComboBox cb_Index to choose an index ==> data in worksheet “Cases”
All these selections are written to a worksheet called “Summary” in order to display the choices the user made. Now my second UserForm (frml_Test1) activates after all choices have been made and the Accept Button on UserForm frml_1 has been clicked. It serves as a Questionnaire. There are different questions on different worksheets (“Questions1-6”) and depending on the cb_Case- value selected previously, the questions are different. This all works fine (thanks again to bertie who helped me a lot!). Now what I would need to realize is the following: The first two questions, which are used for this Questionnaire, need to be concatenated with the value of the class, which has been chosen previously on UserForm frml_1. For example the user makes the following selections:
  • Worker 1 = Firstname1, Surname 1 (the workers category is displayed in the “category”- TextBox, which is MR1)
  • Case “New”
  • Class “1”
  • Index doesn’t matter, but let’s take “Standard”
Now that case "New" has been chosen, the UserForm frml_Test1 will use the Questions from the worksheet "Question1" for the Questionnaire. Because "class 1" has been chosen, question 1 (in worksheet "Question1") would have to be concatenated with the value in worksheet "MR1" (cell B3). Question 2 (in worksheet "Question1") should be concatenated with the bonusvalue in worksheet "MR1" (cell C3). So I probably need a routine, which checks the ComboBox cb_Class what class has been chosen, search the corresponding worksheet for the value and concatenate this value with the Question being used... It’s probably really hard to follow my weird description, maybe if someone has the time take a quick look at my attached file it will get clearer... Anyway, I thought at least I’ll try to write down my problem in order to get some helpful suggestions, which would be highly appreciated. Link to file: https://dl.dropboxusercontent.com/u/84440277/Excel/File_D.xlsm Best wishes Des

This question generated 21 answers. To proceed to the answers, click here.

This thread is current as of March 17, 2014.

For more resources for Microsoft Excel