PDA

View Full Version : Dont allow users to select same in List Boxes



Djblois
04-30-2007, 02:46 PM
I am using multiple columns of Listboxes to allow users to select elements of a pivottable. However, I can not figure out how to stop the user from selection two of the same. Here is the code I use to fill the listbox:

ColumnNames = Array("Customer", "Cust City", "Cust State", "Cust Zip", _
"Cust Category", "Cust Group", "Division", "Department", "Invoice#", "Product", "Prod Country", _
"Prod Category", "SalesPerson", "WareHouse")
Column1Selection.List = ColumnNames
Column2Selecion.List = ColumnNames

If a user chooses Customer In Column1Selection then they shouldn't be able to choose Customer in Column2Selection and so on.

shasur
04-30-2007, 07:04 PM
You can use the change event to check that

Private Sub Column1Selection_Change()
If Column1Selection.Text = Column2Selecion.Text Then
MsgBox "Same value selected"
End If
End Sub

Private Sub Column1Selection_Click()
Column1Selection_Change
End Sub

Private Sub Column2Selecion_Change()
If Column1Selection.Text = Column2Selecion.Text Then
MsgBox "Same value selected"
End If
End Sub

Private Sub Column2Selecion_Click()
Column2Selecion_Change
End Sub



I have re-directed the click event too. You can remove the click if it is not necessary

Djblois
04-30-2007, 07:21 PM
Shasur,

Thank you for the help but wouldn't that just inform my users that they chose the same item in 2 columns? They still techincally can hit ok after and ignore that?

Danieil

P.S. I can't do any testing from home. So, I when I get to work tomorrow I will do more testing.

shasur
04-30-2007, 08:07 PM
Hi Daniel .

Here are some options

Option 1 - Disabling the OK Button

Private Sub Column1Selection_Change()
If Column1Selection.Text = Column2Selecion.Text Then
MsgBox "Same value selected"
cmdOK.Enabled = False 'Assuming that you have a command button cmdOK
Else
cmdOK.Enabled = True
End If

End Sub

Private Sub Column2Selecion_Change()
If Column1Selection.Text = Column2Selecion.Text Then
MsgBox "Same value selected"
cmdOK.Enabled = False
Else
cmdOK.Enabled = True
End If
End Sub


Option 2 - Move the selection to first/last item incase of duplicate selection


Private Sub Column1Selection_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Column1Selection.Text = Column2Selecion.Text Then
MsgBox "Same value selected"

If Column2Selecion.ListIndex <> 0 Then
Column1Selection.Selected(0) = True
Else
Column1Selection.Selected(Column1Selection.ListCount - 1) = True
Column1Selection.ListIndex = Column1Selection.ListCount - 1
End If
End If
End Sub

Private Sub Column2Selecion_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Column1Selection.Text = Column2Selecion.Text Then
MsgBox "Same value selected"
If Column1Selection.ListIndex <> 0 Then
Column2Selecion.Selected(0) = True

Else
Column2Selecion.Selected(Column2Selecion.ListCount - 1) = True
Column2Selecion.ListIndex = Column2Selecion.ListCount - 1
End If
End If
UserForm1.Repaint
End Sub


You can fine tune the above to select the next or previous items

Djblois
05-01-2007, 08:44 AM
One Problem I have:

I set them all the string of Nothing at the beginning and I get the msgbox at when I load the form. Is there anyway around that?

Bob Phillips
05-01-2007, 09:32 AM
Why not just build the second on what is not selected in the first and so on?

Djblois
05-01-2007, 10:33 AM
but I want my users to be able to change their selections. They are all on the same userform

Bob Phillips
05-01-2007, 11:02 AM
Depending upon the user selection.