worksheet_change event with a combo box

I

ice_cool

im having trouble finding the right VB code to enter to allow me to mak
the spreadsheet automatically update the input range for a combo box o
one page when new data is added into a table on another page.

if anyone can give me any tips at all on this i would be very grateful
 
B

Bob Phillips

Here's some worksheet event code that will trap a change in column A of
sheet 2, and re-set the combobox range on sheet 1

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
On Error GoTo ws_exit
If Target.Column = 1 Then
Worksheets("Sheet1").ComboBox1.ListFillRange = _
Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row,
1).Address
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom Ogilvy

Three different types of comboboxes -
Control Toolbox Toolbar
Forms Toolbar
Data=>Validation using the list option.

Which are you using and how do you populate it?

More details, better answers.
 
B

Bob Phillips

oops, sorry, forgot a bit.

Try this instead

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
On Error GoTo ws_exit
If Target.Column = 1 Then
Worksheets("Sheet1").ComboBox1.ListFillRange = _
Me.Name & "!" & _
Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row,
1).Address
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob Phillips said:
Here's some worksheet event code that will trap a change in column A of
sheet 2, and re-set the combobox range on sheet 1

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
On Error GoTo ws_exit
If Target.Column = 1 Then
Worksheets("Sheet1").ComboBox1.ListFillRange = _
Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row,
1).Address
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Top