VBA Excel 2 comboboxes issue

Question

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

See this screenshots for a demo:

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 ?

See below - in general you should avoid using Activate and Select in your code. It is not really needed. — Alex P
– Alex P, Commented Dec 23, 2016 at 15:08

Alex P · Accepted Answer · 2016-12-23 18:44:04Z

1

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

edited Dec 23, 2016 at 18:44

answered Dec 23, 2016 at 15:07

Alex P

12.5k7 gold badges55 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zakaria Over a year ago

for the second part, in order to modify a sales condition, we should add a condition that explains that the two data of comboboxes must be existed first ? do you have any idea?

Alex P Over a year ago

Create a loop over fournisseurs and check for matiere match. Example: if range("a" & row) = "Hermes" AND range("a" & row).offset(0,1) = "matiere2" then...

Collectives™ on Stack Overflow

VBA Excel 2 comboboxes issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related