Private Sub UserForm_Initialize()
'
Dim wb As Workbook, sh As Worksheet
Set wb = Workbooks("Logistics_Cost_TM_Input.xls")
Set sh = Sheets("Part_Family")

wb.Names.Add Name:="Part_Family_Description", _
RefersTo:="='" & sh.Name & "'!" & Range(sh.Range("D3"),
sh.Range("D65536").End(xlUp)).Address


cmb_PrtFam.RowSource = "Part_Family!Part_Family_Description"
End Sub





--
__________________________________
HTH

Bob

"Les" wrote in message
...
Hi all,

I am using the code below to try and resize the range in case a record has
been added to the bottom of this range. I am not sure if the "Old" range
must
first be deleted before adding it again. I get a "Run-Time error '13':
Type
mismatch" message when i run it at the point mentioned below.

Any help much appreciated.


Private Sub UserForm_Initialize()
'
Dim wb As Workbook, sh As Worksheet
Set wb = Workbooks("Logistics_Cost_TM_Input.xls")
Set sh = Sheets("Part_Family")

'Application.Workbooks(wb).Names("Part_Family_Desc ription").Delete<= ??

'<<== ERROR MESSAGE AT THIS POINT ======
Workbooks(wb).Names.Add Name:="Part_Family_Description", _
RefersTo:=Workbooks(wb).Sheets("sh").Range("D3",
Range("D65536").End(xlUp))

cmb_PrtFam.RowSource = "Part_Family!Part_Family_Description"
End Sub

--
Les