UserForm amending data with multiple entries

MyForcey

New Member
Joined
Oct 22, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited by a moderator:
what does the error message say?
Run time error 380;

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

Attachments

  • Screenshot 2021-10-26 170155.png
    Screenshot 2021-10-26 170155.png
    12.9 KB · Views: 9
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top