Fill multiple comboboxes (10) from one source (sheet) and textboxes accordingly

[djemy1975*]

Hi,

Is there any way to fill 10 comboboxes from one source and get adjascent cells data from sheet in two textboxes.

herewith a sample to clarify what i need


thanks in advance,

[LeoTaxi]

It is possible but the meaning is not clear to me


Kind regards
Leo

[djemy1975*]

Thank you .It is exactly what I need.What want to do is to make 2 invoicing forms :

-One for simple invoice with one item and the other is for many items invoice and your help would be highly appreciated

your code worked perfectly,but want to prevent entering duplicate invoice Number in column .How would it possible?

THANK YOU IN ADVANCE

[mikerickson]

deleted post

[mikerickson]

This can be done with a custom class.
Add a Class module and change its name to clsSomeComboBox and put this code in it.

Option Explicit
' in clsSomeComboBox

Public WithEvents cbxItem As MSForms.ComboBox
Public WithEvents txtUnite As MSForms.TextBox
Public WithEvents txtPrix As MSForms.TextBox

Event Change()

Private Sub cbxItem_Change()
    Set UFParent.ChangedComboBox = Me
    RaiseEvent Change
End Sub

Function UFParent() As Object
    Set UFParent = cbxItem
    On Error Resume Next
    Do
        Set UFParent = UFParent.Parent
    Loop Until Err
    On Error GoTo 0
End Function

Then put this code in the user form's code module. Note that the Art1_Change event has been removed, substituted by the ChangedComboBox_Change event.

Option Explicit
' in userform's code module

Dim CommonComboboxes As Collection
Public WithEvents ChangedComboBox As clsSomeComboBox

Private Sub ChangedComboBox_Change()
    With Me.ChangedComboBox
        If .cbxItem.ListIndex <> -1 Then
            .txtUnite.Value = .cbxItem.List(.cbxItem.ListIndex, 1)
            .txtPrix.Value = .cbxItem.List(.cbxItem.ListIndex, 2)
            .cbxItem.BackColor = &H9FFFE0
            .txtUnite.BackColor = &H9FFFE0
            .txtPrix.BackColor = &H9FFFE0
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    Dim newCommonCBX As clsSomeComboBox
    Dim ComboBoxes As Variant
    Dim UniteBoxes As Variant
    Dim PrixBoxes As Variant
    
    Dim f As Worksheet, ListeNivDossier As Variant
    Dim i As Long
    Set f = Sheets("Services") 'Définit l'onglet "Services"
    
    Rem create instance of new class for each combobox
    Set CommonComboboxes = New Collection
    ComboBoxes = Array(Art1, Item2, Item3, Item4, Item5, Item6, Item7, Item8, Item9, Item10)
    UniteBoxes = Array(Unit1, Qté2, Qté3, Qté4, Qté5, Qté6, Qté7, Qté8, Qté9, Qté10)
    PrixBoxes = Array(Price1, Price2, TextBox8, TextBox11, TextBox14, TextBox17, TextBox20, TextBox23, TextBox26, TextBox29)
    
    For i = LBound(ComboBoxes) To UBound(ComboBoxes)
        Set newCommonCBX = New clsSomeComboBox
            With newCommonCBX
                Set .cbxItem = ComboBoxes(i)
                Set .txtPrix = UniteBoxes(i)
                Set .txtUnite = PrixBoxes(i)
            End With
        CommonComboboxes.Add Item:=newCommonCBX
    Next i
    
    Rem define data range
    ListeNivDossier = Range(f.[A2], f.[A65000].End(xlUp)).Resize(, 3).Value
    
    Rem set properties and list of each combobox
    For Each newCommonCBX In CommonComboboxes
        With newCommonCBX.cbxItem
            .ColumnCount = 3
            .ColumnWidths = ";0;0"
            .List = ListeNivDossier
        End With
    Next newCommonCBX

    Set newCommonCBX = Nothing
End Sub

[djemy1975*]

Good morning,

First of all,thank you very much for your replies and suggestions

I have added a new form in which i could prevent duplicate number of invoice in column B ,but i could not do so in the other form (form5) could someone hel me to aply the same on the last one (InvN).

Herewith my file modded.

thanks in advance,

[djemy1975*]

This can be done with a custom class.
Add a Class module and change its name to clsSomeComboBox and put this code in it.

Option Explicit
' in clsSomeComboBox

Public WithEvents cbxItem As MSForms.ComboBox
Public WithEvents txtUnite As MSForms.TextBox
Public WithEvents txtPrix As MSForms.TextBox

Event Change()

Private Sub cbxItem_Change()
    Set UFParent.ChangedComboBox = Me
    RaiseEvent Change
End Sub

Function UFParent() As Object
    Set UFParent = cbxItem
    On Error Resume Next
    Do
        Set UFParent = UFParent.Parent
    Loop Until Err
    On Error GoTo 0
End Function

Then put this code in the user form's code module. Note that the Art1_Change event has been removed, substituted by the ChangedComboBox_Change event.

Option Explicit
' in userform's code module

Dim CommonComboboxes As Collection
Public WithEvents ChangedComboBox As clsSomeComboBox

Private Sub ChangedComboBox_Change()
    With Me.ChangedComboBox
        If .cbxItem.ListIndex <> -1 Then
            .txtUnite.Value = .cbxItem.List(.cbxItem.ListIndex, 1)
            .txtPrix.Value = .cbxItem.List(.cbxItem.ListIndex, 2)
            .cbxItem.BackColor = &H9FFFE0
            .txtUnite.BackColor = &H9FFFE0
            .txtPrix.BackColor = &H9FFFE0
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    Dim newCommonCBX As clsSomeComboBox
    Dim ComboBoxes As Variant
    Dim UniteBoxes As Variant
    Dim PrixBoxes As Variant
    
    Dim f As Worksheet, ListeNivDossier As Variant
    Dim i As Long
    Set f = Sheets("Services") 'Définit l'onglet "Services"
    
    Rem create instance of new class for each combobox
    Set CommonComboboxes = New Collection
    ComboBoxes = Array(Art1, Item2, Item3, Item4, Item5, Item6, Item7, Item8, Item9, Item10)
    UniteBoxes = Array(Unit1, Qté2, Qté3, Qté4, Qté5, Qté6, Qté7, Qté8, Qté9, Qté10)
    PrixBoxes = Array(Price1, Price2, TextBox8, TextBox11, TextBox14, TextBox17, TextBox20, TextBox23, TextBox26, TextBox29)
    
    For i = LBound(ComboBoxes) To UBound(ComboBoxes)
        Set newCommonCBX = New clsSomeComboBox
            With newCommonCBX
                Set .cbxItem = ComboBoxes(i)
                Set .txtPrix = UniteBoxes(i)
                Set .txtUnite = PrixBoxes(i)
            End With
        CommonComboboxes.Add Item:=newCommonCBX
    Next i
    
    Rem define data range
    ListeNivDossier = Range(f.[A2], f.[A65000].End(xlUp)).Resize(, 3).Value
    
    Rem set properties and list of each combobox
    For Each newCommonCBX In CommonComboboxes
        With newCommonCBX.cbxItem
            .ColumnCount = 3
            .ColumnWidths = ";0;0"
            .List = ListeNivDossier
        End With
    Next newCommonCBX

    Set newCommonCBX = Nothing
End Sub

I have tested your code ,but it gives the following error:Compile error-variable not defined.jpgCompile error-variable not defined.jpg

[djemy1975*]

how to custom controls format to: Format(Qté1.Value, "#,##0.00")

[mikerickson]

Formatting textboxes isn't easy, but your format isn't one of the hard ones.
I'll look at that when I get off work this afternoon.

[mikerickson]

By the way, do you want to allow the user to enter negative numbers or is the entry to be either 0 or positive?

[djemy1975*]

the entry SHOULD be either 0 or positive

[mikerickson]

That screen shot is too small for me to read.
What does the error box say and what is the line that is highlighted?

[djemy1975*]

"Compile error:
Variable not defined"------------with ComboBoxes = Array(Art1, Item2, Item3, Item4, Item5, Item6, Item7, Item8, Item9, Item10) and UniteBoxes = Array(Unit1 highlighted

[mikerickson]

Those should be the names of the combo boxes and their linked text boxes.
I took them from the user form in post #3. If they have been changed in subsequent versions, they should be changed to match the new naming.

[djemy1975*]

herewith my file so far and thanks in advance

[djemy1975*]

Herewith the progress I have made so far