run time error '5'  
Author Message
watt





PostPosted: Thu Aug 25 20:18:59 CDT 2005 Top

Excel Programming >> run time error '5'

I am new to VBA programming and am having the following problem. I don't know
how much of the following info is relevant or what other info is required to
answer, so please bear with me and excuse my 'long-windedness'.

I have a multi-sheet workbook ("Orders.xls") that is VBA opened and filled
by another workbook ("FillOrders.xls"). FillOrders.xls also dynamically
creates named ranges in Orders.xls that constitutes the data on each the
sheet in Orders.xls. For example, the range 'HeadersTable' is comprised of
that area on the sheet 'Headers' that actually contains data. I do that as
follows:

**************************************
(Not in this workbook)
Sub BuildHeadersTable()

Dim HeadersTable As Range

With Workbooks("Orders.xls").Worksheets("Headers"). _
Range("A1").CurrentRegion
Set HeadersTable = .Offset(1, 0) _
.Resize(.Rows.Count - 1, _
.Columns.Count)
End With

'Name the table
HeadersTable.Name = "HeadersTable"

End Sub
*********************************

I am now creating a user form (frmDisplayOrders) in Orders.xls to show
information for an order# selected in a listbox (lboOrder) that pulled from
various sheets.

In the 'General' section of Module 1 of Orders.xls I have the following:

Option Explicit

Public HeadersTable As Range
Public ShipToTable As Range
Public TalliesTable As Range
etc.. for all the named ranges

In the Workbook_Open subprocedure i have the following:

With Workbooks("OrdersWork.xls")
Set HeadersTable = .Worksheets("Headers").Range("HeadersTable")
Set ShipToTable = .Worksheets("ShipTo").Range("ShipToTable")
etc.... for all the tables
End With

The subprocedure lboOrders_Change starts as follows:
*********************************
Private Sub lboOrder_Change()

Dim CustomerID As Long
Dim Code As String
Dim OrderReady As String


With Application.WorksheetFunction
'Customer#
CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
**********************
If I run frmDisplayOrders.Show it works just fine. I stop it using the
form's default cancel 'X'. I can restart it and run it multiple times. Then I
will re-run frnDisplayOrder, select a value in lboOrder, and it stops on the
above line (CustomerID = .Vlookup....) with 'Run time error '5': Invalid
procedure or argument. Debug shows:
CustID = 0 (expected)
lboOrder.value = 312628 (correct)
HeadersTable = nothing (I have no idea if this is correct)

In order to get the form to work correctly, I thought that I had to close
Orders.xlsa and re-open, but discovered if I re-ran the Workbook_Open
subprocedure.

I did a search through all the code for 'HeadersTable' and it was only found
where shown above i.e. The general section of the Module 1, Worbook_Open, and
lboOrders_Change.

(The help for 'runtime error 5' is especially helpful. I have never
understood why these error messages are not more explicit. The cause of the
problem is known, why make one guess what it is. Subprocedure??? variable???
which variable???)

TIA

Tim Kredlo

Excel40  
 
 
Jim





PostPosted: Thu Aug 25 20:18:59 CDT 2005 Top

Excel Programming >> run time error '5' Tim,

At first look, I believe
CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
should be
CustomerID = .VLookup(lboOrder.Value,Range(" HeadersTable"), 3, 0)

Regards,
Jim Cone
San Francisco, USA


"Tim Kredlo"



I am new to VBA programming and am having the following problem. I don't know
how much of the following info is relevant or what other info is required to
answer, so please bear with me and excuse my 'long-windedness'.

I have a multi-sheet workbook ("Orders.xls") that is VBA opened and filled
by another workbook ("FillOrders.xls"). FillOrders.xls also dynamically
creates named ranges in Orders.xls that constitutes the data on each the
sheet in Orders.xls. For example, the range 'HeadersTable' is comprised of
that area on the sheet 'Headers' that actually contains data. I do that as
follows:

**************************************
(Not in this workbook)
Sub BuildHeadersTable()

Dim HeadersTable As Range

With Workbooks("Orders.xls").Worksheets("Headers"). _
Range("A1").CurrentRegion
Set HeadersTable = .Offset(1, 0) _
.Resize(.Rows.Count - 1, _
.Columns.Count)
End With

'Name the table
HeadersTable.Name = "HeadersTable"

End Sub
*********************************

I am now creating a user form (frmDisplayOrders) in Orders.xls to show
information for an order# selected in a listbox (lboOrder) that pulled from
various sheets.

In the 'General' section of Module 1 of Orders.xls I have the following:

Option Explicit

Public HeadersTable As Range
Public ShipToTable As Range
Public TalliesTable As Range
etc.. for all the named ranges

In the Workbook_Open subprocedure i have the following:

With Workbooks("OrdersWork.xls")
Set HeadersTable = .Worksheets("Headers").Range("HeadersTable")
Set ShipToTable = .Worksheets("ShipTo").Range("ShipToTable")
etc.... for all the tables
End With

The subprocedure lboOrders_Change starts as follows:
*********************************
Private Sub lboOrder_Change()

Dim CustomerID As Long
Dim Code As String
Dim OrderReady As String


With Application.WorksheetFunction
'Customer#
CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
**********************
If I run frmDisplayOrders.Show it works just fine. I stop it using the
form's default cancel 'X'. I can restart it and run it multiple times. Then I
will re-run frnDisplayOrder, select a value in lboOrder, and it stops on the
above line (CustomerID = .Vlookup....) with 'Run time error '5': Invalid
procedure or argument. Debug shows:
CustID = 0 (expected)
lboOrder.value = 312628 (correct)
HeadersTable = nothing (I have no idea if this is correct)

In order to get the form to work correctly, I thought that I had to close
Orders.xlsa and re-open, but discovered if I re-ran the Workbook_Open
subprocedure.

I did a search through all the code for 'HeadersTable' and it was only found
where shown above i.e. The general section of the Module 1, Worbook_Open, and
lboOrders_Change.

(The help for 'runtime error 5' is especially helpful. I have never
understood why these error messages are not more explicit. The cause of the
problem is known, why make one guess what it is. Subprocedure??? variable???
which variable???)

TIA

Tim Kredlo



 
 
TimKredlo





PostPosted: Fri Aug 26 11:45:04 CDT 2005 Top

Excel Programming >> run time error '5' Jim,

Since CustID= .VLookup(lboOrder.Value, HeadersTable, 3, 0) works great until
'all of a sudden' it doesn't, I don't think that is the issue.

Since this is a 'work in process' and I am modifying the form, testing,
modifying, testing, etc., I think that the program is somehow 'losing'
whatever the result of the WorkBook_Open sub procedure statement "Set
HeadersTable = .Worksheets("Headers").Range("HeadersTable")" is. If I rerun
the Workbook_Open sub procedure everything works OK.

Any other ideas?

Tim Kredlo



> Tim,
>
> At first look, I believe
> CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
> should be
> CustomerID = .VLookup(lboOrder.Value,Range(" HeadersTable"), 3, 0)
>
> Regards,
> Jim Cone
> San Francisco, USA
>
>
> "Tim Kredlo"



> I am new to VBA programming and am having the following problem. I don't know
> how much of the following info is relevant or what other info is required to
> answer, so please bear with me and excuse my 'long-windedness'.
>
> I have a multi-sheet workbook ("Orders.xls") that is VBA opened and filled
> by another workbook ("FillOrders.xls"). FillOrders.xls also dynamically
> creates named ranges in Orders.xls that constitutes the data on each the
> sheet in Orders.xls. For example, the range 'HeadersTable' is comprised of
> that area on the sheet 'Headers' that actually contains data. I do that as
> follows:
>
> **************************************
> (Not in this workbook)
> Sub BuildHeadersTable()
>
> Dim HeadersTable As Range
>
> With Workbooks("Orders.xls").Worksheets("Headers"). _
> Range("A1").CurrentRegion
> Set HeadersTable = .Offset(1, 0) _
> .Resize(.Rows.Count - 1, _
> .Columns.Count)
> End With
>
> 'Name the table
> HeadersTable.Name = "HeadersTable"
>
> End Sub
> *********************************
>
> I am now creating a user form (frmDisplayOrders) in Orders.xls to show
> information for an order# selected in a listbox (lboOrder) that pulled from
> various sheets.
>
> In the 'General' section of Module 1 of Orders.xls I have the following:
>
> Option Explicit
>
> Public HeadersTable As Range
> Public ShipToTable As Range
> Public TalliesTable As Range
> etc.. for all the named ranges
>
> In the Workbook_Open subprocedure i have the following:
>
> With Workbooks("OrdersWork.xls")
> Set HeadersTable = .Worksheets("Headers").Range("HeadersTable")
> Set ShipToTable = .Worksheets("ShipTo").Range("ShipToTable")
> etc.... for all the tables
> End With
>
> The subprocedure lboOrders_Change starts as follows:
> *********************************
> Private Sub lboOrder_Change()
>
> Dim CustomerID As Long
> Dim Code As String
> Dim OrderReady As String
>
>
> With Application.WorksheetFunction
> 'Customer#
> CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
> **********************
> If I run frmDisplayOrders.Show it works just fine. I stop it using the
> form's default cancel 'X'. I can restart it and run it multiple times. Then I
> will re-run frnDisplayOrder, select a value in lboOrder, and it stops on the
> above line (CustomerID = .Vlookup....) with 'Run time error '5': Invalid
> procedure or argument. Debug shows:
> CustID = 0 (expected)
> lboOrder.value = 312628 (correct)
> HeadersTable = nothing (I have no idea if this is correct)
>
> In order to get the form to work correctly, I thought that I had to close
> Orders.xlsa and re-open, but discovered if I re-ran the Workbook_Open
> subprocedure.
>
> I did a search through all the code for 'HeadersTable' and it was only found
> where shown above i.e. The general section of the Module 1, Worbook_Open, and
> lboOrders_Change.
>
> (The help for 'runtime error 5' is especially helpful. I have never
> understood why these error messages are not more explicit. The cause of the
> problem is known, why make one guess what it is. Subprocedure??? variable???
> which variable???)
>
> TIA
>
> Tim Kredlo
>
>
>
>
 
 
Jim





PostPosted: Fri Aug 26 13:46:54 CDT 2005 Top

Excel Programming >> run time error '5' Tim,

Observations that may or may not help...
You refer to Orders.xls and also to OrdersWork.xls.
You are using the same text to describe the range names and range objects.
The Public range object variables will lose their references and become equal
to "Nothing" when the workbook is closed.
Public variables will sometimes lose their value while the VBE is open but
work ok when the program runs normally.

Regards,
Jim Cone
San Francisco, USA


"Tim Kredlo"




Jim,
Since CustID= .VLookup(lboOrder.Value, HeadersTable, 3, 0) works great until
'all of a sudden' it doesn't, I don't think that is the issue.
Since this is a 'work in process' and I am modifying the form, testing,
modifying, testing, etc., I think that the program is somehow 'losing'
whatever the result of the WorkBook_Open sub procedure statement "Set
HeadersTable = .Worksheets("Headers").Range("HeadersTable")" is. If I rerun
the Workbook_Open sub procedure everything works OK.

Any other ideas?

Tim Kredlo

 
 
TimKredlo





PostPosted: Fri Aug 26 14:04:01 CDT 2005 Top

Excel Programming >> run time error '5' Jim,

Thanks for the info aboutv 'Public' variables when VBE is open vrs 'running
normally'. I think that is the problem.

(Orders.xls and Orderswork.xls are really 2 copies of the same file. I cut
and pasted code from the 'work' file to create the question but referred to
the file as Orders.xls in my text. Sorry for the confusion. I'm sure that
didn't help)

Again, thank you for your response.

Tim Kredlo



> Tim,
>
> Observations that may or may not help...
> You refer to Orders.xls and also to OrdersWork.xls.
> You are using the same text to describe the range names and range objects.
> The Public range object variables will lose their references and become equal
> to "Nothing" when the workbook is closed.
> Public variables will sometimes lose their value while the VBE is open but
> work ok when the program runs normally.
>
> Regards,
> Jim Cone
> San Francisco, USA
>
>
> "Tim Kredlo"



>
> Jim,
> Since CustID= .VLookup(lboOrder.Value, HeadersTable, 3, 0) works great until
> 'all of a sudden' it doesn't, I don't think that is the issue.
> Since this is a 'work in process' and I am modifying the form, testing,
> modifying, testing, etc., I think that the program is somehow 'losing'
> whatever the result of the WorkBook_Open sub procedure statement "Set
> HeadersTable = .Worksheets("Headers").Range("HeadersTable")" is. If I rerun
> the Workbook_Open sub procedure everything works OK.
>
> Any other ideas?
>
> Tim Kredlo
>
>
 
 
Tom





PostPosted: Fri Aug 26 14:05:42 CDT 2005 Top

Excel Programming >> run time error '5' If you encounter an error and hit the reset button in the vbe, then you will
clear any variables.

--
Regards,
Tom Ogilvy




> Jim,
>
> Since CustID= .VLookup(lboOrder.Value, HeadersTable, 3, 0) works great
until
> 'all of a sudden' it doesn't, I don't think that is the issue.
>
> Since this is a 'work in process' and I am modifying the form, testing,
> modifying, testing, etc., I think that the program is somehow 'losing'
> whatever the result of the WorkBook_Open sub procedure statement "Set
> HeadersTable = .Worksheets("Headers").Range("HeadersTable")" is. If I
rerun
> the Workbook_Open sub procedure everything works OK.
>
> Any other ideas?
>
> Tim Kredlo
>

>
> > Tim,
> >
> > At first look, I believe
> > CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
> > should be
> > CustomerID = .VLookup(lboOrder.Value,Range(" HeadersTable"), 3, 0)
> >
> > Regards,
> > Jim Cone
> > San Francisco, USA
> >
> >
> > "Tim Kredlo"



> > I am new to VBA programming and am having the following problem. I don't
know
> > how much of the following info is relevant or what other info is
required to
> > answer, so please bear with me and excuse my 'long-windedness'.
> >
> > I have a multi-sheet workbook ("Orders.xls") that is VBA opened and
filled
> > by another workbook ("FillOrders.xls"). FillOrders.xls also dynamically
> > creates named ranges in Orders.xls that constitutes the data on each
the
> > sheet in Orders.xls. For example, the range 'HeadersTable' is comprised
of
> > that area on the sheet 'Headers' that actually contains data. I do that
as
> > follows:
> >
> > **************************************
> > (Not in this workbook)
> > Sub BuildHeadersTable()
> >
> > Dim HeadersTable As Range
> >
> > With Workbooks("Orders.xls").Worksheets("Headers"). _
> > Range("A1").CurrentRegion
> > Set HeadersTable = .Offset(1, 0) _
> > .Resize(.Rows.Count - 1, _
> > .Columns.Count)
> > End With
> >
> > 'Name the table
> > HeadersTable.Name = "HeadersTable"
> >
> > End Sub
> > *********************************
> >
> > I am now creating a user form (frmDisplayOrders) in Orders.xls to show
> > information for an order# selected in a listbox (lboOrder) that pulled
from
> > various sheets.
> >
> > In the 'General' section of Module 1 of Orders.xls I have the following:
> >
> > Option Explicit
> >
> > Public HeadersTable As Range
> > Public ShipToTable As Range
> > Public TalliesTable As Range
> > etc.. for all the named ranges
> >
> > In the Workbook_Open subprocedure i have the following:
> >
> > With Workbooks("OrdersWork.xls")
> > Set HeadersTable = .Worksheets("Headers").Range("HeadersTable")
> > Set ShipToTable = .Worksheets("ShipTo").Range("ShipToTable")
> > etc.... for all the tables
> > End With
> >
> > The subprocedure lboOrders_Change starts as follows:
> > *********************************
> > Private Sub lboOrder_Change()
> >
> > Dim CustomerID As Long
> > Dim Code As String
> > Dim OrderReady As String
> >
> >
> > With Application.WorksheetFunction
> > 'Customer#
> > CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
> > **********************
> > If I run frmDisplayOrders.Show it works just fine. I stop it using the
> > form's default cancel 'X'. I can restart it and run it multiple times.
Then I
> > will re-run frnDisplayOrder, select a value in lboOrder, and it stops
on the
> > above line (CustomerID = .Vlookup....) with 'Run time error '5': Invalid
> > procedure or argument. Debug shows:
> > CustID = 0 (expected)
> > lboOrder.value = 312628 (correct)
> > HeadersTable = nothing (I have no idea if this is correct)
> >
> > In order to get the form to work correctly, I thought that I had to
close
> > Orders.xlsa and re-open, but discovered if I re-ran the Workbook_Open
> > subprocedure.
> >
> > I did a search through all the code for 'HeadersTable' and it was only
found
> > where shown above i.e. The general section of the Module 1,
Worbook_Open, and
> > lboOrders_Change.
> >
> > (The help for 'runtime error 5' is especially helpful. I have never
> > understood why these error messages are not more explicit. The cause of
the
> > problem is known, why make one guess what it is. Subprocedure???
variable???
> > which variable???)
> >
> > TIA
> >
> > Tim Kredlo
> >
> >
> >
> >


 
 
TimKredlo





PostPosted: Fri Aug 26 14:55:04 CDT 2005 Top

Excel Programming >> run time error '5' Thanks Tom. I did not know that.

Is clicking on the forms red 'X' do the same thing?




> If you encounter an error and hit the reset button in the vbe, then you will
> clear any variables.
>
> --
> Regards,
> Tom Ogilvy
>
>


> > Jim,
> >
> > Since CustID= .VLookup(lboOrder.Value, HeadersTable, 3, 0) works great
> until
> > 'all of a sudden' it doesn't, I don't think that is the issue.
> >
> > Since this is a 'work in process' and I am modifying the form, testing,
> > modifying, testing, etc., I think that the program is somehow 'losing'
> > whatever the result of the WorkBook_Open sub procedure statement "Set
> > HeadersTable = .Worksheets("Headers").Range("HeadersTable")" is. If I
> rerun
> > the Workbook_Open sub procedure everything works OK.
> >
> > Any other ideas?
> >
> > Tim Kredlo
> >

> >
> > > Tim,
> > >
> > > At first look, I believe
> > > CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
> > > should be
> > > CustomerID = .VLookup(lboOrder.Value,Range(" HeadersTable"), 3, 0)
> > >
> > > Regards,
> > > Jim Cone
> > > San Francisco, USA
> > >
> > >
> > > "Tim Kredlo"



> > > I am new to VBA programming and am having the following problem. I don't
> know
> > > how much of the following info is relevant or what other info is
> required to
> > > answer, so please bear with me and excuse my 'long-windedness'.
> > >
> > > I have a multi-sheet workbook ("Orders.xls") that is VBA opened and
> filled
> > > by another workbook ("FillOrders.xls"). FillOrders.xls also dynamically
> > > creates named ranges in Orders.xls that constitutes the data on each
> the
> > > sheet in Orders.xls. For example, the range 'HeadersTable' is comprised
> of
> > > that area on the sheet 'Headers' that actually contains data. I do that
> as
> > > follows:
> > >
> > > **************************************
> > > (Not in this workbook)
> > > Sub BuildHeadersTable()
> > >
> > > Dim HeadersTable As Range
> > >
> > > With Workbooks("Orders.xls").Worksheets("Headers"). _
> > > Range("A1").CurrentRegion
> > > Set HeadersTable = .Offset(1, 0) _
> > > .Resize(.Rows.Count - 1, _
> > > .Columns.Count)
> > > End With
> > >
> > > 'Name the table
> > > HeadersTable.Name = "HeadersTable"
> > >
> > > End Sub
> > > *********************************
> > >
> > > I am now creating a user form (frmDisplayOrders) in Orders.xls to show
> > > information for an order# selected in a listbox (lboOrder) that pulled
> from
> > > various sheets.
> > >
> > > In the 'General' section of Module 1 of Orders.xls I have the following:
> > >
> > > Option Explicit
> > >
> > > Public HeadersTable As Range
> > > Public ShipToTable As Range
> > > Public TalliesTable As Range
> > > etc.. for all the named ranges
> > >
> > > In the Workbook_Open subprocedure i have the following:
> > >
> > > With Workbooks("OrdersWork.xls")
> > > Set HeadersTable = .Worksheets("Headers").Range("HeadersTable")
> > > Set ShipToTable = .Worksheets("ShipTo").Range("ShipToTable")
> > > etc.... for all the tables
> > > End With
> > >
> > > The subprocedure lboOrders_Change starts as follows:
> > > *********************************
> > > Private Sub lboOrder_Change()
> > >
> > > Dim CustomerID As Long
> > > Dim Code As String
> > > Dim OrderReady As String
> > >
> > >
> > > With Application.WorksheetFunction
> > > 'Customer#
> > > CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
> > > **********************
> > > If I run frmDisplayOrders.Show it works just fine. I stop it using the
> > > form's default cancel 'X'. I can restart it and run it multiple times.
> Then I
> > > will re-run frnDisplayOrder, select a value in lboOrder, and it stops
> on the
> > > above line (CustomerID = .Vlookup....) with 'Run time error '5': Invalid
> > > procedure or argument. Debug shows:
> > > CustID = 0 (expected)
> > > lboOrder.value = 312628 (correct)
> > > HeadersTable = nothing (I have no idea if this is correct)
> > >
> > > In order to get the form to work correctly, I thought that I had to
> close
> > > Orders.xlsa and re-open, but discovered if I re-ran the Workbook_Open
> > > subprocedure.
> > >
> > > I did a search through all the code for 'HeadersTable' and it was only
> found
> > > where shown above i.e. The general section of the Module 1,
> Worbook_Open, and
> > > lboOrders_Change.
> > >
> > > (The help for 'runtime error 5' is especially helpful. I have never
> > > understood why these error messages are not more explicit. The cause of
> the
> > > problem is known, why make one guess what it is. Subprocedure???
> variable???
> > > which variable???)
> > >
> > > TIA
> > >
> > > Tim Kredlo
> > >
> > >
> > >
> > >
>
>
>
 
 
Dave





PostPosted: Fri Aug 26 16:03:17 CDT 2005 Top

Excel Programming >> run time error '5' Not by itself--but if your code includes an "end" (by itself--not "end if", "end
with", ...), then your variables will be cleared, too.


>
> Thanks Tom. I did not know that.
>
> Is clicking on the forms red 'X' do the same thing?
>

>
> > If you encounter an error and hit the reset button in the vbe, then you will
> > clear any variables.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >


> > > Jim,
> > >
> > > Since CustID= .VLookup(lboOrder.Value, HeadersTable, 3, 0) works great
> > until
> > > 'all of a sudden' it doesn't, I don't think that is the issue.
> > >
> > > Since this is a 'work in process' and I am modifying the form, testing,
> > > modifying, testing, etc., I think that the program is somehow 'losing'
> > > whatever the result of the WorkBook_Open sub procedure statement "Set
> > > HeadersTable = .Worksheets("Headers").Range("HeadersTable")" is. If I
> > rerun
> > > the Workbook_Open sub procedure everything works OK.
> > >
> > > Any other ideas?
> > >
> > > Tim Kredlo
> > >

> > >
> > > > Tim,
> > > >
> > > > At first look, I believe
> > > > CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
> > > > should be
> > > > CustomerID = .VLookup(lboOrder.Value,Range(" HeadersTable"), 3, 0)
> > > >
> > > > Regards,
> > > > Jim Cone
> > > > San Francisco, USA
> > > >
> > > >
> > > > "Tim Kredlo"



> > > > I am new to VBA programming and am having the following problem. I don't
> > know
> > > > how much of the following info is relevant or what other info is
> > required to
> > > > answer, so please bear with me and excuse my 'long-windedness'.
> > > >
> > > > I have a multi-sheet workbook ("Orders.xls") that is VBA opened and
> > filled
> > > > by another workbook ("FillOrders.xls"). FillOrders.xls also dynamically
> > > > creates named ranges in Orders.xls that constitutes the data on each
> > the
> > > > sheet in Orders.xls. For example, the range 'HeadersTable' is comprised
> > of
> > > > that area on the sheet 'Headers' that actually contains data. I do that
> > as
> > > > follows:
> > > >
> > > > **************************************
> > > > (Not in this workbook)
> > > > Sub BuildHeadersTable()
> > > >
> > > > Dim HeadersTable As Range
> > > >
> > > > With Workbooks("Orders.xls").Worksheets("Headers"). _
> > > > Range("A1").CurrentRegion
> > > > Set HeadersTable = .Offset(1, 0) _
> > > > .Resize(.Rows.Count - 1, _
> > > > .Columns.Count)
> > > > End With
> > > >
> > > > 'Name the table
> > > > HeadersTable.Name = "HeadersTable"
> > > >
> > > > End Sub
> > > > *********************************
> > > >
> > > > I am now creating a user form (frmDisplayOrders) in Orders.xls to show
> > > > information for an order# selected in a listbox (lboOrder) that pulled
> > from
> > > > various sheets.
> > > >
> > > > In the 'General' section of Module 1 of Orders.xls I have the following:
> > > >
> > > > Option Explicit
> > > >
> > > > Public HeadersTable As Range
> > > > Public ShipToTable As Range
> > > > Public TalliesTable As Range
> > > > etc.. for all the named ranges
> > > >
> > > > In the Workbook_Open subprocedure i have the following:
> > > >
> > > > With Workbooks("OrdersWork.xls")
> > > > Set HeadersTable = .Worksheets("Headers").Range("HeadersTable")
> > > > Set ShipToTable = .Worksheets("ShipTo").Range("ShipToTable")
> > > > etc.... for all the tables
> > > > End With
> > > >
> > > > The subprocedure lboOrders_Change starts as follows:
> > > > *********************************
> > > > Private Sub lboOrder_Change()
> > > >
> > > > Dim CustomerID As Long
> > > > Dim Code As String
> > > > Dim OrderReady As String
> > > >
> > > >
> > > > With Application.WorksheetFunction
> > > > 'Customer#
> > > > CustomerID = .VLookup(lboOrder.Value, HeadersTable, 3, 0)
> > > > **********************
> > > > If I run frmDisplayOrders.Show it works just fine. I stop it using the
> > > > form's default cancel 'X'. I can restart it and run it multiple times.
> > Then I
> > > > will re-run frnDisplayOrder, select a value in lboOrder, and it stops
> > on the
> > > > above line (CustomerID = .Vlookup....) with 'Run time error '5': Invalid
> > > > procedure or argument. Debug shows:
> > > > CustID = 0 (expected)
> > > > lboOrder.value = 312628 (correct)
> > > > HeadersTable = nothing (I have no idea if this is correct)
> > > >
> > > > In order to get the form to work correctly, I thought that I had to
> > close
> > > > Orders.xlsa and re-open, but discovered if I re-ran the Workbook_Open
> > > > subprocedure.
> > > >
> > > > I did a search through all the code for 'HeadersTable' and it was only
> > found
> > > > where shown above i.e. The general section of the Module 1,
> > Worbook_Open, and
> > > > lboOrders_Change.
> > > >
> > > > (The help for 'runtime error 5' is especially helpful. I have never
> > > > understood why these error messages are not more explicit. The cause of
> > the
> > > > problem is known, why make one guess what it is. Subprocedure???
> > variable???
> > > > which variable???)
> > > >
> > > > TIA
> > > >
> > > > Tim Kredlo
> > > >
> > > >
> > > >
> > > >
> >
> >
> >

--

Dave Peterson