I have a strange problem with some macros in Excel 2000.

The macros are all run from buttons on the same worksheet. Thi
worksheet allows the user to select a client name from a drop-dow
list, then builds a path/filename based on that selection.

Once the path/filename is built, the user can press another button t
actually load a file into Excel (it is an excel workbook). This macr
uses the code "Workbooks.Open m_file" to load the workbook, wher
m_file contains the path/filename generated earlier. This part work
fine.

Once the file is loaded, there is another button which runs a macro t
compare a list of names in the current workbook against a master lis
in another workbook. If there are names to be added, the macro insert
a row in the proper place and copys the information from the maste
list.

I am using "Selection.EntireRow.Insert" to insert the row (th
currently selected row is at the position where I want the ro
inserted).

This code causes a memory access violation and crashes Excel, bu
here's the strange part. This crash happens *only* if the file wa
loaded by clicking the "load" button mentioned earlier.

If the user selects the client name from the list (so the filename get
built), but then goes out and loads the file manually without clickin
the "Load file" button, then when the "Update Rows" button is clicked
there is no crash. The macro works fine and inserts rows as needed
often multiple times in the same run. Running the Update macro afte
the file has been loaded by clicking the Load button results in a fata
crash every single time the EntireRow.Insert code fires.

Anyone have any ideas why this could be happening

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

I think I had a similar problem where the readonly status
of the file was changed during the execution of the macro.

This then caused problems including a memory access
violation.

I suggest you save the newly created workbook before
making changes and then aftewards.

This may not be relevant but I hope it helps

Nick


-----Original Message-----
I have a strange problem with some macros in Excel 2000.

The macros are all run from buttons on the same
worksheet. This
worksheet allows the user to select a client name from a
drop-down
list, then builds a path/filename based on that selection.

Once the path/filename is built, the user can press
another button to
actually load a file into Excel (it is an excel
workbook). This macro
uses the code "Workbooks.Open m_file" to load the
workbook, where
m_file contains the path/filename generated earlier.
This part works
fine.

Once the file is loaded, there is another button which
runs a macro to
compare a list of names in the current workbook against a
master list
in another workbook. If there are names to be added, the
macro inserts
a row in the proper place and copys the information from
the master
list.

I am using "Selection.EntireRow.Insert" to insert the row
(the
currently selected row is at the position where I want
the row
inserted).

This code causes a memory access violation and crashes
Excel, but
here's the strange part. This crash happens *only* if
the file was
loaded by clicking the "load" button mentioned earlier.

If the user selects the client name from the list (so the
filename gets
built), but then goes out and loads the file manually
without clicking
the "Load file" button, then when the "Update Rows"
button is clicked,
there is no crash. The macro works fine and inserts rows
as needed,
often multiple times in the same run. Running the Update
macro after
the file has been loaded by clicking the Load button
results in a fatal
crash every single time the EntireRow.Insert code fires.

Anyone have any ideas why this could be happening?


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

.

Thanks for the suggestion, but that didn't make any difference :(

Here's the code that I'm running (I removed some extraneous stuff, bu
sorry, it's still long):


Code
-------------------

Sub UpdateFundList()

Dim fromWkBk As Excel.Workbook, toWkBk As Excel.Workbook
Dim Fi, Msg, Targ As String

On Error GoTo ErrMsg

ThisWorkbook.Activate
Set fromWkBk = OpenWkBk(Range("PriorPath"), Range("PriorWorkbook"))

ThisWorkbook.Activate
Set toWkBk = OpenWkBk(Range("CurrentPath"), Range("CurrentWorkbook"))

fromWkBk.Activate

On Error GoTo 0
On Error Resume Next

' Update Current Fundlist From Client's Prior Year Fundlist (Adds funds special to this client)
SetupFunds fromWkBk, toWkBk, "FundList" ; FundList is a named range containing a list of fund names

' Close Prior Year Workbook and Activate Current Year Workbook
fromWkBk.Activate
ActiveWorkbook.Close (False)

ThisWorkbook.Activate

Exit Sub

ErrMsg:
If Err.Number < 0 Then
Msg = "Error # " & str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description & Chr(13) & "Procedure No: 3 (UpdateFundList)"
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
Stop
End If
Resume Next

End Sub

Sub SetupFunds(fromWkBk As Excel.Workbook, toWkBk As Excel.Workbook, FdList As String)

' Copies "FundList" Funds into Current Year's Budget from Prior Year's Budget if they are missing in Current Year's "FundList"

Dim CurrFund, fromRow, toRow, Target, Found, c, fromList, toList, activeFund, fromWs, toWs
Dim CurrRow, GetsLAVTR, Msg, ClntFdNo As String

On Error GoTo ErrMsg
Found = False

' Put "FundList" object names into variables
Set fromWs = fromWkBk.Worksheets("Data")
Set toWs = toWkBk.Worksheets("Data")
Set fromList = fromWs.Range(FdList)
Set toList = toWs.Range(FdList)

fromRow = 1

' Step thru Prior Workbook.Data "FundList"
For Each CurrFund In fromList
Target = CurrFund
activeFund = fromList.Cells(fromRow).Offset(0, 1)

' If Fund is Active Locate Prior "FundList" Fund in Current "FundList"
If UCase(activeFund) = "Y" Then
toWs.Activate
With toList
.Select
Set c = .Find(Target, LookIn:=xlValues)
If Not c Is Nothing Then
' Found It
If c.Value = Target Then
Found = True
toRow = c.Row - toList.Row + 1
End If
End If
End With

If Not Found Then
toRow = InsertDataRow(fromWs, toWs, fromList, toList, FdList, fromRow, toRow + toList.Row - 1)
End If
End If

Found = False

fromRow = fromRow + 1
Next CurrFund

Exit Sub

ErrMsg:
If Err.Number < 0 Then
Msg = "Error # " & str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description & Chr(13) & "Procedure No: 4 (SetupFunds)"
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
Stop
End If
Resume Next

End Sub

Function InsertDataRow(fromWs, toWs, fromList, toList, FdList As String, ByVal fromRow As Integer, ByVal toRow As Integer) As Integer

Dim Target, Found As Boolean, c, GeneralRow, Msg
' Fund was NOT found in FundList, Find insertion point
On Error GoTo ErrMsg
Found = False

Do
' Get Target from fromList
fromWs.Activate
With fromList
.Select
fromRow = fromRow - 1
Target = fromList.Cells(fromRow)
End With
' Try to find Target in toList
toWs.Activate
With toList
.Select
Set c = .Find(Target, LookAt:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
' Found previousTarget
Found = True
toRow = c.Row
c.Select
End If
End With
Loop Until Found = True

' Insert a blank row for data from Prior Workbook.Data "FundList" to be put into
Selection.Offset(1, 0).EntireRow.Insert
Selection.Offset(1, 0).Select

toRow = Selection.Row

InsertDataRow = toRow - toList.Row + 1
Exit Function

ErrMsg:
If Err.Number < 0 Then
Msg = "Error # " & str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description & Chr(13) & "Procedure No: 6 (InsertDataRow}"
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
Stop
End If
Resume Next
End Function

--------------------



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

Okay, more information on this problem...

Here's what's happening now. This code is part of a much large
project, so I copied just this module over to another workbook to mak
it easier to work with while trying to fix this problem.

This new workbook contains nothing but the code listed above, and th
code which loads the two workbooks involved, with buttons to fire eac
sub (same as in the original workbook)

It runs fine. Same identical code that crashes in the other project
running on the same two workbooks that it runs on in that project.

In the problematic project, I start excel from scratch, load th
workbook containing all my code, click 'load files', click 'update fun
list' and it crashes at the first time it tries to insert a row.

In the test project, I start excel from scratch, load the workboo
containing all my code click 'load files', click 'update fund list' an
it works without a problem, even inserting about 8-9 rows as it runs.



Ok, so I thought maybe my original workbook was damaged somehow.
copied all my remaining code from the original project over to the ne
project. Re-run the test and it crashes at the first time it tries t
insert a row.



Anyone have an idea what to try next?

Could I be running into some sort of memory limitation in Excel?
recall hearing about an 80meg formula area in some versions. Could I b
tripping that limit in the bigger project? But since the smaller projec
doesn't have all the extra modules in it, it works?

Can anyone help me

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