CODEWITHSUNDEEP
vbaexcel
zakaria
zakaria

Reputation: 13

VBA Excel 2 comboboxes issue

I am trying to make an application which lets you update sales condition details using two comboboxes.

See this screenshots for a demo:

1) Datasheet

2) this Userform concerns the creation of a new sales condition

3) a second Userform is required to modify the data and update it in the desired sheet

Regarding my source code for creating a new sales condition, you can find it in here: 

Private Sub bAnnuler_Click()

    Unload Me 

End Sub

Private Sub bEnregistrer_Click()

   Sheets("ConditionsVente").Activate
   Range("A1").Select
   Selection.End(xlDown).Select  'On se positionne sur la derniere ligne non  vide
   Selection.Offset(1, 0).Select 'On se décale d'une ligne vers le bas
  'ActiveCell = txtNom.Value
  ActiveCell.Offset(0, 3).Value = txtPrix
  ActiveCell.Offset(0, 4).Value = txtDélai

End Sub

Private Sub bReinitialiser_Click()
  txtPrix = ""
  txtDélai = ""
End Sub


Private Sub cboFournisseur_Change()

End Sub

Private Sub UserForm_Initialize()
  'initialiser combobox fournisseur
  Dim Fournisseurs As Range
  Dim Matieres As Range

  Set Fournisseurs = Worksheets("Fournisseurs").Range("A2:A" &  Worksheets("Fournisseurs").Range("A2").End(xlDown).Row)

  Me.cboFournisseur.MaxLength = Fournisseurs.Count
  Me.cboFournisseur.List = Fournisseurs.Value
  'initialiser combobox matiere
  Set Matieres = Worksheets("Matieres").Range("A2:A" &      Worksheets("Matieres").Range("A2").End(xlDown).Row)

  Me.cboMatiere.MaxLength = Matieres.Count
  Me.cboMatiere.List = Matieres.Value

End Sub

I have two issues: 1) when I run this code, I create a new sales condition but what is saved in the sheet are just the price (prix in french) and the delay (délai in french) and in the columns of Suppliers (Fournisseurs in french) and Raw Material (Matiere in french) are still empty.

2) the second point, in order to make a userform which let me modify the sales condition in the desired sheet, what is the simplest way to realize it ?

Upvotes: 1

Views: 53

Answers (1)

Alex P
Alex P

Reputation: 12489

For part 1 you need this:

Private Sub bEnregistrer_Click()
   Dim nextRow as Integer

   With Worksheets("ConditionsVente")

       nextRow = .Range("A1").end(xlDown).Row + 1

       .Range("A" & nextRow) = txtNom.Value
       .Range("B" & nextRow) = txtMatiere.Value
       .Range("C" & nextRow) = txtPrix
       .Range("D" & nextRow) = txtDélai
   End With
End Sub

For part 2 try this:

Private Sub btnSave_Click()
  Dim Fournisseurs As Range, Fournisseur as range

  Set Fournisseurs = Worksheets("Fournisseurs").Range("A2:A" &  Worksheets("Fournisseurs").Range("A2").End(xlDown).Row)

  For each Fournisseur in Fournisseurs
       If Fournisseur = txtNom.Value And Fournisseur.offset(0, 1) = txtMatiere.Value Then
           Fournisseur.offset(0, 3) = txtPrix
           Fournisseur.offset(0, 3) = txtDélai
       End if
  Next Fournisseur    
End Sub

Upvotes: 1

Related Questions