Combobox decides path of textbox value to particular cells in excel, Need VBA Code

[Mukhtar]

Dear Forum Member,

I need your help regarding Excel VBA coding.

In sheet1

There are 9 coloumns A1, A2, A3, B1, B2, B3, C1, C2 & C3.

There are 3 Texboxes nanmed (Red, Blue & Green).

There is 1 Combobox containing Categories "A" "B" & "C".

When any numerical or Text value is filled in any or all of the 3 Textboxes (Red, Blue or Green), this happens on clicking the Submit button.

If "A" is selected in the Combobox, then these value from the 3 Textboxes goes into cells A1, A2 & A3 respectively.

If "B" is selected in the Combobox, then these value from the 3 Textboxes goes into cells B1, B2 & B3 respectively.

If "C" is selected in the Combobox, then these value from the 3 Textboxes goes into cells C1, C2 & C3 respectively.

After clearing the form and when new entry is made in the Boxes, The next new entry of new value goes into new Row.

Excel sheet1 contains complete columns and userform.
Sample Sheet shows what I need. (It is in Normal Excel with Normal Formulas)

I need Excel VBA code for this.( I use MS Excel 2007)

(Its like this, the combobox decides the path of Textbox values to the specific cells. The values goes into the cells from the Textboxes and not from cells to textboxes)

Hope some kind person will help me out with this.

Lot of thanks in advance.

Regards,
Mukhtar

 

[p45cal]

1. On the Sample sheeet, add a button assigned to:

Sub blah()
  With Sheets("Sample")
    Select Case .Range("L16").Value
      Case "A": ofset = 0
      Case "B": ofset = 3
      Case "C": ofset = 6
    End Select
    If Not IsEmpty(ofset) Then
      Set Destn = .Range("A2:C11").Offset(, ofset)
      Set DestRow = Destn.Find("*", Destn.Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious, False, searchformat:=False)
      If Not DestRow Is Nothing Then Intersect(Destn, DestRow.Offset(1).EntireRow).Value = Array(.Cells(14, "C").Value, .Cells(14, "E").Value, .Cells(14, "G").Value)
    End If
  End With
End Sub

2. In the userform's code-module add this code:

Private Sub CommandButton1_Click() 'submit button
  Select Case Me.ComboBox1.Value
    Case "A": ofset = 0
    Case "B": ofset = 3
    Case "C": ofset = 6
  End Select
  If Not IsEmpty(ofset) Then
    Set Destn = Sheets("Sheet1").Range("A1:C10").Offset(, ofset)
    Set DestRow = Destn.Find("*", Destn.Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious, False, searchformat:=False)
    If Not DestRow Is Nothing Then
      Set Destn = Intersect(Destn, DestRow.Offset(1).EntireRow)
      Destn.Value = Array(TextBox1.Value, TextBox2.Value, TextBox3.Value)
    End If
  End If
End Sub

See attached.

 

[Belleke]

Or
see attached

 

[p45cal]

@Belleke , at first I too used range.end(xlup) to find the next empty row but realised that the user can leave any textbox blank; if they leave the first textbox blank and click Submit, nothing will be put in the first column, so the next time Submit is clicked existing data is likely to be overwritten, which is why I moved to a range.find route.

 

[Mukhtar]

Dear p45cal,
Lots,Lots,Lots of thanks to you, Highly grateful to you. you solved my problem.
My age is 60, If I am elder than you, My blessings to you and if I am younger than you, then Lots of thanks to you. Thanks to Chandoo and his Forum for the Fast reply.

Regards,
Mukhtar