how can i shortcut multiple line of repetitive codes like this?

[trying_to_learn_coding]

HELLO GUYS, I NEED HELP.
my code is something like this,

   If CMBCHEMLABEL1.value= TEXTLEGENDTEST1 Then
        Sheets("chem").Shapes("SHLABEL1").TextFrame2.TextRange.Characters.Text = CMBCHEMLABEL1.value
        Sheets("chem").Shapes("SHTEST1").TextFrame2.TextRange.Characters.Text = TXTCHEMTEST1.value
        Sheets("chem").Shapes("SHNVUNIT1").TextFrame2.TextRange.Characters.Text = TEXTLEGUNIT1.value
        Sheets("chem").Shapes("SHNVM1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRM1.value
        Sheets("chem").Shapes("SHNVF1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRF1.value
        Sheets("chem").Shapes("SHNVCH1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRCH1.value
   
    ElseIf CMBCHEMLABEL1.value= TEXTLEGENDTEST2 Then
        Sheets("chem").Shapes("SHLABEL1").TextFrame2.TextRange.Characters.Text = CMBCHEMLABEL1.value
        Sheets("chem").Shapes("SHTEST1").TextFrame2.TextRange.Characters.Text = TXTCHEMTEST1.value
        Sheets("chem").Shapes("SHNVUNIT1").TextFrame2.TextRange.Characters.Text = TEXTLEGUNIT2.value
        Sheets("chem").Shapes("SHNVM1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRM2.value
        Sheets("chem").Shapes("SHNVF1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRF2.value
        Sheets("chem").Shapes("SHNVCH1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRCH2.value
   
    ElseIf CMBCHEMLABEL1.value= TEXTLEGENDTEST3 Then
        Sheets("chem").Shapes("SHLABEL1").TextFrame2.TextRange.Characters.Text = CMBCHEMLABEL1.value
        Sheets("chem").Shapes("SHTEST1").TextFrame2.TextRange.Characters.Text = TXTCHEMTEST1.value
        Sheets("chem").Shapes("SHNVUNIT1").TextFrame2.TextRange.Characters.Text = TEXTLEGUNIT3.value
        Sheets("chem").Shapes("SHNVM1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRM3.value
        Sheets("chem").Shapes("SHNVF1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRF3.value
        Sheets("chem").Shapes("SHNVCH1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRCH3.value

    ElseIf CMBCHEMLABEL1.value= TEXTLEGENDTEST4 Then
        Sheets("chem").Shapes("SHLABEL1").TextFrame2.TextRange.Characters.Text = CMBCHEMLABEL1.value
        Sheets("chem").Shapes("SHTEST1").TextFrame2.TextRange.Characters.Text = TXTCHEMTEST1.value
        Sheets("chem").Shapes("SHNVUNIT1").TextFrame2.TextRange.Characters.Text = TEXTLEGUNIT4.value
        Sheets("chem").Shapes("SHNVM1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRM4.value
        Sheets("chem").Shapes("SHNVF1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRF4.value
        Sheets("chem").Shapes("SHNVCH1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRCH4.value
        
    'more and more like this until i reach

   ElseIf CMBCHEMLABEL1.value= TEXTLEGENDTEST20 Then
        Sheets("chem").Shapes("SHLABEL1").TextFrame2.TextRange.Characters.Text = CMBCHEMLABEL1.value
        Sheets("chem").Shapes("SHTEST1").TextFrame2.TextRange.Characters.Text = TXTCHEMTEST1.value
        Sheets("chem").Shapes("SHNVUNIT1").TextFrame2.TextRange.Characters.Text = TEXTLEGUNIT20.value
        Sheets("chem").Shapes("SHNVM1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRM20.value
        Sheets("chem").Shapes("SHNVF1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRF20.value
        Sheets("chem").Shapes("SHNVCH1").TextFrame2.TextRange.Characters.Text = TEXTLEGRRCH20.value
   
    End If

i write this code for every comboboxes im using, so lets say i have 20 comboboxes, then i copy/paste this code for everyone of them. and also if there are a lot of TEXTLEGENDTEST.values, like 20 of them, its so time consuming to edit in each comboboxes and a lot of lines of codes.

it is working by the way but maybe there are more other options and make it short, look neat.

thank you

 

[DanteAmor]

Hi @trying_to_learn_coding
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.


In your code, what do you have in what I have highlighted in red.
That is, is it a variable, is it an object, is it a cell, is it another control?

Could you post all your code?

Or explain what you are storing in it.

If CMBCHEMLABEL1.Value = TEXTLEGENDTEST1 Then


ElseIf CMBCHEMLABEL1.Value = TEXTLEGENDTEST2 Then


ElseIf CMBCHEMLABEL1.Value = TEXTLEGENDTEST3 Then

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[trying_to_learn_coding]

Hi @trying_to_learn_coding
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.


In your code, what do you have in what I have highlighted in red.
That is, is it a variable, is it an object, is it a cell, is it another control?

Could you post all your code?

Or explain what you are storing in it.

If CMBCHEMLABEL1.Value = TEXTLEGENDTEST1 Then


ElseIf CMBCHEMLABEL1.Value = TEXTLEGENDTEST2 Then


ElseIf CMBCHEMLABEL1.Value = TEXTLEGENDTEST3 Then

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

hello sir Dante Amor, thank you for sparing your time and understanding.
im so sorry, i am new to this, i just learned vba a few weeks ago so i don't really know. I work in medical field and no background studies in coding, I learn in youtube and in mrexcel.com
i think i can describe it to you

TEXTLEGENDTEST1, TEXTLEGENDTEST2, TEXTLEGENDTEST3 are textboxes in my userform containing words. words like "fasting blood sugar", "random blood sugar" like that.

my purpose for this is to print an output result for fasting blood sugar or any test with its test result value, unit, and reference range for male, female and child
hope i made myself clear, and i hope you understand.

 

[trying_to_learn_coding]

Hi @trying_to_learn_coding
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.


In your code, what do you have in what I have highlighted in red.
That is, is it a variable, is it an object, is it a cell, is it another control?

Could you post all your code?

Or explain what you are storing in it.

If CMBCHEMLABEL1.Value = TEXTLEGENDTEST1 Then


ElseIf CMBCHEMLABEL1.Value = TEXTLEGENDTEST2 Then


ElseIf CMBCHEMLABEL1.Value = TEXTLEGENDTEST3 Then

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

also sir, i try the value of TEXTLEGENDTEST using the value from the cell and it works. i just want to explore more options that could shorten the codes =)

 

[DanteAmor]

TEXTLEGENDTEST1, TEXTLEGENDTEST2, TEXTLEGENDTEST3 are textboxes in my userform

ok.

So, what you need is to compare the CMBCHEMLABEL1 with each of the TEXTLEGENDTEST (from 1 to 20)
If it finds it, the texts of the shapes are updated.

Replace all your code you have for that comparison with the following code:

  Dim n As Long, i As Long

  'compare the CMBCHEMLABEL1 with each of the TEXTLEGENDTEST (from 1 to 20)
  For i = 1 To 20
    If CMBCHEMLABEL1.Value = Controls("TEXTLEGENDTEST" & i).Value Then
      n = i
      Exit For
    End If
  Next
  
  If n = 0 Then
    MsgBox "No TEXTLEGENDTEST value matches the CMBCHEMLABEL1 value"
  Else
    With Sheets("chem")
      .Shapes("SHLABEL1").TextFrame2.TextRange.Characters.Text = Controls("CMBCHEMLABEL" & n).Value
      .Shapes("SHTEST1").TextFrame2.TextRange.Characters.Text = Controls("TXTCHEMTEST" & n).Value
      .Shapes("SHNVUNIT1").TextFrame2.TextRange.Characters.Text = Controls("TEXTLEGUNIT" & n).Value
      .Shapes("SHNVM1").TextFrame2.TextRange.Characters.Text = Controls("TEXTLEGRRM" & n).Value
      .Shapes("SHNVF1").TextFrame2.TextRange.Characters.Text = Controls("TEXTLEGRRF" & n).Value
      .Shapes("SHNVCH1").TextFrame2.TextRange.Characters.Text = Controls("TEXTLEGRRCH" & n).Value
    End With
  End If

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

​

 

[trying_to_learn_coding]

ok.

So, what you need is to compare the CMBCHEMLABEL1 with each of the TEXTLEGENDTEST (from 1 to 20)
If it finds it, the texts of the shapes are updated.

Replace all your code you have for that comparison with the following code:

  Dim n As Long, i As Long

  'compare the CMBCHEMLABEL1 with each of the TEXTLEGENDTEST (from 1 to 20)
  For i = 1 To 20
    If CMBCHEMLABEL1.Value = Controls("TEXTLEGENDTEST" & i).Value Then
      n = i
      Exit For
    End If
  Next
 
  If n = 0 Then
    MsgBox "No TEXTLEGENDTEST value matches the CMBCHEMLABEL1 value"
  Else
    With Sheets("chem")
      .Shapes("SHLABEL1").TextFrame2.TextRange.Characters.Text = Controls("CMBCHEMLABEL" & n).Value
      .Shapes("SHTEST1").TextFrame2.TextRange.Characters.Text = Controls("TXTCHEMTEST" & n).Value
      .Shapes("SHNVUNIT1").TextFrame2.TextRange.Characters.Text = Controls("TEXTLEGUNIT" & n).Value
      .Shapes("SHNVM1").TextFrame2.TextRange.Characters.Text = Controls("TEXTLEGRRM" & n).Value
      .Shapes("SHNVF1").TextFrame2.TextRange.Characters.Text = Controls("TEXTLEGRRF" & n).Value
      .Shapes("SHNVCH1").TextFrame2.TextRange.Characters.Text = Controls("TEXTLEGRRCH" & n).Value
    End With
  End If

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

​

thank you so much Dante Amor, it works just as I want it to. it really helps me a lot. Majority of my codes are based on this lines of codes not just for chemistry section, but for other sections as well. I've learned something new today. Thank you for sharing your knowledge, time and expertise.

God Bless

 

[trying_to_learn_coding]

Hello Sir, again thank you so much for helping me fixing my codes, it works perfectly fine. Regarding to that, in my serology and immunology section, i think there are more than 50 kind of test. So I was thinking, maybe I will link the value of TEXTLEGENDTEST to CELL value as well as the others like the TEXTLEGUNIT, TEXTLEGMMR etc to the value of a Cell. I would like to ask if they share the same lines of codes or have some changes? Thank you

 

[DanteAmor]

maybe I will link the value of TEXTLEGENDTEST to CELL value

I don't know what you mean by CELL value.

Another question: do you have more than 50 textboxes in your userform?

I don't understand what your goal is with the textboxes and the comboboxes.

Maybe if you could share a file with test data and a short explanation of what you have and what you want to do, then maybe, I could help you with the best practice with better code and maybe less textboxes.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[trying_to_learn_coding]

My Sincere apology sir for causing you more trouble.

what I mean is, I will get the value from a cell, like " sheets("sheet1").range("a1") instead of getting the value from the textbox. I hope I made that clear.
Actually sir, I try to understand the codes you've given, and I think I get it how it works. So I twerk it a little that fits to my needs and it actually it works. I could not have done it without your help. I deleted those textboxes. I will paste here the code and the credit belongs to you sir DanteAmor.


Dim n As Long, i As Long

For i = 1 To 100
If CMBCHEMLABEL1.Value = Sheets("tests").Range("C" & i).Value Then
n = i
Exit For
End If
Next

If n = 0 Then
MsgBox "Test not Available or no record in the Test Records"
Else

With Sheets("chem")
.Shapes("SHLABEL1").TextFrame2.TextRange.Characters.Text = CMBCHEMLABEL1.Value
.Shapes("SHNVUNIT1").TextFrame2.TextRange.Characters.Text = Sheets("tests").Range("D" & i).Value
.Shapes("SHNVM1").TextFrame2.TextRange.Characters.Text = Sheets("tests").Range("E" & i).Value
.Shapes("SHNVF1").TextFrame2.TextRange.Characters.Text = Sheets("tests").Range("F" & i).Value
.Shapes("SHNVCH1").TextFrame2.TextRange.Characters.Text = Sheets("tests").Range("G & i).Value
End With
End If


Again sir thank you so much. My question ends here. You've been a great help.