Thread: Add value to cell depending on combobox value !

Hi !

Im trying to figure out how to controll when i add data in to a cell whit a combobox and hope that someone could help me a little.

The code belows adds the data like this when i press the button :

TextBox1 adds value to cell A2,B2,C2
TextBox2 adds value to cell A3,B3,C3
TextBox3 adds value to cell A4,B4,C4

I want to contol this whit the combobox.
I have 3 values in comboBox1 (Value1, Value2 and Value3)

If i pick Value1 i want the Textbox 1-3 to add the data as above:

TextBox1 adds value to cell A2,B2,C2
TextBox2 adds value to cell A3,B3,C3
TextBox3 adds value to cell A4,B4,C4

If i pick Value2 i want i to add like this:
TextBox1 adds value to cell E2,F2,G2
TextBox2 adds value to cell E3,F3,G3
TextBox3 adds value to cell E4,F4,G4

If i pick Value3 i want i to add like this:
TextBox1 adds value to cell I2,J2,K2
TextBox2 adds value to cell I3,J3,K3
TextBox3 adds value to cell I4,J4,K4

I also want Textbox 4-6 to display results depending on the value in the combobox.

If i pick Value1 i want them to show the value like this:

TextBox4 adds value to cell A6
TextBox5 adds value to cell B6
TextBox6 adds value to cell C6

If i pick Value2 i want them to show the value like this:

TextBox4 adds value to cell E6
TextBox5 adds value to cell F6
TextBox6 adds value to cell G6

If i pick Value3 i want them to show the value like this:

TextBox4 adds value to cell I6
TextBox5 adds value to cell J6
TextBox6 adds value to cell K6

I think you get the idea and tanx in advance

Best regards

Petter

Private Sub UserForm_Initialize()

With ComboBox1
   .AddItem "Value1"
   .AddItem "Value2"
   .AddItem "Value3"

   
End With
End Sub

Private Sub Cmd1_Click()


          
     
    Sheets("Blad1").Range("A2").Value = TextBox1.Value
    Sheets("Blad1").Range("A3").Value = TextBox2.Value
    Sheets("Blad1").Range("A4").Value = TextBox3.Value
   
    
    Sheets("Blad1").Range("B2").Value = TextBox1.Value
    Sheets("Blad1").Range("B3").Value = TextBox2.Value
    Sheets("Blad1").Range("B4").Value = TextBox3.Value
   
    Sheets("Blad1").Range("C2").Value = TextBox1.Value
    Sheets("Blad1").Range("C3").Value = TextBox2.Value
    Sheets("Blad1").Range("C4").Value = TextBox3.Value
   
    
    
    
     
     
    
    TextBox4 = Range("A6").Value
TextBox4.Text = Format(TextBox4.Value, "00.00 %")

TextBox5 = Range("B6").Value
TextBox5.Text = Format(TextBox5.Value, "00.00 %")

TextBox6 = Range("C6").Value
TextBox6.Text = Format(TextBox6.Value, "00.00 %")
    
    
     
End Sub

my idea is that

combobox have property ListIndex (first element have index 0)
column A = 1, B = 2, C = 3
column E = A + 4, F = B + 4, G = C + 4

so you can use algorithm:

    With Sheets("Blad1").Range("A2")
            .offset(,1+ combobox1.listindex * 4).Value = TextBox1.Value
            .offset(,2+ combobox1.listindex * 4).Value = TextBox1.Value
            .offset(2,3+ combobox1.listindex * 4).Value = TextBox1.Value

            .offset(1,1+ combobox1.listindex * 4).Value = TextBox2.Value
            .offset(1,2+ combobox1.listindex * 4).Value = TextBox2.Value
            .offset(1,3+ combobox1.listindex * 4).Value = TextBox2.Value

            .offset(2,1+ combobox1.listindex * 4).Value = TextBox3.Value
            .offset(2,2+ combobox1.listindex * 4).Value = TextBox3.Value
            .offset(2,3+ combobox1.listindex * 4).Value = TextBox3.Value
    End With

This should cover first part of your need

I got it to work whit this code

Petter

Private Sub Cmd1_Click()


          
     
   With Sheets("Blad1").Range("A2")
            .Offset(, 0 + ComboBox1.ListIndex * 4).Value = TextBox1.Value
            .Offset(, 1 + ComboBox1.ListIndex * 4).Value = TextBox1.Value
            .Offset(, 2 + ComboBox1.ListIndex * 4).Value = TextBox1.Value

            .Offset(1, 0 + ComboBox1.ListIndex * 4).Value = TextBox2.Value
            .Offset(1, 1 + ComboBox1.ListIndex * 4).Value = TextBox2.Value
            .Offset(1, 2 + ComboBox1.ListIndex * 4).Value = TextBox2.Value

            .Offset(2, 0 + ComboBox1.ListIndex * 4).Value = TextBox3.Value
            .Offset(2, 1 + ComboBox1.ListIndex * 4).Value = TextBox3.Value
            .Offset(2, 2 + ComboBox1.ListIndex * 4).Value = TextBox3.Value
            
    

  If Me.ComboBox1.Value = "Value1" Then
        TextBox4.Value = Worksheets("Blad1").Range("A6").Value
        TextBox4.Text = Format(TextBox4.Value, "00.00 %")
    End If
     
    If Me.ComboBox1.Value = "Value2" Then
        TextBox5.Value = Worksheets("Blad1").Range("b6").Value
        TextBox5.Text = Format(TextBox5.Value, "00.00 %")
    End If
    
      If Me.ComboBox1.Value = "Value3" Then
        TextBox6.Value = Worksheets("Blad1").Range("c6").Value
        TextBox6.Text = Format(TextBox5.Value, "00.00 %")
    End If
    
            
    End With
    
            
 
  

     End Sub

Hello Petter120,

I know post is SOLVED but consider to use below shorter code which should do the same you need in shorter way

Option Base 0
Private Sub Cmd1_Click()
If ComboBox1.ListIndex = -1 Then MsgBox "you have to select value from combobox": Exit Sub

T1 = Array("TextBox1", "TextBox2", "TextBox3")
T2 = Array("TextBox4", "TextBox5", "TextBox6")

   With Sheets("Blad1").Range("A2")
        For i = 0 To UBound(T1)
            .Offset(i, 0 + ComboBox1.ListIndex * 4).Value = Me.Controls(T1(i)).Value
            .Offset(i, 1 + ComboBox1.ListIndex * 4).Value = Me.Controls(T1(i)).Value
            .Offset(i, 2 + ComboBox1.ListIndex * 4).Value = Me.Controls(T1(i)).Value
        Next i
    End With
  
  Me.Controls(T2(ComboBox1.ListIndex)) = Format(Worksheets("Blad1").Range("A6").Offset(ComboBox1.ListIndex, 0).Value, "00.00 %")

End Sub

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "Value1"
        .AddItem "Value2"
        .AddItem "Value3"
    End With
End Sub