Re: run time error '5'

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"Jim Cone" wrote:

> 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"
> <TimKredlo@xxxxxxxxxxxxxxxxxxxxxxxxx>
> wrote in message
> news:C3D94792-43E2-4966-9C7B-884CBCCC65C1@xxxxxxxxxxxxx
> 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-*** 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
> *** in Orders.xls. For example, the range 'HeadersTable' is comprised of
> that area on the *** '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
>
>
>
>
.

Quantcast