Prev: Listbox headers (Permission denied)
Next: record sheet row order, then restore to that point
From: basstbone on 6 Mar 2010 20:56
Is there a way to refresh the combo box after creating a new record in the
worksheet?

I have a simple form that enters Names & Sales units to the worksheet...I
also have a combo box on the same form whose row source is the names.
After I add the record by using the on click event, how do I refresh the
combo box at the same time?
From: Dave Peterson on 6 Mar 2010 21:45
How about clearing the rowsource and reassigning it to the expanded (and
sorted???) range.

Option Explicit
Private Sub CommandButton1_Click()

Dim DestCell As Range

If Me.TextBox1.Value = "" Then
Beep
Else
With Worksheets("Sheet1")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
DestCell.Value = Me.TextBox1.Value

Me.TextBox1.Value = ""

Me.ComboBox1.RowSource = ""
Me.ComboBox1.RowSource _
= .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) _
.Address(external:=True)
End With
End If

End Sub


basstbone wrote:
>
> Is there a way to refresh the combo box after creating a new record in the
> worksheet?
>
> I have a simple form that enters Names & Sales units to the worksheet...I
> also have a combo box on the same form whose row source is the names.
> After I add the record by using the on click event, how do I refresh the
> combo box at the same time?

--

Dave Peterson
 | 
Pages: 1
Prev: Listbox headers (Permission denied)
Next: record sheet row order, then restore to that point