combobox out of memory error

J

jgh

I have a combobox in a form which throws up a message when the combobox
change event is fired the message reads "Not enough system resources to
display completley". Even with on error statement the messages still appears,
is this a problem with forms that have many feilds or objects?

can one flush the memory within vba or release resources?

See attached segment of code,

Public Sub CHANGECATDETAILS_Click()
Msg = ""
If CHANGECATDETAILS.Caption = "LIST VDR CATEGORIES ONLY" Then
Title = "VDR Categories Only Selected"
Msg = "Note Drop Down Box will only display Categories listed in VDR" &
vbCrLf & vbCrLf
Msg = Msg & "To display All categories Click on Cancel button" & vbCrLf
& vbCrLf
Else
Title = "ALL Categories Selected"
Msg = "Note Drop Down Box will display ALL Categories available in
database" & vbCrLf & vbCrLf
Msg = Msg & "To display only VDR categories Click on Cancel button" &
vbCrLf & vbCrLf

End If
Msg = Msg & "To continue click on OK button"
Style = vbOK + vbInformation

response = MsgBox(Msg, Style, Title)

If response = vbCancel Then Exit Sub

If CHANGECATDETAILS.Caption = "LIST VDR CATEGORIES ONLY" Then
vdr1 = True
Else
vdr1 = False
End If

refresh

If vdr1 = True Then
CHANGECATDETAILS.Caption = "LIST ALL CATEGORIES"

Else
CHANGECATDETAILS.Caption = "LIST VDR CATEGORIES ONLY"
End If
End Sub

Public Sub refresh()
UserForm2.ComboBox5.RowSource = ("")
UserForm2.ComboBox6.RowSource = ("")

If vdr1 = True Then


Dim buf
Dim arrayRet()
Dim i As Long, j As Long
Dim rownum1, ms As Variant
buf = Application.Transpose([doccat3].Value)

For i = LBound(buf, 2) To UBound(buf, 2)

If buf(1, i) <> "" And buf(1, i) <> "ZZZ" Then
j = j + 1
ReDim Preserve arrayRet(LBound(buf) To UBound(buf), 1 To j)
arrayRet(1, j) = buf(1, i) 'Column A
arrayRet(2, j) = buf(2, i) 'Column B
Set listcat = Range("catonly")
rownum1 = Application.Match(buf(1, i), listcat, 0) 'Column C
arrayRet(3, j) = rownum1

End If

Next

UserForm2.ComboBox5.Column = arrayRet
UserForm2.ComboBox6.Column = arrayRet

'CHANGECATDETAILS.Caption = "LIST ALL CATEGORIES"

UserForm2.ComboBox6.Text = "A01"


Else
'vdr1 = False
'CHANGECATDETAILS.Caption = "LIST VDR CATEGORIES ONLY"
UserForm2.ComboBox5.RowSource = ("STDTITLES1")
UserForm2.ComboBox6.RowSource = ("catonly")
End If
End Sub

**************end*************
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top