G
Guest
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
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