integrating a userform with combobox into a macro
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
|