Listbox Value = Null ??

[Ryan H]

I have a list box and a command button on a userform. When the command
button is clicked I want to test to ensure the user has selected only 1 item
in the listbox. I have this but it doesn't work and I don't know why, any
ideas?

' ensure a part number is selected to edit
If lbxPreview.Value = Null Then
MsgBox "Please select a Part Number from the list to edit.",
vbCritical
Exit Sub
End If

 

[JLGWhiz]

If you make the listbox single select instead of multiselect, they can only
select one item.

 

[Ryan H]

I need the listbox to remain a multiselect, because I have another
commandbutton that clears all the users selected lines from the listbox.

If I pause the code I have below during execution and I type
"?lbxPreview.Value" in the Immediate Window, Null is returned. So why is it
my message box does not show?
--
Cheers,
Ryan

If you make the listbox single select instead of multiselect, they can only
select one item.

 

[Dave Peterson]

If lbxPreview.listindex < 0 then
'nothing selected

0 is the first item. N-1 is the Nth item.

 

[Rick Rothstein]

But if you make the ListBox single-select only, you wouldn't need that other
button or, at worst, you would need it to clean the single selection (by
setting the ListIndex to -1).

--
Rick (MVP - Excel)

I need the listbox to remain a multiselect, because I have another
commandbutton that clears all the users selected lines from the listbox.

If I pause the code I have below during execution and I type
"?lbxPreview.Value" in the Immediate Window, Null is returned. So why is
it
my message box does not show?

 

[Rick Rothstein]

Try it this way...

If lbxPreview.ListIndex = -1 Then
MsgBox "Please select a Part Number from the list to edit.", vbCritical
Exit Sub
End If

although I am not sure why you are exiting the Sub rather than waiting for
them to pick an item.

 

[egun]

Shouldn't you be using "IsNull()" instead of "= Null"??

HTH,

Eric

 

[Rick Rothstein]

Neither would be right... Null is for Variant variables to indicate whether
a value of any kind has been assigned to it or not. Unlike "regular"
variables which have a default value (Doubles, Longs, etc. are defaulted to
0, Strings to the empty string), Variants can be almost anything, so there
is no one value that can be assigned to them as a default... they are Null
until a value is given to them.

 

[Ryan H]

Yes, you are right. I prematurely clicked yes for Egun's post, Egun's post
does not work.

How can I test to be sure and item and only 1 item is selected when I click
my "Edit Items" button?


To be clear on what my userform has:

"Remove Items" Command Button
"Edit Item" Command Button
"Add Item" Command Button
"Preview" List Box

Sub cmbRemoveItems_Click()

With lbxPreview
For i = .ListCount To 1 Step -1
If .Selected(i - 1) Then
.RemoveItem (i - 1)
End If
Next i
End With
End Sub

Sub cmbEditItem_Click()

' code to ensure only 1 item was selected before continuing

' edit item
With lbxPreview
.List(.ListIndex, 0) = cboPartNumber
.List(.ListIndex, 1) = cboPartDescription
.List(.ListIndex, 2) = Val(tbxQuantity)
.List(.ListIndex, 3) = Format(Val(tbxUnitPrice), "$ #,##0.00")
.List(.ListIndex, 4) = cboBilling

' check if part is billable
If cboBilling = "Warranty" Then
.List(.ListIndex, 5) = Format(0, "$ #,##0.00")
Else
.List(.ListIndex, 5) = Format(Val(tbxQuantity) *
Val(tbxUnitPrice), "$ #,##0.00")
End If
End With
End Sub

Sub cmbAddItem_Click()

' add new item to listbox
With lbxPreview
.AddItem
.List(.ListCount - 1, 0) = cboPartNumber
.List(.ListCount - 1, 1) = cboPartDescription
.List(.ListCount - 1, 2) = Val(tbxQuantity)
.List(.ListCount - 1, 3) = Format(Val(tbxUnitPrice), "$ #,##0.00")
.List(.ListCount - 1, 4) = cboBilling

' check if part is billable
If cboBilling = "Warranty" Then
.List(.ListCount - 1, 5) = Format(0, "$ #,##0.00")
Else
.List(.ListCount - 1, 5) = Format(Val(tbxQuantity) *
Val(tbxUnitPrice), "$ #,##0.00")
End If
End With
End Sub

 

[Rick Rothstein]

Give code like this a try...

Dim X As Long
Dim SelCount As Long
With lbxPreview
For X = 0 To .ListCount - 1
If .Selected(X) Then SelCount = SelCount + 1
If SelCount > 1 Then
MsgBox "Select one item only!", vbExclamation
.SetFocus
Exit Sub
End If
Next
If SelCount = 0 Then
MsgBox "You must select at least one item!", vbExclamation
.SetFocus
Exit Sub
End If
'
' Rest of your edit code goes here.
'
End With

--
Rick (MVP - Excel)

Yes, you are right. I prematurely clicked yes for Egun's post, Egun's
post
does not work.

How can I test to be sure and item and only 1 item is selected when I
click
my "Edit Items" button?


To be clear on what my userform has:

"Remove Items" Command Button
"Edit Item" Command Button
"Add Item" Command Button
"Preview" List Box

Sub cmbRemoveItems_Click()

With lbxPreview
For i = .ListCount To 1 Step -1
If .Selected(i - 1) Then
.RemoveItem (i - 1)
End If
Next i
End With
End Sub

Sub cmbEditItem_Click()

' code to ensure only 1 item was selected before continuing

' edit item
With lbxPreview
.List(.ListIndex, 0) = cboPartNumber
.List(.ListIndex, 1) = cboPartDescription
.List(.ListIndex, 2) = Val(tbxQuantity)
.List(.ListIndex, 3) = Format(Val(tbxUnitPrice), "$ #,##0.00")
.List(.ListIndex, 4) = cboBilling

' check if part is billable
If cboBilling = "Warranty" Then
.List(.ListIndex, 5) = Format(0, "$ #,##0.00")
Else
.List(.ListIndex, 5) = Format(Val(tbxQuantity) *
Val(tbxUnitPrice), "$ #,##0.00")
End If
End With
End Sub

Sub cmbAddItem_Click()

' add new item to listbox
With lbxPreview
.AddItem
.List(.ListCount - 1, 0) = cboPartNumber
.List(.ListCount - 1, 1) = cboPartDescription
.List(.ListCount - 1, 2) = Val(tbxQuantity)
.List(.ListCount - 1, 3) = Format(Val(tbxUnitPrice), "$
#,##0.00")
.List(.ListCount - 1, 4) = cboBilling

' check if part is billable
If cboBilling = "Warranty" Then
.List(.ListCount - 1, 5) = Format(0, "$ #,##0.00")
Else
.List(.ListCount - 1, 5) = Format(Val(tbxQuantity) *
Val(tbxUnitPrice), "$ #,##0.00")
End If
End With
End Sub

 

[Ryan H]

That is the only way I could think to do it myself, but I was wondering if
there were some built in listbox properties I didn't know about.

Thanks for all the help! You tha man!!

 

[Jon Peltier]

If the user can only select one item at a time, why do you need another
button to deselect "all" of the user's selections?

Just allow one selection, and have another button to clear the single item
selected, by setting ListIndex to -1.

- Jon

 

[Rick Rothstein]

The OP appears to be allowing multiple selections for his other buttons to
process (delete for example) whereas he wants to limit the edit process to
single selections (in other words, the OP is using one ListBox for multiple
purposes). Setting the ListIndex to -1 does not appear to clear multiple
selections.

 

[Jon Peltier]

No it doesn't. I couldn't figure out the purpose for the dual behavior.

- Jon