Combobox in userfrm that paste data into given cells

[espenskeie]

Hi

I try to create a userform where I can add new products to my firms database. If we get a new type of a server to our store I will open a userform and in the first combobox I will select "Server", and then the productprice and some other data that all are equal whatever category it is, Server, Cameras etc.

I would like the VBA to put all Server-products in one column, all Camera product in another. And I have tried to write this, but it only puts the data into the last specified column.

I think the problem in my code is that I haven't given the values when you choose category in the combobox. And I don't know how to do that either.

This is what my code looks like, and yes, I think it is too long, but I don't know how to shorten it and still keep the results as I want.

Private Sub cmdOkAddProd_Click()
    Dim RowCount As Long
    Dim ctl As Control
  
    
    If Me.cboProdGroup.Value = "" Then
       MsgBox "Vennligst velg produkt.", vbExclamation, "Legg til produkt"
       Me.cboProdGroup.SetFocus
       Exit Sub
    End If
         
    If Me.txtProdName.Value = "" Then
        MsgBox "Vennligst skriv inn produktnavn.", vbExclamation, "Legg til produkt"
        Me.cboProdGroup.SetFocus
        Exit Sub
    End If
    If Me.txtCost.Value = "" Then
        MsgBox "Vennligst skriv inn innkjøpspris.", vbExclamation, "Legg til produkt"
        Me.cboProdGroup.SetFocus
        Exit Sub
    End If
    If Me.txtTimeWork.Value = "" Then
        MsgBox "Vennligst skriv inn antall timer for montering.", vbExclamation, "Legg til produkt"
        Me.cboProdGroup.SetFocus
        Exit Sub
    End If
    If Me.cboMtbf.Value = "" Then
        MsgBox "Vennligst skriv inn Mean Time Befor Failure.", vbExclamation, "Legg til produkt"
        Me.cboProdGroup.SetFocus
        Exit Sub
    End If
        
    If Not IsNumeric(Me.txtCost.Value) Then
        MsgBox "Skriv inn innkjøpsprisen med tall.", vbExclamation, "Legg til produkt"
        Me.txtCost.SetFocus
        Exit Sub
    End If
    
    If Not IsNumeric(Me.txtTimeWork.Value) Then
        MsgBox "Skriv inn antall timer (tall,tall).", vbExclamation, "Legg til produkt"
        Me.txtTimeWork.SetFocus
        Exit Sub
    End If
    
    
    
    'RowCount = Worksheets("Ark3").Range("A1").CurrentRegion.Rows.Count ' place the data dynamic into the first free row
    
            '.Offset(RowCount, 1).Value = Me.txtCompNo.Value
            
    If Me.cboProdGroup.Value = C3 Then
    
    With Worksheets("Produktliste").RAnge("C4")
        .Offset(0, 0).Value = Me.cboProdGroup.Value
        .Offset(0, 1).Value = Me.txtProdName.Value
        .Offset(0, 2).Value = Me.txtCost.Value
        .Offset(0, 3).Value = Me.txtTimeWork.Value
        .Offset(0, 4).Value = Me.cboMtbf.Value
    
    End With
    
        ElseIf Me.cboProdGroup.Value = G3 Then
    
    With Worksheets("Produktliste").RAnge("G4")
        .Offset(0, 0).Value = Me.cboProdGroup.Value
        .Offset(0, 1).Value = Me.txtProdName.Value
        .Offset(0, 2).Value = Me.txtCost.Value
        .Offset(0, 3).Value = Me.txtTimeWork.Value
        .Offset(0, 4).Value = Me.cboMtbf.Value
        
    End With
    
        ElseIf Me.cboProdGroup.Value = K3 Then
    
    With Worksheets("Produktliste").RAnge("K4")
        .Offset(0, 0).Value = Me.cboProdGroup.Value
        .Offset(0, 1).Value = Me.txtProdName.Value
        .Offset(0, 2).Value = Me.txtCost.Value
        .Offset(0, 3).Value = Me.txtTimeWork.Value
        .Offset(0, 4).Value = Me.cboMtbf.Value
        
    End With
    
        ElseIf Me.cboProdGroup.Value = O3 Then
    
    With Worksheets("Produktliste").RAnge("O4")
        .Offset(0, 0).Value = Me.cboProdGroup.Value
        .Offset(0, 1).Value = Me.txtProdName.Value
        .Offset(0, 2).Value = Me.txtCost.Value
        .Offset(0, 3).Value = Me.txtTimeWork.Value
        .Offset(0, 4).Value = Me.cboMtbf.Value
        
    End With
    
        ElseIf Me.cboProdGroup.Value = S3 Then
    
    With Worksheets("Produktliste").RAnge("S4")
        .Offset(0, 0).Value = Me.cboProdGroup.Value
        .Offset(0, 1).Value = Me.txtProdName.Value
        .Offset(0, 2).Value = Me.txtCost.Value
        .Offset(0, 3).Value = Me.txtTimeWork.Value
        .Offset(0, 4).Value = Me.cboMtbf.Value
        
    End With
    
        ElseIf Me.cboProdGroup.Value = W3 Then
    
    With Worksheets("Produktliste").RAnge("W4")
        .Offset(0, 0).Value = Me.cboProdGroup.Value
        .Offset(0, 1).Value = Me.txtProdName.Value
        .Offset(0, 2).Value = Me.txtCost.Value
        .Offset(0, 3).Value = Me.txtTimeWork.Value
        .Offset(0, 4).Value = Me.cboMtbf.Value
        
    End With
    
        ElseIf Me.cboProdGroup.Value = AA3 Then
    
    With Worksheets("Produktliste").RAnge("AA4")
        .Offset(0, 0).Value = Me.cboProdGroup.Value
        .Offset(0, 1).Value = Me.txtProdName.Value
        .Offset(0, 2).Value = Me.txtCost.Value
        .Offset(0, 3).Value = Me.txtTimeWork.Value
        .Offset(0, 4).Value = Me.cboMtbf.Value
        
    End With
    
        Else
        
        'Me.cboProdGroup.Value = AE3 Then
    With Worksheets("Produktliste").RAnge("AE4")
        .Offset(0, 0).Value = Me.cboProdGroup.Value
        .Offset(0, 1).Value = Me.txtProdName.Value
        .Offset(0, 2).Value = Me.txtCost.Value
        .Offset(0, 3).Value = Me.txtTimeWork.Value
        .Offset(0, 4).Value = Me.cboMtbf.Value
        
    End With
       
    End If
                           
      For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        ElseIf TypeName(ctl) = "CheckBox" Then
            ctl.Value = False
        End If
    
    Next ctl
    
    Unload Me
End Sub

I hope someone know how to solve this.

Best regards
Espen

 

[espenskeie]

SORRY!!!

I just discovered that I forgot to add the

Range("K4") etcetc

Now it is working..


Question then is, can this be written in a more effective way?

 

[espenskeie]

Ok, I'm talking to myself now, but maybe someone could help me out. When I add a new product in same category, for example the first one in column C4, it writes over the data in those cells.

How can I make it such that it starts writing in the first emtpy row below?