Solved

How to Initialize VBA 4.0 ListBox?

Posted on 2000-03-13
13
840 Views
Last Modified: 2008-02-20
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
Comment
Question by:vbdvlper
  • 8
  • 3
  • 2
13 Comments
 

Author Comment

by:vbdvlper
ID: 2612627
Adjusted points to 40
0
 

Author Comment

by:vbdvlper
ID: 2612628
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 2613515
Try this:


format(yournullvalue)

This will give your empty string.
0
 

Author Comment

by:vbdvlper
ID: 2613563
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 2613640
How do you define the function

and how do you pass the listbox

please show code
0
 

Author Comment

by:vbdvlper
ID: 2613676
'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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:vbdvlper
ID: 2613680
'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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 2613697
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
 

Author Comment

by:vbdvlper
ID: 2613729
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
 
LVL 27

Expert Comment

by:Ark
ID: 2614769
Hi
It's easy. Before calling your function, add
ListBox1.Refresh
Cheers
0
 

Author Comment

by:vbdvlper
ID: 2615743
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
 
LVL 27

Accepted Solution

by:
Ark earned 40 total points
ID: 2617961
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
 

Author Comment

by:vbdvlper
ID: 2620360
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Mysql vs Oracle 10 120
Vb.net 2008 2 49
How to debug this code 7 50
to transfer string from C lanaguage to VBA 4 44
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now