• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

How to Initialize VBA 4.0 ListBox?

Experts,

I'm working on this problem for a couple days now....
The function works fine for comboboxes, but gives type mismatch error for a listbox...the listbox is not initializing properly in the form activate event and is being passed as Null instead of an empty string.....?

I saw a question yesterday about initializing a list box and passing it to a function.....I am having this problem in VBA 4.0 for windows 95.....

The function is having trouble determining the data type of the list box, the type is displaying as variant and = Null...VBA 4.0 has limited functionality and there is no repaint method on the listbox....

Is there a way to initialize the List Box during a VBA form Activate event prior to passing the listbox to a function....ie, so the Listbox = "" an empty string instead of Null????
0
vbdvlper
Asked:
vbdvlper
  • 8
  • 3
  • 2
1 Solution
 
vbdvlperAuthor Commented:
Adjusted points to 40
0
 
vbdvlperAuthor Commented:
This may be difficult....I would pass a combobox, but the list box works better for the application.  There have been some questions on initializing the listbox in the archives....they don't apply to VBA though.  

I tried to repaint the form, and to embed the function call from other events ie form_change, form_activate, and form_click, but the value of the listbox variable = Null and is being passed to the function as Null.  

Is there a way to initialize the value of a listbox variable before it is passed to a function call?  Can the Null value be changed to an empty string or a listbox value? Thanks
0
 
EDDYKTCommented:
Try this:


format(yournullvalue)

This will give your empty string.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
vbdvlperAuthor Commented:
Thank you,

it is still crashing when the function is called and the listbox is passed to the function with runtime error 13 type mismatch.  Using format() the value of the listbox = "" and not Null, but the error still comes up....The listbox = "", but this is still crashing the function call...Is there a way to pass the listbox to the function?
0
 
EDDYKTCommented:
How do you define the function

and how do you pass the listbox

please show code
0
 
vbdvlperAuthor Commented:
'Form Activate Event
Private Sub UserForm_Activate()
ErrorString = ""
StringVar = "Test"
If Not IniListBox(ErrorString,ListBox1, StringVar) Then
End If
End Sub
'Function
Public Function IniListBox(ByRef ErrorString As String, _
                           ByRef ListBoxOne As ListBox, _
                           ByVal SearchString As String) _
                           As Boolean
Do While j < 10
 ListBox1.AddItem cstr(j), j - 1
 j = j + 1
Loop
IniListBox = true
End Function

Thanks
0
 
vbdvlperAuthor Commented:
'Function
Public Function IniListBox(ByRef ErrorString As String, _
                           ByRef ListBoxOne As ListBox, _
                           ByVal SearchString As String) _
                           As Boolean
Do While j < 10
 ListBox1.AddItem SearchString & cstr(j), j - 1
 j = j + 1
Loop
IniListBox = true
End Function

Forgot to add the SearchString
Thanks
0
 
EDDYKTCommented:
I don't have any problem ex cept change your function to


Private Function IniListBox(ByRef ErrorString As String, _
ByRef ListBoxOne As ListBox, _
ByVal SearchString As String) _
As Boolean
Dim j As Integer
Do While j < 10
 ListBoxOne.AddItem CStr(j), j
 j = j + 1
Loop
IniListBox = True
End Function
0
 
vbdvlperAuthor Commented:
Thanks...I changed the function to a private function, and now VB doesn't know where it is. When the function is public, VB can find it, but then it crashes with runtime error #13 type mismatch...if the function call is ok, then what is this error from?  This is version 4.0 of VBA on Windows 95....Thanks

vbdvlper
0
 
ArkCommented:
Hi
It's easy. Before calling your function, add
ListBox1.Refresh
Cheers
0
 
vbdvlperAuthor Commented:
There seems to be a problem with ListBox1.Refresh....I'm getting a compile message that the method is not found....this is VBA 4.0 for 97, and i tried a repaint of the user form, and this isn't working either.....

as a quick fix, i'm going to pass a combo box to the function, initialize the combo, then initialize the list box using the combo box, since the combo box passes to the function and initializes with no problems....but the list box should also pass to the function with no problems or a type mismatch error....

Is there another way to refresh the listbox before its passed....this version 4.0 doesn't have the refresh method... Thanks
0
 
ArkCommented:
Hi
In VB4 some controls initialize during Form_Initialize event (combos, buttons, pictures), some after Form_Load event (Listboxes, TextBoxes, Labels). You can call your functiopn not from Form_Load event, but from Form_Activate, Form_Paint or Form_Resize event (all this events occure when form show at first time). If you want to call your function only one time when form initializing, you can use Static variable:
Private Sub Form_Resize ()
  Static bInit as Boolean
  If Not bInit Then
     bInit = True
     Call MyFunc (List1)
  End If
End Sub
Cheers
0
 
vbdvlperAuthor Commented:
Thanks Ark,

This is a workaround for the bugs.....i was using a workaround of passing a combobox to the function, then filling up the combobox with the info, then returning the combo, and filling the listbox with the info in the combobox.....it is many times more controls than i would like,....but it also is a way around passing the list box......
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now