How can I the replace the inputbox by a userform with combobox?
I succeeded in creating a form with combobox that fills itself as it should
do, but I do not succeed in integrating the form into my macro so that it
starts the search. See the codes below. What do you suggest?
....
Workbooks.Open Filename:="C:\WOPST\Curriculumstellingen.xls"

Dim Name As String
Name = Application.InputBox(prompt:="Select name. Then 'OK'." , Type:=3)
Debug.Print Name

If Name = "" Or Name = "0" Then
MsgBox ("You should select the right name. This macro stosp here.")
ActiveWorkbook.Close
Sheets("Curstellingen").Select
ActiveSheet.Delete
Sheets("Datablad").Select
End
Exit Sub
End If

With Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End With
ActiveCell.CurrentRegion.Select
Selection.Offset(0, 1).Resize(4, 1).Select
Selection.Copy
€¦

Private Sub ComboBox1_Change()

ActiveSheet.Columns("A:A").Select

ComboBox1.Clear

Dim column As String
Dim i
i = 1
Do Until Cells(i, 1) = ""
ComboBox1.AddItem Cells(i, 1)
i = i + 1
Loop

End Sub

Hi

Not 100% sure what all you are doing here but you could do the
following

Add this line of code to the workbook open event

Private Sub Workbook_Open()
UserForm1.Show vbModal
End Sub


Add this code to the userform


Private Sub ComboBox1_Change()
Dim Name As String
Name = ComboBox1.Value
If Name = "" Or Name = "0" Then
MsgBox ("You should select the right name. This macro stosp here.")
ActiveWorkbook.Close
Sheets("Curstellingen").Select
ActiveSheet.Delete
Sheets("Datablad").Select
End
Exit Sub
End If


With Cells.Find(What:=Name, After:=ActiveCell, LookAt:=xlPart)
ActiveCell.CurrentRegion.Select
Selection.Offset(0, 1).Resize(4, 1).Select
Selection.Copy
End With

End Sub

Private Sub UserForm_Initialize()
ActiveSheet.Columns("A:A").Select
ComboBox1.Clear
Dim column As String
Dim i
i = 1
Do Until Cells(i, 1) = ""
ComboBox1.AddItem Cells(i, 1)
i = i + 1
Loop
End Sub

This should load your userform when you open the book and then you can
run your code from there...

S

Hi Incidental and other colleagues
Your advice was of great help and the code works. But in order to go back to
the main procedure after having selected an item in the combobox, it is
necessary to close the userform by clicking the "X", because else the
combobox doesn't "clear".
What do I have to bring into the code to avoid that second click? I've tried
several possibilities including "UserForm1.Hide" in "Private Sub
Combobox1_change()" just before "End sub". In that case I'don't have to click
a second time, but the combobox isn't cleared. Thank you for your reaction.




New code:




"Incidental" wrote:

Hi

Not 100% sure what all you are doing here but you could do the
following

Add this line of code to the workbook open event

Private Sub Workbook_Open()
UserForm1.Show vbModal
End Sub


Add this code to the userform


Private Sub ComboBox1_Change()
Dim Name As String
Name = ComboBox1.Value
If Name = "" Or Name = "0" Then
MsgBox ("You should select the right name. This macro stosp here.")
ActiveWorkbook.Close
Sheets("Curstellingen").Select
ActiveSheet.Delete
Sheets("Datablad").Select
End
Exit Sub
End If


With Cells.Find(What:=Name, After:=ActiveCell, LookAt:=xlPart)
ActiveCell.CurrentRegion.Select
Selection.Offset(0, 1).Resize(4, 1).Select
Selection.Copy
End With

End Sub

Private Sub UserForm_Initialize()
ActiveSheet.Columns("A:A").Select
ComboBox1.Clear
Dim column As String
Dim i
i = 1
Do Until Cells(i, 1) = ""
ComboBox1.AddItem Cells(i, 1)
i = i + 1
Loop
End Sub

This should load your userform when you open the book and then you can
run your code from there...

S