| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Failed to save table attributes of (null) into (null). | Excel Discussion (Misc queries) | |||
| Why null reference to ListBox? | Excel Programming | |||
| Null, "null", vbNull, vbNullString, vbEmpty | Excel Programming | |||
| VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
| listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Hybrid Mode
