Hi,

I have a userform with two comboboxes.
the first one lists the customer name
the second one lists the customer advisor

Depending on which combobox the user selects, i want to use the value the
user selects to pick the customer form the data sheet.
I managed to do it for one combobox using a "retrieve" button called
"ophalen" like this;

Private Sub but_ophalen_Click()
Dim klantkeuze As String
Dim naamtotaal As String
klantkeuze = zoekwg.Value
Set klantmatrix = Worksheets("datadga").Range("B2:b500").Find(klantkeuze)
txt_naamwg = Worksheets("datadga").Cells(klantmatrix.Row,
klantmatrix.Column)
etc..
end sub

where
zoekwg = a combobox

My question,
How can i get my form to use either combobox 1 or 2 depending on which
combobox the user chooses ?

I can only imagine a second button for combobox 2 but there must be a more
elegant solution i think?
Please help me.
Pierre

Hi Pierre

Private Sub but_ophalen_Click()
Dim klantkeuze As String
Dim naamtotaal As String
if not isempty(zoekwg.Value) then
'This Combobox was selected

klantkeuze = zoekwg.Value
Set klantmatrix =
Worksheets("datadga").Range("B2:b500").Find(klantkeuze)
txt_naamwg = Worksheets("datadga").Cells(klantmatrix.Row,
klantmatrix.Column)
etc..
elseif not isempty(combobox2.value) ' Fit to suit combobox name
'Combobox2 was selected
' Your code for Combobox2
end if
end sub

Regards, Per

"Pierre" <(E-Mail Removed)> skrev i en meddelelse
news:4766558e$0$85793$(E-Mail Removed)...
> Hi,
>
> I have a userform with two comboboxes.
> the first one lists the customer name
> the second one lists the customer advisor
>
> Depending on which combobox the user selects, i want to use the value the
> user selects to pick the customer form the data sheet.
> I managed to do it for one combobox using a "retrieve" button called
> "ophalen" like this;
>
> Private Sub but_ophalen_Click()
> Dim klantkeuze As String
> Dim naamtotaal As String
> klantkeuze = zoekwg.Value
> Set klantmatrix = Worksheets("datadga").Range("B2:b500").Find(klantkeuze)
> txt_naamwg = Worksheets("datadga").Cells(klantmatrix.Row,
> klantmatrix.Column)
> etc..
> end sub
>
> where
> zoekwg = a combobox
>
> My question,
> How can i get my form to use either combobox 1 or 2 depending on which
> combobox the user chooses ?
>
> I can only imagine a second button for combobox 2 but there must be a more
> elegant solution i think?
> Please help me.
> Pierre
>
>

Hi Per,

THANK YOU !

I first got an error message but i quickly saw that you forgot a Then
statement.
Now it works fine !

Thanks again for your help Per.
Pierre

"Per Jessen" <(E-Mail Removed)> schreef in bericht
news:eMwCY$(E-Mail Removed)...
> Hi Pierre
>
> Private Sub but_ophalen_Click()
> Dim klantkeuze As String
> Dim naamtotaal As String
> if not isempty(zoekwg.Value) then
> 'This Combobox was selected
>
> klantkeuze = zoekwg.Value
> Set klantmatrix =
> Worksheets("datadga").Range("B2:b500").Find(klantkeuze)
> txt_naamwg = Worksheets("datadga").Cells(klantmatrix.Row,
> klantmatrix.Column)
> etc..
> elseif not isempty(combobox2.value) ' Fit to suit combobox name
> 'Combobox2 was selected
> ' Your code for Combobox2
> end if
> end sub
>
> Regards, Per
>
> "Pierre" <(E-Mail Removed)> skrev i en meddelelse
> news:4766558e$0$85793$(E-Mail Removed)...
>> Hi,
>>
>> I have a userform with two comboboxes.
>> the first one lists the customer name
>> the second one lists the customer advisor
>>
>> Depending on which combobox the user selects, i want to use the value the
>> user selects to pick the customer form the data sheet.
>> I managed to do it for one combobox using a "retrieve" button called
>> "ophalen" like this;
>>
>> Private Sub but_ophalen_Click()
>> Dim klantkeuze As String
>> Dim naamtotaal As String
>> klantkeuze = zoekwg.Value
>> Set klantmatrix = Worksheets("datadga").Range("B2:b500").Find(klantkeuze)
>> txt_naamwg = Worksheets("datadga").Cells(klantmatrix.Row,
>> klantmatrix.Column)
>> etc..
>> end sub
>>
>> where
>> zoekwg = a combobox
>>
>> My question,
>> How can i get my form to use either combobox 1 or 2 depending on which
>> combobox the user chooses ?
>>
>> I can only imagine a second button for combobox 2 but there must be a
>> more elegant solution i think?
>> Please help me.
>> Pierre
>>
>>
>
>

Hi Pierre

Thanks again for your reply.

Good you could find the missing Then, i didn't test the code :-(

//Per
"Pierre" <(E-Mail Removed)> skrev i en meddelelse
news:47666385$0$85794$(E-Mail Removed)...
> Hi Per,
>
> THANK YOU !
>
> I first got an error message but i quickly saw that you forgot a Then
> statement.
> Now it works fine !
>
> Thanks again for your help Per.
> Pierre
>
> "Per Jessen" <(E-Mail Removed)> schreef in bericht
> news:eMwCY$(E-Mail Removed)...
>> Hi Pierre
>>
>> Private Sub but_ophalen_Click()
>> Dim klantkeuze As String
>> Dim naamtotaal As String
>> if not isempty(zoekwg.Value) then
>> 'This Combobox was selected
>>
>> klantkeuze = zoekwg.Value
>> Set klantmatrix =
>> Worksheets("datadga").Range("B2:b500").Find(klantkeuze)
>> txt_naamwg = Worksheets("datadga").Cells(klantmatrix.Row,
>> klantmatrix.Column)
>> etc..
>> elseif not isempty(combobox2.value) ' Fit to suit combobox name
>> 'Combobox2 was selected
>> ' Your code for Combobox2
>> end if
>> end sub
>>
>> Regards, Per
>>
>> "Pierre" <(E-Mail Removed)> skrev i en meddelelse
>> news:4766558e$0$85793$(E-Mail Removed)...
>>> Hi,
>>>
>>> I have a userform with two comboboxes.
>>> the first one lists the customer name
>>> the second one lists the customer advisor
>>>
>>> Depending on which combobox the user selects, i want to use the value
>>> the user selects to pick the customer form the data sheet.
>>> I managed to do it for one combobox using a "retrieve" button called
>>> "ophalen" like this;
>>>
>>> Private Sub but_ophalen_Click()
>>> Dim klantkeuze As String
>>> Dim naamtotaal As String
>>> klantkeuze = zoekwg.Value
>>> Set klantmatrix =
>>> Worksheets("datadga").Range("B2:b500").Find(klantkeuze)
>>> txt_naamwg = Worksheets("datadga").Cells(klantmatrix.Row,
>>> klantmatrix.Column)
>>> etc..
>>> end sub
>>>
>>> where
>>> zoekwg = a combobox
>>>
>>> My question,
>>> How can i get my form to use either combobox 1 or 2 depending on which
>>> combobox the user chooses ?
>>>
>>> I can only imagine a second button for combobox 2 but there must be a
>>> more elegant solution i think?
>>> Please help me.
>>> Pierre
>>>
>>>
>>
>>
>
>