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

--
Message posted from http://www.ExcelForum.com

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)

"ice_cool " wrote in message
...
im having trouble finding the right VB code to enter to allow me to make
the spreadsheet automatically update the input range for a combo box on
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.


---
Message posted from http://www.ExcelForum.com/

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.

--
Regards,
Tom Ogilvy

"ice_cool " wrote in message
...
im having trouble finding the right VB code to enter to allow me to make
the spreadsheet automatically update the input range for a combo box on
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.


---
Message posted from http://www.ExcelForum.com/

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" wrote in message
...
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)

"ice_cool " wrote in message
...
im having trouble finding the right VB code to enter to allow me to make
the spreadsheet automatically update the input range for a combo box on
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.


---
Message posted from http://www.ExcelForum.com/