Combobox : Set a Rowsource when an option button = TRUE

[Silecya]

Hello everyone,

I'm new on MrExcel and I try to code in VBA the following case but something went wrong and I can't find where my error is. I have an intermediate level in VBA and have already research similar cases before asking this question.

The IDEA is the following :
- Depending on the user choice on the option button 1 & 2 and 3 we will affect for a combobox a row source that is store in another sheet in the same document.
- The format of the row source that will be displayed is text (i.e last name for option button 1, 1rst name for option button 2, 2nd name for option button 3)
- When the user select a value in the combo box, we make a research (Vlookup) in a database to find his mail adress for example
- Then we copy all the records of the vlookup lign in another sheet for a futher macro


I have already started something by myself with other subjects that I found on this forum however I need to learn more. That why i request your help because I want to learn from my mistakes.

Thanks a lot in advance for your help and the time that you've take


VBA in the Userform called UDFTest

Option Explicit 'This is VBA for in a USERFORM called UDFTest

Private Sub ComboBox1_AfterUpdate()
    Call Vlookupresultofcombobox1 'Please go in Macrotest
End Sub


Private Sub OptionButton1_Click()
    Sheets("_ADMINISTRATOR").Range("A1") = 1
    Call SelectCaseMacro
    
End Sub

Private Sub OptionButton2_Click()
    Sheets("_ADMINISTRATOR").Range("A1") = 2
    Call SelectCaseMacro
End Sub

Private Sub OptionButton3_Click()
    Sheets("_ADMINISTRATOR").Range("A1") = 3
    Call SelectCaseMacro
End Sub

VBA Code in a module called Macrotest

Option Explicit 'This is a module called Macrotest

Private Sub SelectCaseMacro()

Dim AdministratorRange1Result As Integer

AdministratorRange1Result = Sheets("_ADMINISTRATOR").Range("A1").Value

    Select Case AdministratorRange1Result
    Case OptionButton1 = 1 'OptionButton 1 is the LastName
             Sheets("Database").Shapes(ComboBox1).RowSource = Range("_Last Name")
             
    Case OptionButton2 = 2 'OptionButton 2 is the 1rst name
            Sheets("Database").Shapes(ComboBox1).RowSource = Range("_1rst Name")

    Case OptionButton3 = 3 'OptionButton 3 is the 2nd name
            Sheets("Database").Shapes(ComboBox1).RowSource = Range("_1rst Name")
           
    Case Else
    MsgBox "Please select a case"

End Select

    Call Vlookupresultofcombobox1 'See below

End Sub

Function Vlookupresultofcombobox1()

Dim ComboBox1 As Variant
    ComboBox1 = UFTest.ComboBox1.Value
    
    'Something went wrong below this message i can't find what
    application.worksheetfunction.vlookup(UFTest.ComboBox1.Value,Sheets("Database").Range("A:D"),5,0)
   
   'I'm not sure about the section below I think that something miss but i don't know how to write it in VBA
   'I would like to copy the all the informations from the vlookup result in a sheet in order to use it in another macro
   
   Call Copypasteresultat 'See below
End Function

Private Sub Copypasteresultat()
'The aim is to copy past the lign that the previous function as find in a sheet in order to past it in a mail for and automate sending

Dim AdministratorRange1Result As Integer

AdministratorRange1Result = Sheets("_ADMINISTRATOR").Range("A1").Value
   
   Select Case AdministratorRange1Result
   
        Case AdministratorRange1Result = 1 '1 is the FullName
                application.worksheetfunction.vlookup(UFTest.ComboBox1.Value,Sheets("Records to copy").Range("A:G"),5,0)
                Sheets("Result of copy").Range("A1:").Select
                ActiveCell.PasteSpecial Paste:=xlPasteValues
                
        Case AdministratorRange1Result = 2 '2 is the 1rst name
                application.worksheetfunction.vlookup(UFTest.ComboBox1.Value,Sheets("Records to copy").Range("B:G"),5,0)
                Sheets("Result of copy").Range("A1:").Select
                ActiveCell.PasteSpecial Paste:=xlPasteValues
                
        Case AdministratorRange1Result = 3 '3 is the 2nd name
                application.worksheetfunction.vlookup(UFTest.ComboBox1.Value,Sheets("Records to copy").Range("C:G"),5,0)
                Sheets("Result of copy").Range("A1:").Select
                ActiveCell.PasteSpecial Paste:=xlPasteValues

 

[Fluff]

Hi & welcome to MrExcel.
What sheet are your named ranges on?
Also what are the names of those ranges as "_Last Name" is not a valid range.

 

[GWteB]

At first glance this does not seem to be correct.

application.worksheetfunction.vlookup(UFTest.ComboBox1.Value,Sheets("Database").Range("A:D"),5,0)

The LookIn index range consists of just four columns, so a fifth column within that index does not exist. If what you're looking for is located in column "D:D" then use

application.worksheetfunction.vlookup(UFTest.ComboBox1.Value,Sheets("Database").Range("A:D"),4,0)

 

[Silecya]

Hi & welcome to MrExcel.
What sheet are your named ranges on?
Also what are the names of those ranges as "_Last Name" is not a valid range.

Hi Fluff and thanks you for your quick answer

The sheet that is concerned by all named range that you can see in the code is : Database

In detail : (You will find a screen save if needed)
1rst_Name referes to range =Database!A:A | 2nd_Name =Database!B:B | Last_Name : =Database!C:C

Let me know if you need more information

 

[Silecya]

At first glance this does not seem to be correct.

application.worksheetfunction.vlookup(UFTest.ComboBox1.Value,Sheets("Database").Range("A:D"),5,0)

The LookIn index range consists of just four columns, so a fifth column within that index does not exist. If what you're looking for is located in column "D:D" then use

application.worksheetfunction.vlookup(UFTest.ComboBox1.Value,Sheets("Database").Range("A:D"),4,0)

Hi GWteB

That sounds logical because it still a Excel function. That is a mistake from me i was a bit confuse by VBA code and i forgot about the basics.

However VBA still flag up a synthax error at this point.

I made a bit of research and I would like to know if that comming from the fact that i want to make a research which depends on a combobox value that is executed before that macro

Thanks in advance for the time that you take

Regards

 

[Fluff]

Rather than calling a separate macro each time, you can just use

Private Sub OptionButton1_Click()
   Me.ComboBox1.List = Sheets("Database").Range("Last_Name").Value
End Sub

and similar for the other option buttons.