Listbox not changing content after combobox selection has changed.

W

Windy borough

New Member
Joined
Nov 3, 2018
Messages
35
Hi,

I need some help with my vba code for excel 2016. I have a listbox (named lstData) which displays/fetches data from the workbook sheet. I managed to get Listbox to work and display data but the listbox content doesn't change when combobox selection is made. It shows the data first time but if I change the combobox to another month my listbox doesn't update.

If somebody can point out where I'm entering the code wrong then I'd appreciate it very much.

Code: 
Private Sub UserForm_Initialize()Dim wsActive As Worksheet
Dim i As Long, LastRow As Long
Set wsActive = ActiveSheet
LastRow = wsActive.Cells(wsActive.Rows.Count, "G").End(xlUp).Row


For i = 1 To ThisWorkbook.Sheets.Count
    Me.cboMonths.AddItem ThisWorkbook.Sheets(i).Name
Next


'Dim wsActive As Worksheet
'Dim i As Long, LastRow As Long
Set wsActive = ActiveSheet
'LastRow = wsActive.Cells(wsActive.Rows.Count, "G").End(xlUp).Row


'TextBox 1 carries the desired value
'Label8.Caption = "        Balance is: " & wsActive.Cells(LastRow, 7).Value


For i = 1 To ThisWorkbook.Sheets.Count
    Me.cboMonths.AddItem ThisWorkbook.Sheets(i).Name
Next
Dim Cell As Range
For Each Cell In Range("A8:F8")
    cboHeader.AddItem (Cell.Value)
Next Cell


End Sub

Code: 
Private Sub cmdGetData_Click()Dim SheetName As String
Dim ws As Worksheet
Dim LastRow As Long


SheetName = cboMonths.Value
Set ws = Sheets(SheetName)




lstData.ColumnCount = 7
lstData.RowSource = "A9:F375"


End Sub

Code: 
Private Sub cmdAdd_Click()Dim dcc As Long
 Dim abc As Worksheet, pfl As Worksheet


 Set abc = ThisWorkbook.Worksheets(Me.cboMonths.Value)
 Set pfl = Sheets("ProfitLoss")


With abc


   dcc = .Range("A" & Rows.Count).End(xlUp).Row


.Cells(dcc + 1, 1).Value = Date
.Cells(dcc + 1, 2).Value = Me.txtSource.Value
.Cells(dcc + 1, 3).Value = Me.txtRent.Value
.Cells(dcc + 1, 4).Value = Me.txtRentalAdmin.Value
.Cells(dcc + 1, 5).Value = Me.txtMiscHoldingDeposit.Value
.Cells(dcc + 1, 6).Value = Me.txtOut.Value


End With


If CheckBox1.Value Then 'this is a shorter way of writing the conditional
    With pfl
      dcc = .Range("A" & Rows.Count).End(xlUp).Row
      .Cells(dcc + 1, 1).Value = Date
      .Cells(dcc + 1, 2).Value = Me.txtSource.Value
      .Cells(dcc + 1, 3).Value = Me.txtRent.Value
      '.Cells(dcc + 1, 4).Value = Me.TextBox3.Value
      '.Cells(dcc + 1, 5).Value = Me.TextBox4.Value
      '.Cells(dcc + 1, 6).Value = Me.TextBox5.Value
    End With
      End If
End Sub

Code: 
Private Sub cboMonths_Change()Dim SheetName As String
Dim ws As Worksheet
Dim LastRow As Long


SheetName = cboMonths.Value
Set ws = Sheets(SheetName)


LastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
LabelBalance.Caption = "Balance is: " & ws.Cells(LastRow, 7).Value


End Sub

Combobox named cboMonths is the main which sets the sheet where data goes. Based on number of months there are many sheets. lstData is my listbox which shows only the sheet which is opened in the background and not working on the selected month from the cboMonths. I would like it to show the months data if cboMonths selection is changed regardless of which sheet is opened in excel. Data entry works fine, I can have january 2019 open in the background and I can select February 2019 from cboMonths and data is entered correctly. It's the lstData (listbox) is not working in accordance with cboMonths. Am I entering the wrong vba code? Entering in the wrong section? I'd appreciate it if someone can help me with this.

Cheers.
 
Sort by date Sort by votes
@Akuini I got it working to the point that where it is no longer giving the run time error permission denied.

I went through your comment again and tried to read and understand the code which I pasted. You were right that the code was there twice, hence the error. For some odd reason every form stopped working and wasn't accepting the date field. Even the form which I had on my laptop wasn't working. Weird I know. Long story short, I downloaded the file from the link which I posted here, lol I know. Then had to redesign the form and all that. Added code, minus the one which you mentioned in the morning and ListBox is now working with the change of ComboBox selection/value. Phew. It took me whole day but with your helpful comment and pointer, also the line of vba code which you gave me, it's working and doing what it's suppose to do.

Only two minor things, one the ListBox is not auto updating, have to change to another selection and then back to where changed it from and then it updates the ListBox. The date format is different from the format on the sheet. I have the setting set to UK, DD/MM/YYYY but ListBox is displaying MM/DD/YYYY and mouse scroll isn't working. Is there any possible way to make them work? Too much of a code is required to do this? But regardless of this, I thank you for your help and time, and for the code, form is in working condition and can be used to add data at least.

If you can or have the code to fix/solve the minor things which I mentioned above, please let me know. I'd appreciate it very much. I already do for your help!

Cheers.


Akuini said:
I think it has something to do with Private Sub cmdGetData_Click
What is this sub supposed to do? You populate the listbox again there .
I think I need you to explain the work flow here step by step. I mean what you're trying to do when you open the userform.
Click to expand...
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Windy borough said:
@Akuini I got it working to the point that where it is no longer giving the run time error permission denied.

Only two minor things, one the ListBox is not auto updating, have to change to another selection and then back to where changed it from and then it updates the ListBox. The date format is different from the format on the sheet. I have the setting set to UK, DD/MM/YYYY but ListBox is displaying MM/DD/YYYY and mouse scroll isn't working. Is there any possible way to make them work? Too much of a code is required to do this? But regardless of this, I thank you for your help and time, and for the code, form is in working condition and can be used to add data at least.

If you can or have the code to fix/solve the minor things which I mentioned above, please let me know. I'd appreciate it very much. I already do for your help!

Cheers.
Click to expand...

Could you upload the last modified file?
 
Upvote 0
Akuini said:
Could you upload the last modified file?
Click to expand...

@Akuini Yes the link of the modified file is this.

Add button is working and I can add data. I had to think of a way around for the header columns and names not showing in them. Well you will see when you download the file. It's the best I could think and make do kinda thing.

Thanks for your reply and let me know what you think. I appreciate your help.

Cheers.
 
Upvote 0
Windy borough said:
@Akuini Yes the link of the modified file is this.

Add button is working and I can add data. I had to think of a way around for the header columns and names not showing in them. Well you will see when you download the file. It's the best I could think and make do kinda thing.

Thanks for your reply and let me know what you think. I appreciate your help.

Cheers.
Click to expand...

This is what I got so far.
I manage to make these process to work:
Load list to combobox & Listbox, ADD, EDIT,DELETE.
Note: to populate the listbox I'm using an array not Rowsource. See 'Sub changeList'.

Try & play with it, if you're ok with this approach we can try to do the rest.

The file:

https://www.dropbox.com/s/ni5h1jqc194l4zw/LMP DE Most Updated (userform4) - 03.xlsm?dl=0

Code: 
[B][COLOR=Royalblue]Sub[/COLOR][/B] changeList(idx [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B])

[B][COLOR=Royalblue]Dim[/COLOR][/B] wn [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]String[/COLOR][/B], va [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]

    [B][COLOR=Royalblue]If[/COLOR][/B] cboMonths.Value = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] MsgBox [COLOR=brown]"Pick a name"[/COLOR]:    [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
    
    [B][COLOR=Royalblue]With[/COLOR][/B] ThisWorkbook.Sheets(cboMonths.Value)
       va = .Range([COLOR=brown]"A8:F"[/COLOR] & .Cells(.Rows.Count, [COLOR=brown]"A"[/COLOR]).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row).Value
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]

    [I][COLOR=seagreen]'set the date format as "dd-mm-yyyy"[/COLOR][/I]
    [B][COLOR=Royalblue]For[/COLOR][/B] i = [COLOR=crimson]2[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]1[/COLOR])
        va(i, [COLOR=crimson]1[/COLOR]) = Format(va(i, [COLOR=crimson]1[/COLOR]), [COLOR=brown]"dd-mm-yyyy"[/COLOR])
    [B][COLOR=Royalblue]Next[/COLOR][/B]

    [I][COLOR=seagreen]'populate the listbox[/COLOR][/I]
    lstData.List = va
    [I][COLOR=seagreen]'select a row in listbox[/COLOR][/I]
    Me.lstData.ListIndex = idx
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdClear_Click()
lstData.Clear
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdDelete_Click()
[I][COLOR=seagreen]'DELETE ENTRY BUTTON[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] r [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], ans [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] rng [B][COLOR=Royalblue]As[/COLOR][/B] Range

ans = MsgBox([COLOR=brown]"Are you sure to delete?"[/COLOR], vbOKCancel)
[B][COLOR=Royalblue]If[/COLOR][/B] ans = vbCancel [B][COLOR=Royalblue]Then[/COLOR][/B] [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

    [B][COLOR=Royalblue]With[/COLOR][/B] ThisWorkbook.Sheets(cboMonths.Value)
   
        [B][COLOR=Royalblue]If[/COLOR][/B] Me.lstData.ListIndex = -[COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            MsgBox [COLOR=brown]" No selection made"[/COLOR]
        [B][COLOR=Royalblue]ElseIf[/COLOR][/B] Me.lstData.ListIndex >= [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            r = Me.lstData.ListIndex + [COLOR=crimson]8[/COLOR]
            [I][COLOR=seagreen]'delete row in sheet[/COLOR][/I]
            .Rows(r).EntireRow.Delete
           
            [I][COLOR=seagreen]'change list in listbox[/COLOR][/I]
             [B][COLOR=Royalblue]Call[/COLOR][/B] changeList([COLOR=crimson]1[/COLOR])

        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdEdit_Click()
[I][COLOR=seagreen]'EDIT ENTRY BUTTON[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] r [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] rng [B][COLOR=Royalblue]As[/COLOR][/B] Range

    [B][COLOR=Royalblue]If[/COLOR][/B] cboMonths.Value = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] MsgBox [COLOR=brown]"Pick a name"[/COLOR]:    [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
    

    [B][COLOR=Royalblue]With[/COLOR][/B] ThisWorkbook.Sheets(cboMonths.Value)
   
        [B][COLOR=Royalblue]If[/COLOR][/B] Me.lstData.ListIndex = -[COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            MsgBox [COLOR=brown]" No selection made"[/COLOR]
        [B][COLOR=Royalblue]ElseIf[/COLOR][/B] Me.lstData.ListIndex >= [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            r = Me.lstData.ListIndex + [COLOR=crimson]8[/COLOR]
           
            [I][COLOR=seagreen]'change data in sheet[/COLOR][/I]
            .Cells(r, [COLOR=crimson]2[/COLOR]) = Me.Emp2.Text
            .Cells(r, [COLOR=crimson]3[/COLOR]) = Me.Emp3.Text
            .Cells(r, [COLOR=crimson]4[/COLOR]) = Me.Emp4.Text
            .Cells(r, [COLOR=crimson]5[/COLOR]) = Me.Emp5.Text
            .Cells(r, [COLOR=crimson]6[/COLOR]) = Me.Emp7.Text
           
            [I][COLOR=seagreen]'change list in listbox[/COLOR][/I]
             [B][COLOR=Royalblue]Call[/COLOR][/B] changeList(r - [COLOR=crimson]8[/COLOR])

        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] UserForm_Initialize()
[B][COLOR=Royalblue]Dim[/COLOR][/B] wsActive [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], LastRow [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]


lstData.ColumnCount = [COLOR=crimson]6[/COLOR]
[I][COLOR=seagreen]'set each column width of lstData[/COLOR][/I]
lstData.ColumnWidths = [COLOR=brown]"60,200,60,100,100,60"[/COLOR]


[B][COLOR=Royalblue]For[/COLOR][/B] i = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] ThisWorkbook.Sheets.Count
    Me.cboMonths.AddItem ThisWorkbook.Sheets(i).Name
[B][COLOR=Royalblue]Next[/COLOR][/B]

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] lstData_Click()
[B][COLOR=Royalblue]Dim[/COLOR][/B] n [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
    [B][COLOR=Royalblue]If[/COLOR][/B] Me.lstData.ListIndex = -[COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
        MsgBox [COLOR=brown]" No selection made"[/COLOR]
    [B][COLOR=Royalblue]ElseIf[/COLOR][/B] Me.lstData.ListIndex >= [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
        n = Me.lstData.ListIndex
            Me.Emp2.Text = lstData.List(n, [COLOR=crimson]1[/COLOR])
            Me.Emp3.Text = lstData.List(n, [COLOR=crimson]2[/COLOR])
            Me.Emp4.Text = lstData.List(n, [COLOR=crimson]3[/COLOR])
            Me.Emp5.Text = lstData.List(n, [COLOR=crimson]4[/COLOR])
            [I][COLOR=seagreen]'Me.Emp6.Text = in ??[/COLOR][/I]
            Me.Emp7.Text = lstData.List(n, [COLOR=crimson]5[/COLOR])

    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cboMonths_Change()
[B][COLOR=Royalblue]Dim[/COLOR][/B] ws [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet
[B][COLOR=Royalblue]Dim[/COLOR][/B] LastRow [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]

[B][COLOR=Royalblue]Set[/COLOR][/B] ws = Sheets(cboMonths.Value)

LastRow = ws.Cells(ws.Rows.Count, [COLOR=brown]"G"[/COLOR]).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row
LabelBalance.Caption = [COLOR=brown]"Balance is: "[/COLOR] & ws.Cells(LastRow, [COLOR=crimson]7[/COLOR]).Value

[B][COLOR=Royalblue]Call[/COLOR][/B] changeList([COLOR=crimson]1[/COLOR])

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdAdd_Click()
[B][COLOR=Royalblue]Dim[/COLOR][/B] dcc [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
 [B][COLOR=Royalblue]Dim[/COLOR][/B] abc [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet, pfl [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet

 [B][COLOR=Royalblue]If[/COLOR][/B] cboMonths.Value = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] MsgBox [COLOR=brown]"Pick a name"[/COLOR]:    [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
 
 [B][COLOR=Royalblue]Set[/COLOR][/B] abc = ThisWorkbook.Worksheets(Me.cboMonths.Value)
 [B][COLOR=Royalblue]Set[/COLOR][/B] pfl = Sheets([COLOR=brown]"ProfitLoss"[/COLOR])

[B][COLOR=Royalblue]With[/COLOR][/B] abc

   dcc = .Range([COLOR=brown]"A"[/COLOR] & .Rows.Count).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row + [COLOR=crimson]1[/COLOR]

    .Cells(dcc, [COLOR=crimson]1[/COLOR]).Value = [B][COLOR=Royalblue]Date[/COLOR][/B]
    .Cells(dcc, [COLOR=crimson]2[/COLOR]).Value = Me.txtSource.Value
    .Cells(dcc, [COLOR=crimson]3[/COLOR]).Value = Me.txtRent.Value
    .Cells(dcc, [COLOR=crimson]4[/COLOR]).Value = Me.txtRentalAdmin.Value
    .Cells(dcc, [COLOR=crimson]5[/COLOR]).Value = Me.txtMiscHoldingDeposit.Value
    .Cells(dcc, [COLOR=crimson]6[/COLOR]).Value = Me.txtOut.Value

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
      
[B][COLOR=Royalblue]Call[/COLOR][/B] changeList(dcc - [COLOR=crimson]8[/COLOR])
      

[B][COLOR=Royalblue]If[/COLOR][/B] CheckBox1.Value [B][COLOR=Royalblue]Then[/COLOR][/B] [I][COLOR=seagreen]'this is a shorter way of writing the conditional[/COLOR][/I]
    [B][COLOR=Royalblue]With[/COLOR][/B] pfl
      dcc = .Range([COLOR=brown]"A"[/COLOR] & Rows.Count).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row + [COLOR=crimson]1[/COLOR]
      .Cells(dcc, [COLOR=crimson]1[/COLOR]).Value = [B][COLOR=Royalblue]Date[/COLOR][/B]
      .Cells(dcc, [COLOR=crimson]2[/COLOR]).Value = Me.txtSource.Value
      .Cells(dcc, [COLOR=crimson]3[/COLOR]).Value = Me.txtRent.Value
      [I][COLOR=seagreen]'.Cells(DCC, 4).Value = Me.TextBox3.Value[/COLOR][/I]
      [I][COLOR=seagreen]'.Cells(DCC, 5).Value = Me.TextBox4.Value[/COLOR][/I]
      [I][COLOR=seagreen]'.Cells(DCC, 6).Value = Me.TextBox5.Value[/COLOR][/I]
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
      
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdGetData_Click()
    [I][COLOR=seagreen]'change list in listbox[/COLOR][/I]
     [B][COLOR=Royalblue]Call[/COLOR][/B] changeList([COLOR=crimson]1[/COLOR])

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
 
Last edited:
Upvote 0
Akuini said:
This is what I got so far.
I manage to make these process to work:
Load list to combobox & Listbox, ADD, EDIT,DELETE.
Note: to populate the listbox I'm using an array not Rowsource. See 'Sub changeList'.

Try & play with it, if you're ok with this approach we can try to do the rest.

The file:

https://www.dropbox.com/s/ni5h1jqc194l4zw/LMP DE Most Updated (userform4) - 03.xlsm?dl=0

Code: 
[B][COLOR=Royalblue]Sub[/COLOR][/B] changeList(idx [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B])

[B][COLOR=Royalblue]Dim[/COLOR][/B] wn [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]String[/COLOR][/B], va [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]

    [B][COLOR=Royalblue]If[/COLOR][/B] cboMonths.Value = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] MsgBox [COLOR=brown]"Pick a name"[/COLOR]:    [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
    
    [B][COLOR=Royalblue]With[/COLOR][/B] ThisWorkbook.Sheets(cboMonths.Value)
       va = .Range([COLOR=brown]"A8:F"[/COLOR] & .Cells(.Rows.Count, [COLOR=brown]"A"[/COLOR]).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row).Value
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]

    [I][COLOR=seagreen]'set the date format as "dd-mm-yyyy"[/COLOR][/I]
    [B][COLOR=Royalblue]For[/COLOR][/B] i = [COLOR=crimson]2[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]1[/COLOR])
        va(i, [COLOR=crimson]1[/COLOR]) = Format(va(i, [COLOR=crimson]1[/COLOR]), [COLOR=brown]"dd-mm-yyyy"[/COLOR])
    [B][COLOR=Royalblue]Next[/COLOR][/B]

    [I][COLOR=seagreen]'populate the listbox[/COLOR][/I]
    lstData.List = va
    [I][COLOR=seagreen]'select a row in listbox[/COLOR][/I]
    Me.lstData.ListIndex = idx
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdClear_Click()
lstData.Clear
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdDelete_Click()
[I][COLOR=seagreen]'DELETE ENTRY BUTTON[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] r [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], ans [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] rng [B][COLOR=Royalblue]As[/COLOR][/B] Range

ans = MsgBox([COLOR=brown]"Are you sure to delete?"[/COLOR], vbOKCancel)
[B][COLOR=Royalblue]If[/COLOR][/B] ans = vbCancel [B][COLOR=Royalblue]Then[/COLOR][/B] [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

    [B][COLOR=Royalblue]With[/COLOR][/B] ThisWorkbook.Sheets(cboMonths.Value)
   
        [B][COLOR=Royalblue]If[/COLOR][/B] Me.lstData.ListIndex = -[COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            MsgBox [COLOR=brown]" No selection made"[/COLOR]
        [B][COLOR=Royalblue]ElseIf[/COLOR][/B] Me.lstData.ListIndex >= [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            r = Me.lstData.ListIndex + [COLOR=crimson]8[/COLOR]
            [I][COLOR=seagreen]'delete row in sheet[/COLOR][/I]
            .Rows(r).EntireRow.Delete
           
            [I][COLOR=seagreen]'change list in listbox[/COLOR][/I]
             [B][COLOR=Royalblue]Call[/COLOR][/B] changeList([COLOR=crimson]1[/COLOR])

        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdEdit_Click()
[I][COLOR=seagreen]'EDIT ENTRY BUTTON[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] r [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] rng [B][COLOR=Royalblue]As[/COLOR][/B] Range

    [B][COLOR=Royalblue]If[/COLOR][/B] cboMonths.Value = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] MsgBox [COLOR=brown]"Pick a name"[/COLOR]:    [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
    

    [B][COLOR=Royalblue]With[/COLOR][/B] ThisWorkbook.Sheets(cboMonths.Value)
   
        [B][COLOR=Royalblue]If[/COLOR][/B] Me.lstData.ListIndex = -[COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            MsgBox [COLOR=brown]" No selection made"[/COLOR]
        [B][COLOR=Royalblue]ElseIf[/COLOR][/B] Me.lstData.ListIndex >= [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            r = Me.lstData.ListIndex + [COLOR=crimson]8[/COLOR]
           
            [I][COLOR=seagreen]'change data in sheet[/COLOR][/I]
            .Cells(r, [COLOR=crimson]2[/COLOR]) = Me.Emp2.Text
            .Cells(r, [COLOR=crimson]3[/COLOR]) = Me.Emp3.Text
            .Cells(r, [COLOR=crimson]4[/COLOR]) = Me.Emp4.Text
            .Cells(r, [COLOR=crimson]5[/COLOR]) = Me.Emp5.Text
            .Cells(r, [COLOR=crimson]6[/COLOR]) = Me.Emp7.Text
           
            [I][COLOR=seagreen]'change list in listbox[/COLOR][/I]
             [B][COLOR=Royalblue]Call[/COLOR][/B] changeList(r - [COLOR=crimson]8[/COLOR])

        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] UserForm_Initialize()
[B][COLOR=Royalblue]Dim[/COLOR][/B] wsActive [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], LastRow [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]


lstData.ColumnCount = [COLOR=crimson]6[/COLOR]
[I][COLOR=seagreen]'set each column width of lstData[/COLOR][/I]
lstData.ColumnWidths = [COLOR=brown]"60,200,60,100,100,60"[/COLOR]


[B][COLOR=Royalblue]For[/COLOR][/B] i = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] ThisWorkbook.Sheets.Count
    Me.cboMonths.AddItem ThisWorkbook.Sheets(i).Name
[B][COLOR=Royalblue]Next[/COLOR][/B]

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] lstData_Click()
[B][COLOR=Royalblue]Dim[/COLOR][/B] n [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
    [B][COLOR=Royalblue]If[/COLOR][/B] Me.lstData.ListIndex = -[COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
        MsgBox [COLOR=brown]" No selection made"[/COLOR]
    [B][COLOR=Royalblue]ElseIf[/COLOR][/B] Me.lstData.ListIndex >= [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
        n = Me.lstData.ListIndex
            Me.Emp2.Text = lstData.List(n, [COLOR=crimson]1[/COLOR])
            Me.Emp3.Text = lstData.List(n, [COLOR=crimson]2[/COLOR])
            Me.Emp4.Text = lstData.List(n, [COLOR=crimson]3[/COLOR])
            Me.Emp5.Text = lstData.List(n, [COLOR=crimson]4[/COLOR])
            [I][COLOR=seagreen]'Me.Emp6.Text = in ??[/COLOR][/I]
            Me.Emp7.Text = lstData.List(n, [COLOR=crimson]5[/COLOR])

    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cboMonths_Change()
[B][COLOR=Royalblue]Dim[/COLOR][/B] ws [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet
[B][COLOR=Royalblue]Dim[/COLOR][/B] LastRow [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]

[B][COLOR=Royalblue]Set[/COLOR][/B] ws = Sheets(cboMonths.Value)

LastRow = ws.Cells(ws.Rows.Count, [COLOR=brown]"G"[/COLOR]).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row
LabelBalance.Caption = [COLOR=brown]"Balance is: "[/COLOR] & ws.Cells(LastRow, [COLOR=crimson]7[/COLOR]).Value

[B][COLOR=Royalblue]Call[/COLOR][/B] changeList([COLOR=crimson]1[/COLOR])

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdAdd_Click()
[B][COLOR=Royalblue]Dim[/COLOR][/B] dcc [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
 [B][COLOR=Royalblue]Dim[/COLOR][/B] abc [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet, pfl [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet

 [B][COLOR=Royalblue]If[/COLOR][/B] cboMonths.Value = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] MsgBox [COLOR=brown]"Pick a name"[/COLOR]:    [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
 
 [B][COLOR=Royalblue]Set[/COLOR][/B] abc = ThisWorkbook.Worksheets(Me.cboMonths.Value)
 [B][COLOR=Royalblue]Set[/COLOR][/B] pfl = Sheets([COLOR=brown]"ProfitLoss"[/COLOR])

[B][COLOR=Royalblue]With[/COLOR][/B] abc

   dcc = .Range([COLOR=brown]"A"[/COLOR] & .Rows.Count).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row + [COLOR=crimson]1[/COLOR]

    .Cells(dcc, [COLOR=crimson]1[/COLOR]).Value = [B][COLOR=Royalblue]Date[/COLOR][/B]
    .Cells(dcc, [COLOR=crimson]2[/COLOR]).Value = Me.txtSource.Value
    .Cells(dcc, [COLOR=crimson]3[/COLOR]).Value = Me.txtRent.Value
    .Cells(dcc, [COLOR=crimson]4[/COLOR]).Value = Me.txtRentalAdmin.Value
    .Cells(dcc, [COLOR=crimson]5[/COLOR]).Value = Me.txtMiscHoldingDeposit.Value
    .Cells(dcc, [COLOR=crimson]6[/COLOR]).Value = Me.txtOut.Value

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
      
[B][COLOR=Royalblue]Call[/COLOR][/B] changeList(dcc - [COLOR=crimson]8[/COLOR])
      

[B][COLOR=Royalblue]If[/COLOR][/B] CheckBox1.Value [B][COLOR=Royalblue]Then[/COLOR][/B] [I][COLOR=seagreen]'this is a shorter way of writing the conditional[/COLOR][/I]
    [B][COLOR=Royalblue]With[/COLOR][/B] pfl
      dcc = .Range([COLOR=brown]"A"[/COLOR] & Rows.Count).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row + [COLOR=crimson]1[/COLOR]
      .Cells(dcc, [COLOR=crimson]1[/COLOR]).Value = [B][COLOR=Royalblue]Date[/COLOR][/B]
      .Cells(dcc, [COLOR=crimson]2[/COLOR]).Value = Me.txtSource.Value
      .Cells(dcc, [COLOR=crimson]3[/COLOR]).Value = Me.txtRent.Value
      [I][COLOR=seagreen]'.Cells(DCC, 4).Value = Me.TextBox3.Value[/COLOR][/I]
      [I][COLOR=seagreen]'.Cells(DCC, 5).Value = Me.TextBox4.Value[/COLOR][/I]
      [I][COLOR=seagreen]'.Cells(DCC, 6).Value = Me.TextBox5.Value[/COLOR][/I]
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
      
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdGetData_Click()
    [I][COLOR=seagreen]'change list in listbox[/COLOR][/I]
     [B][COLOR=Royalblue]Call[/COLOR][/B] changeList([COLOR=crimson]1[/COLOR])

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
Click to expand...

@Akuini Thanks, thanks a bunch actually! I will download and play around with it in an hour or so. I will update here and I can not thank you enough for doing this, I really can not. I appreciate your time and help. Enjoy your weenkend! Cheers.
 
Upvote 0
Akuini said:
This is what I got so far.
I manage to make these process to work:
Load list to combobox & Listbox, ADD, EDIT,DELETE.
Note: to populate the listbox I'm using an array not Rowsource. See 'Sub changeList'.

Try & play with it, if you're ok with this approach we can try to do the rest.

The file:

https://www.dropbox.com/s/ni5h1jqc194l4zw/LMP DE Most Updated (userform4) - 03.xlsm?dl=0

Code: 
[B][COLOR=Royalblue]Sub[/COLOR][/B] changeList(idx [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B])

[B][COLOR=Royalblue]Dim[/COLOR][/B] wn [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]String[/COLOR][/B], va [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]

    [B][COLOR=Royalblue]If[/COLOR][/B] cboMonths.Value = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] MsgBox [COLOR=brown]"Pick a name"[/COLOR]:    [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
    
    [B][COLOR=Royalblue]With[/COLOR][/B] ThisWorkbook.Sheets(cboMonths.Value)
       va = .Range([COLOR=brown]"A8:F"[/COLOR] & .Cells(.Rows.Count, [COLOR=brown]"A"[/COLOR]).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row).Value
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]

    [I][COLOR=seagreen]'set the date format as "dd-mm-yyyy"[/COLOR][/I]
    [B][COLOR=Royalblue]For[/COLOR][/B] i = [COLOR=crimson]2[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]1[/COLOR])
        va(i, [COLOR=crimson]1[/COLOR]) = Format(va(i, [COLOR=crimson]1[/COLOR]), [COLOR=brown]"dd-mm-yyyy"[/COLOR])
    [B][COLOR=Royalblue]Next[/COLOR][/B]

    [I][COLOR=seagreen]'populate the listbox[/COLOR][/I]
    lstData.List = va
    [I][COLOR=seagreen]'select a row in listbox[/COLOR][/I]
    Me.lstData.ListIndex = idx
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdClear_Click()
lstData.Clear
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdDelete_Click()
[I][COLOR=seagreen]'DELETE ENTRY BUTTON[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] r [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], ans [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] rng [B][COLOR=Royalblue]As[/COLOR][/B] Range

ans = MsgBox([COLOR=brown]"Are you sure to delete?"[/COLOR], vbOKCancel)
[B][COLOR=Royalblue]If[/COLOR][/B] ans = vbCancel [B][COLOR=Royalblue]Then[/COLOR][/B] [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

    [B][COLOR=Royalblue]With[/COLOR][/B] ThisWorkbook.Sheets(cboMonths.Value)
   
        [B][COLOR=Royalblue]If[/COLOR][/B] Me.lstData.ListIndex = -[COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            MsgBox [COLOR=brown]" No selection made"[/COLOR]
        [B][COLOR=Royalblue]ElseIf[/COLOR][/B] Me.lstData.ListIndex >= [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            r = Me.lstData.ListIndex + [COLOR=crimson]8[/COLOR]
            [I][COLOR=seagreen]'delete row in sheet[/COLOR][/I]
            .Rows(r).EntireRow.Delete
           
            [I][COLOR=seagreen]'change list in listbox[/COLOR][/I]
             [B][COLOR=Royalblue]Call[/COLOR][/B] changeList([COLOR=crimson]1[/COLOR])

        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdEdit_Click()
[I][COLOR=seagreen]'EDIT ENTRY BUTTON[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] r [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] rng [B][COLOR=Royalblue]As[/COLOR][/B] Range

    [B][COLOR=Royalblue]If[/COLOR][/B] cboMonths.Value = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] MsgBox [COLOR=brown]"Pick a name"[/COLOR]:    [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
    

    [B][COLOR=Royalblue]With[/COLOR][/B] ThisWorkbook.Sheets(cboMonths.Value)
   
        [B][COLOR=Royalblue]If[/COLOR][/B] Me.lstData.ListIndex = -[COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            MsgBox [COLOR=brown]" No selection made"[/COLOR]
        [B][COLOR=Royalblue]ElseIf[/COLOR][/B] Me.lstData.ListIndex >= [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            r = Me.lstData.ListIndex + [COLOR=crimson]8[/COLOR]
           
            [I][COLOR=seagreen]'change data in sheet[/COLOR][/I]
            .Cells(r, [COLOR=crimson]2[/COLOR]) = Me.Emp2.Text
            .Cells(r, [COLOR=crimson]3[/COLOR]) = Me.Emp3.Text
            .Cells(r, [COLOR=crimson]4[/COLOR]) = Me.Emp4.Text
            .Cells(r, [COLOR=crimson]5[/COLOR]) = Me.Emp5.Text
            .Cells(r, [COLOR=crimson]6[/COLOR]) = Me.Emp7.Text
           
            [I][COLOR=seagreen]'change list in listbox[/COLOR][/I]
             [B][COLOR=Royalblue]Call[/COLOR][/B] changeList(r - [COLOR=crimson]8[/COLOR])

        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] UserForm_Initialize()
[B][COLOR=Royalblue]Dim[/COLOR][/B] wsActive [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], LastRow [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]


lstData.ColumnCount = [COLOR=crimson]6[/COLOR]
[I][COLOR=seagreen]'set each column width of lstData[/COLOR][/I]
lstData.ColumnWidths = [COLOR=brown]"60,200,60,100,100,60"[/COLOR]


[B][COLOR=Royalblue]For[/COLOR][/B] i = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] ThisWorkbook.Sheets.Count
    Me.cboMonths.AddItem ThisWorkbook.Sheets(i).Name
[B][COLOR=Royalblue]Next[/COLOR][/B]

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] lstData_Click()
[B][COLOR=Royalblue]Dim[/COLOR][/B] n [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
    [B][COLOR=Royalblue]If[/COLOR][/B] Me.lstData.ListIndex = -[COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
        MsgBox [COLOR=brown]" No selection made"[/COLOR]
    [B][COLOR=Royalblue]ElseIf[/COLOR][/B] Me.lstData.ListIndex >= [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
        n = Me.lstData.ListIndex
            Me.Emp2.Text = lstData.List(n, [COLOR=crimson]1[/COLOR])
            Me.Emp3.Text = lstData.List(n, [COLOR=crimson]2[/COLOR])
            Me.Emp4.Text = lstData.List(n, [COLOR=crimson]3[/COLOR])
            Me.Emp5.Text = lstData.List(n, [COLOR=crimson]4[/COLOR])
            [I][COLOR=seagreen]'Me.Emp6.Text = in ??[/COLOR][/I]
            Me.Emp7.Text = lstData.List(n, [COLOR=crimson]5[/COLOR])

    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cboMonths_Change()
[B][COLOR=Royalblue]Dim[/COLOR][/B] ws [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet
[B][COLOR=Royalblue]Dim[/COLOR][/B] LastRow [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]

[B][COLOR=Royalblue]Set[/COLOR][/B] ws = Sheets(cboMonths.Value)

LastRow = ws.Cells(ws.Rows.Count, [COLOR=brown]"G"[/COLOR]).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row
LabelBalance.Caption = [COLOR=brown]"Balance is: "[/COLOR] & ws.Cells(LastRow, [COLOR=crimson]7[/COLOR]).Value

[B][COLOR=Royalblue]Call[/COLOR][/B] changeList([COLOR=crimson]1[/COLOR])

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdAdd_Click()
[B][COLOR=Royalblue]Dim[/COLOR][/B] dcc [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
 [B][COLOR=Royalblue]Dim[/COLOR][/B] abc [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet, pfl [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet

 [B][COLOR=Royalblue]If[/COLOR][/B] cboMonths.Value = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] MsgBox [COLOR=brown]"Pick a name"[/COLOR]:    [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
 
 [B][COLOR=Royalblue]Set[/COLOR][/B] abc = ThisWorkbook.Worksheets(Me.cboMonths.Value)
 [B][COLOR=Royalblue]Set[/COLOR][/B] pfl = Sheets([COLOR=brown]"ProfitLoss"[/COLOR])

[B][COLOR=Royalblue]With[/COLOR][/B] abc

   dcc = .Range([COLOR=brown]"A"[/COLOR] & .Rows.Count).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row + [COLOR=crimson]1[/COLOR]

    .Cells(dcc, [COLOR=crimson]1[/COLOR]).Value = [B][COLOR=Royalblue]Date[/COLOR][/B]
    .Cells(dcc, [COLOR=crimson]2[/COLOR]).Value = Me.txtSource.Value
    .Cells(dcc, [COLOR=crimson]3[/COLOR]).Value = Me.txtRent.Value
    .Cells(dcc, [COLOR=crimson]4[/COLOR]).Value = Me.txtRentalAdmin.Value
    .Cells(dcc, [COLOR=crimson]5[/COLOR]).Value = Me.txtMiscHoldingDeposit.Value
    .Cells(dcc, [COLOR=crimson]6[/COLOR]).Value = Me.txtOut.Value

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
      
[B][COLOR=Royalblue]Call[/COLOR][/B] changeList(dcc - [COLOR=crimson]8[/COLOR])
      

[B][COLOR=Royalblue]If[/COLOR][/B] CheckBox1.Value [B][COLOR=Royalblue]Then[/COLOR][/B] [I][COLOR=seagreen]'this is a shorter way of writing the conditional[/COLOR][/I]
    [B][COLOR=Royalblue]With[/COLOR][/B] pfl
      dcc = .Range([COLOR=brown]"A"[/COLOR] & Rows.Count).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row + [COLOR=crimson]1[/COLOR]
      .Cells(dcc, [COLOR=crimson]1[/COLOR]).Value = [B][COLOR=Royalblue]Date[/COLOR][/B]
      .Cells(dcc, [COLOR=crimson]2[/COLOR]).Value = Me.txtSource.Value
      .Cells(dcc, [COLOR=crimson]3[/COLOR]).Value = Me.txtRent.Value
      [I][COLOR=seagreen]'.Cells(DCC, 4).Value = Me.TextBox3.Value[/COLOR][/I]
      [I][COLOR=seagreen]'.Cells(DCC, 5).Value = Me.TextBox4.Value[/COLOR][/I]
      [I][COLOR=seagreen]'.Cells(DCC, 6).Value = Me.TextBox5.Value[/COLOR][/I]
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
      
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] cmdGetData_Click()
    [I][COLOR=seagreen]'change list in listbox[/COLOR][/I]
     [B][COLOR=Royalblue]Call[/COLOR][/B] changeList([COLOR=crimson]1[/COLOR])

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
Click to expand...

I just tried the file. It is how it should be, spot on! I tried all the functions and they work perfectly. I have looked at the code and Sub ChangeList and also you managed to get the date format right and column headers too. I'm lost for words to be honest. If you saw the UserForm5 then you know I was doing it the way around kinda way.

I just had dinner and I'll see how it is but to be honest I have had a look and all things work fine and haven't noticed any issues. The search with keywords is the only thing left to do to be honest. I will go through the code and fiddle around with it for sure.

I can not thank you enough. Honestly speaking I can not. I can not express enough thanks and gratitude to you for your hours of time and effort. Please do enjoy your weekend and festivities. You're a gent. Cheers.
 
Upvote 0
Windy borough said:
I just tried the file. It is how it should be, spot on! I tried all the functions and they work perfectly. I have looked at the code and Sub ChangeList and also you managed to get the date format right and column headers too. I'm lost for words to be honest. If you saw the UserForm5 then you know I was doing it the way around kinda way.

I just had dinner and I'll see how it is but to be honest I have had a look and all things work fine and haven't noticed any issues. The search with keywords is the only thing left to do to be honest. I will go through the code and fiddle around with it for sure.
Click to expand...

Ok, glad it works for you.

The listbox header actually has flaws, because it isn't really a header so it won't be seen if you scroll down far enough. But I think I can find a solution.
Searching part is actually harder to code, maybe I can spend some time to write the code for it.
I'll get back to you, maybe in Monday.

Questions:
1. Why do you need to use 2 pages in the form? I think we can use 1 page (or no page) for all the process.
2. How big is your real data? How many data rows in each sheet on average?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
432
pure vito
P
R
  • Solved
Add Filtered Data to a ListBox in Userform
Replies
5
Views
535
NoSparks
N
M
  • Solved
Display textbox from listbox - getting 381 error
Replies
4
Views
206
maiatanaka
M
C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
120
Akuini
Akuini
T
  • Question
Filter data in listbox based on three conditions by selected from combobox
Replies
1
Views
165
tubrak
T

Forum statistics

Threads
1,214,818
Messages
6,121,725
Members
449,049
Latest member
MiguekHeka

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