listbox inconsistencies

[TimK]

I am a relatively new VBA user and this is my first post to this forum.
I am using Excel 2002 sp3 with VBA (help shows VB 6.03 version 1024) on an
XP sp3 machine.

I have a project with one workbook that has one form - frmRecordJob.
This form has a calendar control, a combo box, 2 listboxes, some labels, and
2 text boxes.
The 2 list boxes (lstCode1 and lstCode2) are in a frame.

lstCode2 was created by copy/paste from lstCode1.
Checking their properties shows them to be identical except for the tab
indices and top properties.
The value and text properties are both empty, with no blanks in field.
Both listboxes get their row source set in the form initialization routine.
These listboxes are not changed in any project code other than the form
initialize event.

If I close Excel, restart Excel, open the workbook, immediately start the VB
editor, open and step through the form initialize procedure, the following
occurs:

Private Sub UserForm_Initialize()

*** some irrelevant code left out here ***

With lstCode1
.RowSource = strRngBaseTable
End With 'HERE lstCode1.Value = ""

With lstCode2
.RowSource = strRngBaseTable
End With 'HERE lstCode2.Value = null
End Sub

Why are the 2 list box values be different at this point?
I don't know if they should be "" or null, but I would think they would be
the same.
Would they normally be "" or null?

At the end of the initialize procedure the form is shown and the 2 list
boxes do not have any values selected. This is correct.
The form has a command button on it. If this button is clicked (no other
mouse or keyboard actions) the click event procedure shows the following.

Private Sub cmdOK_Click()
Dim lng01 As Long
Dim bValidateNewGroom() As Variant
ReDim aryGroomDataFlags(1 To 6) As Variant

'If no Code1 entered
If IsNull(lstCode1) Then 'HERE lstCode1 = "" as before
lstCode1.Value = "" 'This line is not executed (correct)
End If 'HERE lstCode1 = "" as
before (correct)

'If no Code2 entered
If IsNull(lstCode2) Then 'HERE lstCode2 = null (as before)
lstCode2.Value = "" 'This line IS executed (correct)
End If 'HERE lstCode2 = null
(should be "")

The following (next) statement blows up with runtime error 94
"Invalid use of null"
I think this is because lstCode2 is null

'If new job data is valid
aryGroomDataFlags = ValidateGroomData_
(calGroomDate.Value, _
cmbAnimalName.Value, _
lstCode1.Value, _
lstCode2.Value, _
txtExtra1Charge.Value, _
txtExtra2Charge.Value)

 

[Jon Peltier]

Check whether lstCode1 has a selected item (ListIndex>=0) which is blank
(i.e., ""), and lstCode2 has no selected item (ListIndex=-1), resulting
in a null value.

- Jon

 

[OssieMac]

Hi Tim,

It appears that you are aware that a zero length string and null are not the
same. Viewing the list boxes on the user form you will be able to see that
lstCode1 has the focus and the value of this one becomes a zero length string
while the other one is null. I can't explain why this occurs but if you
change the tab order in the list box properties and make lstCode2 tab 0 and
lstCode1 tab 1 then the value of lstCode2 becomes a zero length string and
lstCode1 is Null.

Anyway you should be able to work around it with the following code by
assigning zero length strings to temporary variables where you have nulls
then use the temporary variables in your formula.

Dim tempVariable1
Dim tempVariable2

If IsNull(lstCode1) Then
tempVariable1 = ""
Else
tempVariable1 = lstCode1
End If

If IsNull(lstCode2) Then
tempVariable2 = ""
Else
tempVariable2 = lstCode2
End If

aryGroomDataFlags = ValidateGroomData _
(calGroomDate.Value, _
cmbAnimalName.Value, _
tempVariable1.Value, _
tempVariable2.Value, _
txtExtra1Charge.Value, _
txtExtra2Charge.Value)

End Sub

 

[TimK]

Thanks for the suggestion.
I had checked out the listindex issue (and re-checked after yout post) and
both lstCode1 and lstCode2 have a listindex of -1. Neither of them has or has
had focus in my testing. (The form is shown, the cursor is in the combobox,
and I then immediately click the command button.)

There is:
a calendar: calGroomDate -- tab index = 0, tab stop = false
a combobox: cmbAnimal -- tab index = 1, tab stop = true
a listbox: lstCode1 -- tab index = 2, tab stop = true
a listbox: lstCode2 -- tab index = 4, tab stop = true
and a few labels with other tab index numbers and tab stops as false.

 

[TimK]

OssieMac,

That worked!!

However, neither of the listboxes has or has had focus in my testing. There
is a combobox that has focus when the form is shown and I immediately click
the command button.

If you can explain why the code you submitted worked, and the following did
not , I would greatly appreciate it. Seriously.

If lstCode1.Value = null Then
lstCode1.value = ""
End If

lstCode.Value is null.
The statement 'lstCode = "" ' is executed - line gets highlighted in debug.
lstCode1.value is still null immediately after (when the 'End If' line is
highlighted).

I would really like to 'understand' so that I don't make the same kind of
mistakes in the future. What is the reason I couldn't change the value of
lstCode1 from null to "" as I had tried?

Again, thanks for your help.

 

[OssieMac]

I don't know the answers Tim. I simply tested to see what was happening but
am at a loss to explain why. The code I posted is simply a workaround because
I know it is possible to test for null and also that you can assign a zero
length string to a variable.

 

[Jon Peltier]

As we say in Boston, "Bizaaah".

I never use the list source property. It links the form too tightly to
the sheet. Instead I load the values in the range into an array, and
assign this array to the list.

I suspect the listbox with focus has a different relationship with the
range than the listbox without focus. This difference may account for
the difference between "" and Null. Using an array instead of a range
does not have any such difference.

- Jon

 

[CellShocked]

I too prefer to create and name a range, and make a call to the named
range in the listbox definition field.