UserForm amending data with multiple entries

[MyForcey]

Hi All, As per the attached example, I have a table with 3 forms. UserForm1 adds data to a new row and UserForm3 populates the selected row to be amended. I’ve had assistance with my listbox in UserForm1where I needed to select multiple items and list them on a new line but within the same cell.

I am struggling to match the code of UserForm1 to populate and have the chance to amend (update) the data using UserForm3… everything else works but the list.
I am a complete novice and this is far beyond my VBA knowledge (and it's already taken me over a year to get to this point).

Any assistance will be highly appreciated, thank you.

ExampleWorkBook <-- Download Link.

Also posted here ListBox in UserForm with multiple drop down entries

 

[MyForcey]

what does the error message say?

Run time error 380;

that code gets highlighted - says null when hovering over it.

 

[dmt32]

Run time error 380;

that code gets highlighted - says null when hovering over it.

curious - follow these instructions

- double click the listbox & DELETE the event code
- DELETE the listbox on the userform
- ADD a NEW listbox & rename it accordingly (reinsurers)

See if this resolves the issue.

Dave

 

[MyForcey]

curious - follow these instructions

- double click the listbox & DELETE the event code
- DELETE the listbox on the userform
- ADD a NEW listbox & rename it accordingly (reinsurers)

See if this resolves the issue.

Dave

so i did as you said, i quickly tried and it worked but i needed to change from single to multiselect in the listbox and sent me back to the same issue.

Code is looking like this:

Option Explicit

Dim ary1 As Variant, ary2 As Variant, ary3 As Variant, aryRISK As Variant
Dim tbl1 As ListObject, tbl2 As ListObject, tbl3 As ListObject, tblRISK As ListObject
Dim ws1 As Worksheet, ws2 As Worksheet
Dim newrow As ListRow
Dim reins As String
Dim msgValue As VbMsgBoxResult
Dim X As Long

Private Sub UserForm_Initialize()
  Set ws1 = LIVE
  Set tblRISK = ws1.ListObjects("FAC_RISKS")
  Set ws2 = Sheet2
  Set tbl1 = ws2.ListObjects("Table1")
  Set tbl2 = ws2.ListObjects("Table2")
  Set tbl3 = ws2.ListObjects("Table3")
  With tbl1
    If .ListRows.Count = 1 Then
      Me.cedent.AddItem .DataBodyRange(1)
    Else
      ary1 = .DataBodyRange
      Me.cedent.List = ary1
    End If
  End With
  With tbl2
    If .ListRows.Count = 1 Then
      Me.status.AddItem .DataBodyRange(1)
    Else
      ary1 = .DataBodyRange
      Me.status.List = ary1
    End If
  End With
  With tbl3
    If .ListRows.Count = 1 Then
      Me.reinsurers.AddItem .DataBodyRange(1)
    Else
      ary1 = .DataBodyRange
      Me.reinsurers.List = ary1
    End If
  End With
End Sub

Private Sub ADDCommandButton1_Click()
  Dim msgValue    As VbMsgBoxResult


  msgValue = MsgBox("Do You Want To Save The Amended Details?", 36, "Confirmation")
  If msgValue = vbNo Then Exit Sub


  With tblRISK.ListRows(ActiveCell.Row - 2).Range(, 1)
    .Value = Me.status.Value
    .Offset(, 1).Value = Me.reference.Value
    .Offset(, 2).Value = Me.insured.Value
    .Offset(, 3).Value = CDate(Me.startdate.Value)
    .Offset(, 4).Value = CDate(Me.enddate.Value)
    .Offset(, 5).Value = Format(Me.share.Value / 100, "Percent")
    .Offset(, 6).Value = Me.reinsurers.Value
    .Offset(, 7).Value = Me.brokerage.Value
    .Offset(, 8).Value = Me.excess.Value
    .Offset(, 9).Value = Me.cedent.Value
  End With
   
  MsgBox "Record Updated", 64, "Updated"
     
  Unload Me

End Sub

Private Sub brokerage_Change()
  Dim msoMetaPropertyTypeCurrency
End Sub

Private Sub excess_Change()
  Dim isCurrency
End Sub

Private Sub reinsurers_Change()
  Dim i As Integer
  reinsurers.MultiSelect = fmMultiSelectMulti
  If Checked Then
    For i = 0 To reinsurers.ListCount - 1
    Next i
  End If
End Sub

Private Sub share_Change()
  Dim IsNumeric
End Sub

Private Sub UserForm_Activate()
  status.Value = Cells(Selection.Row, "A").Value
  reference.Value = Cells(Selection.Row, "B").Value
  insured.Value = Cells(Selection.Row, "C").Value
  startdate.Value = Format$(Cells(Selection.Row, "D").Value)
  enddate.Value = Format$(Cells(Selection.Row, "E").Value)
  share.Value = Cells(Selection.Row, "F").Value * 100
  Me.reinsurers.Value = Cells(Selection.Row, "G").Value
  brokerage.Value = Cells(Selection.Row, "H").Value
  excess.Value = Cells(Selection.Row, "I").Value
  cedent.Value = Cells(Selection.Row, "J").Value
End Sub

 

[dmt32]

I was guessing it may have been something like that causing the issue.

As you only have a single field for that entry why the need to multiselect??

Dave

 

[MyForcey]

I was guessing it may have been something like that causing the issue.

As you only have a single field for that entry why the need to multiselect??

Dave

On some I need to enter more than one “reinsurer” so with the adding new data form, I can select multiple and it goes onto a new line within the same cell.

 

[dmt32]

On some I need to enter more than one “reinsurer” so with the adding new data form, I can select multiple and it goes onto a new line within the same cell.

I don't have an immediate answer for that issue - if get time, will have a play around.

Dave