UserForm amending data with multiple entries

M

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:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
This is the code:

Userform1 (working correctly):
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 ADDCommandButton1_Click()
msgValue = MsgBox("Save & Add This Risk Now?", vbYesNo + vbInformation, "Confirmation")
If msgValue = vbNo Then Exit Sub
reins = ""
With Me.reinsurers
For X = 0 To .ListCount - 1
If .Selected(X) = True Then
reins = reins & .List(X) & vbCrLf
End If
Next X
End With
Set newrow = tblRISK.ListRows.Add
With newrow
.Range(1) = Me.status.Value
.Range(2) = Me.reference.Value
.Range(3) = Me.insured.Value
.Range(4) = CDate(Me.startdate.Value)
.Range(5) = CDate(Me.enddate.Value)
.Range(6) = Me.share.Value / 100
.Range(7) = reins
.Range(8) = Me.brokerage.Value
.Range(9) = Me.excess.Value
.Range(10) = Me.cedent.Value
End With
Unload Me
UserForm1.Show
End Sub

Private Sub brokerage_Change()
Dim msoMetaPropertyTypeCurrency
End Sub

Private Sub CLEARCommandButton4_Click()
msgValue = MsgBox("Do You Want To Clear The Form?", vbYesNo + vbInformation, "Confirmation")
If msgValue = vbNo Then Exit Sub
Unload Me
UserForm1.Show
End Sub

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

This is the UserForm3 code that is not working:
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 ADDCommandButton1_Click()
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do You Want To Save The Amended Details?", vbYesNo + vbInformation, "Confirmation")
If msgValue = vbNo Then Exit Sub
Dim ws As Worksheet
Dim Rw As Long
Set ws = Worksheets("FAC DATABASE")
Rw = WorksheetFunction.CountA(Range("A:A")) + 2
ws.Cells(Selection.Row, "A") = UserForm3.status.Value
ws.Cells(Selection.Row, "B") = UserForm3.reference.Value
ws.Cells(Selection.Row, "C") = UserForm3.insured.Value
ws.Cells(Selection.Row, "D") = CDate(UserForm3.startdate.Value)
ws.Cells(Selection.Row, "E") = CDate(UserForm3.enddate.Value)
ws.Cells(Selection.Row, "F") = UserForm3.share.Value / 100
ws.Cells(Selection.Row, "H") = UserForm3.brokerage.Value
ws.Cells(Selection.Row, "I") = UserForm3.excess.Value
ws.Cells(Selection.Row, "J") = UserForm3.cedent.Value
Unload Me
On Error GoTo 0
UserForm3.Show
End Sub

Private Sub brokerage_Change()
Dim msoMetaPropertyTypeCurrency
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 excess_Change()
Dim isCurrency
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
brokerage.Value = Cells(Selection.Row, "H").Value
excess.Value = Cells(Selection.Row, "I").Value
cedent.Value = Cells(Selection.Row, "J").Value
End Sub

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
 
Upvote 0
MyForcey said:
Unless there's a better way to do it?
Click to expand...
This really belongs in a database, I think. That way you could have one table for the individual risks/policies, another for the insurers, and then a third that has multiple rows for each policy (one for each market with their relevant share). Your current version will be OK as a simple log of policies but will make your life much harder than it needs to be in terms of any reporting. Even if you simply replicated all the policy data on separate lines for each reinsurer, that would simplify things.
 
Upvote 0
RoryA said:
This really belongs in a database, I think. That way you could have one table for the individual risks/policies, another for the insurers, and then a third that has multiple rows for each policy (one for each market with their relevant share). Your current version will be OK as a simple log of policies but will make your life much harder than it needs to be in terms of any reporting. Even if you simply replicated all the policy data on separate lines for each reinsurer, that would simplify things.
Click to expand...
Hi, thank you for having a look... How do I go about that? I honestly have built all of this in the space of a year from forums and searches.. I am not knowledgeable for such, however this is just a personal project I have been doing in my spare time at the office... not sure where to go from here apart from getting the current code fixed
 
Upvote 0
Can the code below be changed so that I can select the row which will populate the UserForm3 and allow me to amend/update the data.

The code below is for userForm1, I need it to apply to UserForm3 if possible?
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()
  msgValue = MsgBox("Save & Add This Risk Now?", vbYesNo + vbInformation, "Confirmation")
  If msgValue = vbNo Then Exit Sub
  reins = ""
  With Me.reinsurers
    For X = 0 To .ListCount - 1
      If .Selected(X) = True Then
        reins = reins & .List(X) & vbCrLf
      End If
    Next X
  End With
  Set newrow = tblRISK.ListRows.Add
  With newrow
    .Range(2) = Me.reference.Value
    .Range(3) = Me.insured.Value
    .Range(4) = CDate(Me.startdate.Value)
    .Range(5) = CDate(Me.enddate.Value)
    .Range(6) = Me.share.Value / 100
    .Range(7) = reins
    .Range(8) = Me.brokerage.Value
    .Range(9) = Me.excess.Value
    .Range(10) = Me.cedent.Value
  End With
  Unload Me
  UserForm1.Show
End Sub
 
Upvote 0
Hi,
as already suggested, you would be better using a database for this project.

I have had a quick glance at your project & note that you have created Two almost identical forms - one to submit & one to edit data - You only need one form to perfrom both processes but i do not have time to rewrite your project.

with regard to the editing problem,

try this update & see if helps you

VBA Code: 
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

you also need to include line shown in BOLD in the Activate event

Rich (BB code): 
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

Hope helpful

Dave
 
Upvote 0
dmt32 said:
Hi,
as already suggested, you would be better using a database for this project.

I have had a quick glance at your project & note that you have created Two almost identical forms - one to submit & one to edit data - You only need one form to perfrom both processes but i do not have time to rewrite your project.

with regard to the editing problem,

try this update & see if helps you

VBA Code: 
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

you also need to include line shown in BOLD in the Activate event

Rich (BB code): 
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

Hope helpful

Da
Click to expand...
 
Upvote 0
Hi, thank you very much for your help.

Where you asked me to add the bold part - it keeps returning an error? what's the cause?
 

Attachments

  • Picture1.png
    Picture1.png
    57.2 KB · Views: 11
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
192
krunal123
K
L
  • Question Question
Getting data to go to the correct columns
Replies
5
Views
285
mumps
mumps
P
  • Question Question
Listbox Filter Search
Replies
5
Views
530
Pacman52
P
L
  • Solved
Insert formulas to new lines in table
Replies
3
Views
152
Ltuk85
L
S
  • Question Question
VBA - Populate listbox in userform based on combo box selection
Replies
1
Views
399
Saber75
S

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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