Concatenation depending on ComboBox-Value

Desperation · Mar 17, 2014

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

bertie · Mar 17, 2014

Hi, me again.

You are writing the control selections to the Summary sheet. Why not write the bonus values there also, say in columns G and H. Any time you need them just reference their location on the Summary sheet.

I would do this in the Accept Click event.
You can build the sheet name for the bonus using the CATAGORY TextBox.
And you can get the bonus "row" using the CLASS ComboBox.

Rich (BB code):

Private Sub cb_Accept_Click()
   Dim sheetBonus As String
   Dim rowBonus As Long


   Sheets("tbl_Test2").Range("A1:E2").Copy Sheets("Summary").Cells(2, 2)
 
   'store bonus values on workers sheet
   With Sheets(Me.tbCategory.Value & "_T")
      Sheets("Summary").Range("G3").Value = .Range("B" & 2 + Me.cb_Class.Value)
      Sheets("Summary").Range("H3").Value = .Range("C" & 2 + Me.cb_Class.Value)
   End With
   
  
frml_Test1.Show
frml_1.Hide


End Sub

Desperation · Mar 18, 2014

Hey bertie,

thanks again for your helpful input, I'll implement it like you suggested. I think the following concatenation should be doable for me (at least I'm going to try

).
Your method of getting the row for the value and the bonusvalue is very good, but how would you get the row if the cb_Class.Value wasn't a numerical value, but a string (e.g. class "1" would be class "A1-B1" and so on)?
Many thanks for your opinion!

Des

bertie · Mar 18, 2014

You are of course correct. Text Boxes hold values of data type string.

For numerical string values like, "1", use a conversion function to change the value to data type long:

Rich (BB code):

'store bonus values on workers sheet
   With Sheets(Me.tbCategory.Value & "_T")
      Sheets("Summary").Range("G3").Value = .Range("B" & 2 + CLng(Me.cb_Class.Value))
      Sheets("Summary").Range("H3").Value = .Range("C" & 2 + CLng(Me.cb_Class.Value))
   End With

For values like "A1-B1" there are various techniques.
Use the RIGHT function,
e.g., Right("A1-B1",1) would return the last character in the string.
Incorporate the CLNG conversion function
myRow = Clng(Right("A1-B1",1)), would return the numerical value of the last character in the string.
Problem, what happens if the string is "A10-B10"? You would need to take the last TWO characters.

Alternatively, you could loop through "A1-B1" until you find a numerical character;
Isolate a sub string, e.g., "1-B1"
The use the VAL function to return the numerical part.

The approach you take really depends on the string being analysed. Ideally you would want one approach to process all such strings. So you would need some consistency in how you design such strings.
e.g.,
A1-B1
A10-B10
etc,

Rather than having a mixed up bunch of strings to process:
A1-B1
A10B10, no hyphen, no consistency.

bertie · Mar 18, 2014

PS.
The CLASS control is a ComboBox.
The simplest way to get the relevant row would be to use the ComBox.ListIndex.

myRow = me.cb_Class.ListIndex + 3
The "+ 3" takes into account header rows on the sheet which populated the ComboBox.

Desperation · Mar 18, 2014

Hey,
I applied your approach using the CB Class ListIndex to get the rows and it works like I want it to, that’s brilliant bertie, thank you!
I also tried to realize the concatenation in order to put together question 1 with “value” (G3 on summary sheet) and question 2 with “bonus” (H3 on summary sheet) of the various question- sheets. Maybe it can be done better, but for now it seems to work (except for the Format of value and bonus, which should be currency, but that's no big deal).

Code looks like this:

Code:

Private Sub cb_Accept_Click()
Dim myRow As String
Dim myQuestionValue As String, myQuestionBonus As String
Dim myValue As Currency, myBonus As Currency
Dim conValue As String, conBonus As String

Sheets("tbl_Test2").Range("A1:E2").Copy Sheets("Summary").Cells(2, 2)
myRow = Me.cb_Class.ListIndex + 3
'store value & bonus value on "Summary"- sheet

With Sheets(Me.tbCategory.Value & "_T")
   Sheets("Summary").Range("G3").Value = .Range("B" & myRow)
   Sheets("Summary").Range("H3").Value = .Range("C" & myRow)
End With

'=========================================
'       Concatenation of Questions
'=========================================

'concatenate question 1 with value on sheet "Summary"
myQuestionValue = Sheets(SheetName).Range("C3").Value
myValue = Sheets("Summary").Range("G3").Value
myValue = Format(myValue, "Currency")
conValue = myQuestionValue & " " & myValue & "?"

With Sheets(SheetName)
    .Range("C3").Value = conValue
End With

'concatenate question 2 with bonus on sheet "Summary"
myQuestionBonus = Sheets(SheetName).Range("C4").Value
myBonus = Sheets("Summary").Range("H3").Value
myBonus = Format(myBonus, "Currency")
conBonus = myQuestionBonus & " " & myBonus & "?"

With Sheets(SheetName)
    .Range("C4").Value = conBonus
End With

frml_Test1.Show
frml_1.Hide

End Sub

Thanks once again for saving my day bertie, I really appreciate it!

bertie · Mar 18, 2014

Use the NumberFormat to set the output cells as currency.

Rich (BB code):

   With Sheets(Me.tbCategory.Value & "_T")
      Sheets("Summary").Range("G3").Value = .Range("B" & myRow)
      Sheets("Summary").Range("H3").Value = .Range("C" & myRow)
   End With
   
   Sheets("Summary").Range("G3:H3").NumberFormat = "£#,###.00"

Desperation · Mar 18, 2014

Hey bertie,
thanks for the reply! I tried to implement it like you suggested and it works in a sense that Range("G3:H3") on the summary sheet gets the currency format,
unfortunately this doesn't apply to the concatenated value displayed in the worksheet/textbox.

Des

bertie · Mar 18, 2014

Rich (BB code):

myvalue = Format(Sheets("Summary").Range("G3").Value, "$#,###.00")

Then concatenate the myValue variable.

When you assign myValue to the TextBox use the Text property to preserve formatting.

Rich (BB code):

me.TextBox1.Text = myValue

[edit] myValue should be data type String.

Desperation · Mar 19, 2014

bertie said:
Code:

myvalue = Format(Sheets("Summary").Range("G3").Value, "$#,###.00")

This works just fine, thanks again!

I was just wondering if this way of concatenating, namely writing the concatenated strings (question 1 with value, question 2 with bonus) directly to the question cells (C3 and C4 of the question- sheets) is the best solution... Maybe writing the concatenation to an adjacent cell and just referencing the content of those cells (directly through the sheet) to C3 and C4 would be better. Because the workbook should stay open and allow the user to make several new requests. So it's probably easier to make an event where the adjacent cell with concatenation gets deleted then going into the "question" cell and deleting only the value that has been added to the question.

Well, there's still much to think about

.

Anyhow, thank you so much bertie, without your help I would probably have given up already!

Concatenation depending on ComboBox-Value

Desperation

New Member

Excel Facts

bertie

Well-known Member

Desperation

New Member

bertie

Well-known Member

bertie

Well-known Member

Desperation

New Member

bertie

Well-known Member

Desperation

New Member

bertie

Well-known Member

Desperation

New Member

Similar threads

Forum statistics

Share this page

Concatenation depending on ComboBox-Value

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock