MultiSelect property of one control depend on value of checkbox?

[fredalina]

I have a listbox that lists several possible rejection codes. The user first requested that I put some default language in a comments field based on the value of the listbox, which works fine. Later the user requested that the listbox be able to allow multiple selections. I found that when I changed the MultiSelect property of the listbox, the default language no longer works.

I am now trying to accommodate both, at least when multiple selections are not required, and they are only required on a small number of records. I inserted a check box called chkMultiple, with the idea that when multiple selections are required, the user can check that box and VBA can change the MultiSelect property of the listbox to 2 (Extended), but the default will be 0 (None).

Here is my code:

Private Sub chkMultiple_AfterUpdate()
    MsgBox "Check box value is " & Me.chkMultiple.Value
    If Me.chkMultiple.Value = 0 Then
        Me.lstRejCode.MultiSelect = 0 'None
    Else
        Me.lstRejCode.MultiSelect = 2 'Extended
    End If
End Sub

The error code is "Run-time error '2448': You can't assign a value to the object, with the object being lstRejCode. The error occurs in both Me.lstRejCode.MultiSelect = lines.

Can you help me debug this code, or make other suggestions to accomplish the same end? Thanks!

 

[pbaldy]

IIRC, the multiselect property can only be set in design view. What was the problem with the default value? Since a multiselect listbox always returns Null, you'd have to cycle through selected items rather than examine its value.

 

[fredalina]

Ah, bummer.

Let me explain the default values a bit more. The comment field is a memo field because frequently large amounts of text is stored in it, sometimes several paragraphs. This is where the user explains to the end customer why their item was rejected in more detail. Sometimes company policies are referenced, etc.

The two most common rejection codes have common wording that doesn't change much in any order with that rejection code. The user would prefer that wording to be filled in the Comments section as soon as the Rejection Code is chosen from the list box, rather than to have to type it in each time or even copy/paste from another record or program. The code for the default comments wording is:

Private Sub lstRejCode_Click()
    Select Case Me.lstRejCode.Value
        Case 6
            Me.Comments.Value = "Default Wording 6"
        Case 4
            Me.Comments.Value = "Default Wording 4"
        Case Else
            Me.Comments.Value = ""
    End Select
End Sub

It's a bit more complicated than that and actually affects a few fields, but you get the idea. When the lstRejCode.MultiSelect value is either Simple or Complex in Design view, the above code just stops working. No errors or anything, it just doesn't fill in the Comments field even if there is only one selection made and it's either 4 or 6. I was hoping to be able to have None be the default of the MultiSelect field and only enable MultiSelect (complex) on the fly as the user actually needs it, thus enabling the best of both worlds.

Thanks!

 

[pbaldy]

Like I said, a listbox set to either of the multiselect settings will always have a Null value. You'd have to use the code to loop through selected items. Are you familiar with that code?

 

[fredalina]

Not for listboxes specifically. Thanks again!

 

[pbaldy]

Here's the shell I add when I need it:

  Dim ctl         As Control
  Dim varItem     As Variant

  Set ctl = Me.ListBoxName
  For Each varItem In ctl.ItemsSelected
    'ctl.ItemData(varItem)
  Next varItem

  Set ctl = Nothing

The commented out line is how you refer to the selected item.

 

[fredalina]

The commented out line is how you refer to the selected item.

i'm sorry, i don't understand this part at all (or its corresponding code).

 

[pbaldy]

What I mean is that this:

ctl.ItemData(varItem)

tells you the value of the selected item(s). That code will loop through however many items are selected, so within the loop that refers to one specific selection. You might want to try this:

  Dim ctl         As Control
  Dim varItem     As Variant

  Set ctl = Me.ListBoxName
  For Each varItem In ctl.ItemsSelected

    Select Case ctl.ItemData(varItem)
        Case 6
            Me.Comments.Value = "Default Wording 6"
        Case 4
            Me.Comments.Value = "Default Wording 4"
        Case Else
            Me.Comments.Value = ""
    End Select
  Next varItem

  Set ctl = Nothing

Though because the listbox is multiselect, you'd probably want to add to the comments field each time.

 

[fredalina]

This did it for me. Thanks!

    Dim ctl As Control
    Dim varItem As Variant
    Dim lstvar As String
    
    Set ctl = Me.lstRejCode
    For Each varItem In ctl.ItemsSelected
        'ctl.ItemData(varItem)
        lstvar = lstvar & ctl.ItemData(varItem)
    Next varItem
    
    Select Case lstvar
        Case "06"
            Me.Comments.Value = "Default Wording 6"
        Case "04"
            Me.Comments.Value = "Default Wording 4"
        Case Else
            Me.Comments.Value = ""
    End Select    
    Set ctl = Nothing
 
End Sub

This works for selections 04 and 06 only, but not 0406 any combination of 04 or 06 and any other code, which is exactly how I need it. Thanks so much!

 

[pbaldy]

Excellent! Glad you were able to tweak it to your needs.