Listbox Value = Null ??
 

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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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


"Ryan H" wrote in message
...
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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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


"JLGWhiz" wrote:

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


"Ryan H" wrote in message
...
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

--
Cheers,
Ryan

Listbox Value = Null ??
 

If lbxPreview.listindex < 0 then
'nothing selected

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


Ryan H wrote:

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

--
Cheers,
Ryan

--

Dave Peterson

Listbox Value = Null ??
 

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)


"Ryan H" wrote in message
...
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


"JLGWhiz" wrote:

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


"Ryan H" wrote in message
...
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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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.

--
Rick (MVP - Excel)


"Ryan H" wrote in message
...
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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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

HTH,

Eric

"Ryan H" wrote:

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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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.

--
Rick (MVP - Excel)


"egun" wrote in message
...
Shouldn't you be using "IsNull()" instead of "= Null"??

HTH,

Eric

"Ryan H" wrote:

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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"egun" wrote in message
...
Shouldn't you be using "IsNull()" instead of "= Null"??

HTH,

Eric

"Ryan H" wrote:

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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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)


"Ryan H" wrote in message
...
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
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"egun" wrote in message
...
Shouldn't you be using "IsNull()" instead of "= Null"??

HTH,

Eric

"Ryan H" wrote:

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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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!!
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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)


"Ryan H" wrote in message
...
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
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"egun" wrote in message
...
Shouldn't you be using "IsNull()" instead of "= Null"??

HTH,

Eric

"Ryan H" wrote:

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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Ryan H" wrote in message
...
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!!
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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)


"Ryan H" wrote in message
...
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
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"egun" wrote in message
...
Shouldn't you be using "IsNull()" instead of "= Null"??

HTH,

Eric

"Ryan H" wrote:

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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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.

--
Rick (MVP - Excel)


"Jon Peltier" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Ryan H" wrote in message
...
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!!
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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)


"Ryan H" wrote in message
...
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
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"egun" wrote in message
...
Shouldn't you be using "IsNull()" instead of "= Null"??

HTH,

Eric

"Ryan H" wrote:

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

--
Cheers,
Ryan

Listbox Value = Null ??
 

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Jon Peltier" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Ryan H" wrote in message
...
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!!
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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)


"Ryan H" wrote in message
...
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
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"egun" wrote in message
...
Shouldn't you be using "IsNull()" instead of "= Null"??

HTH,

Eric

"Ryan H" wrote:

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

--
Cheers,
Ryan