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
VBA Code in a module called Macrotest
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
VBA Code:
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
VBA Code:
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