combobox out of memory error
 

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*************

combobox out of memory error
 

Here is a link for you to check out in terms of memory usage.

http://www.decisionmodels.com/memlimits.htm

Just to confirm you are out of memory and it is not a stack overflow or
something else...
--
HTH...

Jim Thomlinson


"jgh" wrote:

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*************