Vba code for two different data validation list with multiple selections on same worksheet

Question

I have created a worksheet that has a Userform and Listbox that allows for multiple selections.  I now need to do the same but with a different list for a second column in my worksheet.  I am not sure how to augment the Sheet code to allow for a second userform and listbox on a different column/cell.  I assume (although I dont have much knowledge of what I'm doing) that  have to make additional userforms and modules for each of the columns so that the selections can be from a different reference list.

Sheet code:

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.EnableEvents = False

    On Error GoTo exitHandler

    If Target.Column = 2 And (Target.Row >= 2 And Target.Row <= 30) Then

        gSourceListArr = Sheets("Lists").Range("D3:D24").Value

        gCellCurrVal = Target.Value

        UserForm1.Show 'Pop up the form

        Target.Value = gCellCurrVal

    End If

exitHandler:

    Application.EnableEvents = True

End Sub

Userform Code:

Private Sub CommandButton1_Click()

  UserForm1.Hide 'Pop up the form

End Sub

Private Sub CommandButton2_Click()

    For ii = 0 To Listbox1.ListCount - 1

        Me.Listbox1.Selected(ii) = False

    Next ii

End Sub

Private Sub CommandButton3_Click()

    For ii = 0 To Listbox1.ListCount - 1

        Me.Listbox1.Selected(ii) = True

    Next ii

End Sub

Private Sub CommandButton4_Click()

    gCellCurrVal = ""

    For ii = 0 To Listbox1.ListCount - 1

      If Me.Listbox1.Selected(ii) = True Then

        If gCellCurrVal = "" Then

          gCellCurrVal = Me.Listbox1.List(ii)

        Else

          gCellCurrVal = gCellCurrVal & vbNewLine & Me.Listbox1.List(ii)

        End If

      End If

    Next ii

    UserForm1.Hide

End Sub

Private Sub UserForm_Activate()

On Error Resume Next

Me.Listbox1.Clear

For Each element In gSourceListArr

  Me.Listbox1.AddItem element

Next element

UserForm_initialize

End Sub

Private Sub UserForm_initialize()

  For Each element In Split(gCellCurrVal, vbNewLine)

    For ii = 0 To Listbox1.ListCount - 1

      If element = Me.Listbox1.List(ii) Then

        Me.Listbox1.Selected(ii) = True

      End If

    Next ii

  Next element

End Sub

Module code:

Option Explicit

Global gSourceListArr As Variant

Global gCellCurrVal As String

Sub enEvents()

  Application.EnableEvents = True

End Sub

Answer 1

OK, I answered my own question.  Thanks for leading me in the right direction!!

Answer 2

Compile error:

Ambiguous name detected: gCellCurrVal

I don't know what half of the code does (I watched a tutorial to learn which code I needed to put on my worksheet) so its hard to know which things need to be changed when I create the second Userform.  For instance, do I need a second Module? Also, what does "gCellCurrVal" even mean?  if it's just the name given to certain variables then would I need a different name for the second userform/module?

Answer 3

What is the error?

Answer 4

But that's just it, I don't know how to refer to it. If I add an additional section on the sheet code addition below bold and underlined) that has a different source it gives me an error. I'm not sure how else to reference it.

 If Target.Column = 2 And (Target.Row >= 2 And Target.Row <= 30) Then

        gSourceListArr = Sheets("Lists").Range("D3:D24").Value

        gCellCurrVal = Target.Value

        UserForm1.Show 'Pop up the form

        Target.Value = gCellCurrVal

    End If

If Target.Column = 3 And (Target.Row >= 2 And Target.Row <= 30) Then

gSourceListArr = Sheets("Lists").Range("A1:A6").Value

gCellCurrVal = Target.Value

       UserForm2.Show 'Pop up the form

Target.Value = gCellCurrVal

End If

Answer 5

You are correct. Create another form and refer to its name in your code.