Out of Memory Error when calling Userform

[Stephen Lloyd]

Hi, I created and distributed an add-in that creates a new menu in Excel.
There are only two menu items-one of which calls a userform. Two users have
experienced problems wherein when they try to select the menu item they get a
Microsoft Visual Basic Out of Memory error. Upon hitting ok the error shows
as a Run Time error 7. I had a co-worker (i am not at that location) try
pulling up the userform directly from the userform_Initialize event by using
F8 to step through, but an error message was received before even beginning
the sub implicating an object not being found. We checked and all referenced
objects were in place. My code is below. The code is contained within the
..xla that is referenced. Any ideas?


Thank you so much for any assistance.

Private Sub UserForm_Initialize()

Dim wbEndUser As Workbook
Dim wsListInfo As Worksheet
Dim rDatabases As Range
Dim rPayments As Range
Dim rCell As Range

Dim wsUserInfo As Worksheet
Dim rUserName As Range
Dim rPayPointId As Range
Dim rUserCenter As Range
Dim rLastLogin As Range
Dim strPayPointId As String
Dim strUserName As String
Dim strUserCenter As String


Set wbEndUser = Workbooks("FDEndUser v0.2.xla")
Set wsListInfo = wbEndUser.Worksheets("FormList")

Set rDatabases = wsListInfo.Range("rDataBases")
Set rPayments = wsListInfo.Range("rPayments")

Set wsUserInfo = wbEndUser.Worksheets("UserInfo")
Set rUserName = wsUserInfo.Range("rUserName")
Set rPayPointId = wsUserInfo.Range("rPayPointID")
Set rUserCenter = wsUserInfo.Range("rUserCenter")
Set rLastLogin = wsUserInfo.Range("rLastLogin")

If rLastLogin.Value <> Format(Now(), "mm/dd/yy") Then
If rUserName.Value = "" Then
strPayPointId = InputBox("Please Enter your PayPoint UserID:",
"Enter PayPoint ID")

Do While strPayPointId = vbNullString
MsgBox "You must enter your Paypoint UserID.", vbOKOnly +
vbCritical
strPayPointId = InputBox("Please Enter your PayPoint
UserID:", "Enter PayPoint ID")
Loop

strUserName = Left(strPayPointId, Len(strPayPointId) - 15)
strUserCenter = InputBox("Enter your Center: (KOP or CDR)" &
vbCrLf & _
"Effingham CSRs should enter CDR.", "EnterCenter")
rLastLogin.Value = Format(Now(), "mm/dd/yy")
rUserName.Value = strUserName
rPayPointId.Value = strPayPointId
rUserCenter.Value = strUserCenter
End If
End If

For Each rCell In rDatabases
Me.lbDatabase.AddItem rCell.Value
Next rCell

For Each rCell In rPayments
Me.lbPayType.AddItem rCell.Value
Next rCell

frmPaymentEntry.StartUpPosition = 2

End Sub

 

[Jim Cone]

Are you unloading the form after the user is done with it?
--
Jim Cone
Portland, Oregon USA




"Stephen Lloyd"
wrote in message
Hi, I created and distributed an add-in that creates a new menu in Excel.
There are only two menu items-one of which calls a userform.
Two users have experienced problems wherein when they try to select
the menu item they get a Microsoft Visual Basic Out of Memory error.
Upon hitting ok the error shows as a Run Time error 7.
I had a co-worker (i am not at that location) try pulling up the userform
directly from the userform_Initialize event by using F8 to step through,
but an error message was received before even beginning the sub
implicating an object not being found. We checked and all referenced
objects were in place. My code is below.
The code is contained within the .xla that is referenced. Any ideas?
Thank you so much for any assistance.

Private Sub UserForm_Initialize()
Dim wbEndUser As Workbook
Dim wsListInfo As Worksheet
Dim rDatabases As Range
Dim rPayments As Range
Dim rCell As Range

Dim wsUserInfo As Worksheet
Dim rUserName As Range
Dim rPayPointId As Range
Dim rUserCenter As Range
Dim rLastLogin As Range
Dim strPayPointId As String
Dim strUserName As String
Dim strUserCenter As String


Set wbEndUser = Workbooks("FDEndUser v0.2.xla")
Set wsListInfo = wbEndUser.Worksheets("FormList")

Set rDatabases = wsListInfo.Range("rDataBases")
Set rPayments = wsListInfo.Range("rPayments")

Set wsUserInfo = wbEndUser.Worksheets("UserInfo")
Set rUserName = wsUserInfo.Range("rUserName")
Set rPayPointId = wsUserInfo.Range("rPayPointID")
Set rUserCenter = wsUserInfo.Range("rUserCenter")
Set rLastLogin = wsUserInfo.Range("rLastLogin")

If rLastLogin.Value <> Format(Now(), "mm/dd/yy") Then
If rUserName.Value = "" Then
strPayPointId = InputBox("Please Enter your PayPoint UserID:",
"Enter PayPoint ID")

Do While strPayPointId = vbNullString
MsgBox "You must enter your Paypoint UserID.", vbOKOnly +
vbCritical
strPayPointId = InputBox("Please Enter your PayPoint
UserID:", "Enter PayPoint ID")
Loop

strUserName = Left(strPayPointId, Len(strPayPointId) - 15)
strUserCenter = InputBox("Enter your Center: (KOP or CDR)" &
vbCrLf & _
"Effingham CSRs should enter CDR.", "EnterCenter")
rLastLogin.Value = Format(Now(), "mm/dd/yy")
rUserName.Value = strUserName
rPayPointId.Value = strPayPointId
rUserCenter.Value = strUserCenter
End If
End If

For Each rCell In rDatabases
Me.lbDatabase.AddItem rCell.Value
Next rCell

For Each rCell In rPayments
Me.lbPayType.AddItem rCell.Value
Next rCell

frmPaymentEntry.StartUpPosition = 2
End Sub

 

[Bob Flanagan]

If your add-in has a reference (under Tools in VBA) to a refedit control,
remove the reference. It can cause similar problems. A bad install of
Office can also cause it. You will find step by step instructions on how to
properly fix Office at http://www.add-ins.com/how_to_repair_office.htm

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel