Listbox not changing content after combobox selection has changed.

[Windy borough]

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.

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

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

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

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.

 

[Windy borough]

Bump.

 

[Windy borough]

Ok here's the link to the file in case somebody wants to help out. Your help is much appreciated.

Get Data button works fine but only shows the sheet open in the background and not according to the combobox.

 

[Akuini]

Ok here's the link to the file in case somebody wants to help out. Your help is much appreciated.

Get Data button works fine but only shows the sheet open in the background and not according to the combobox.

You need to populate the listbox in the cboMonths_Change. Something like this:

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

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

lstData.ColumnCount = 7
[COLOR=#0000ff]lstData.List = ws.Range("A9:F375").Value[/COLOR]

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

End Sub

 

[Windy borough]

@Akuini thanks for the help. I'll give this a try now and see how it goes. I appreciate your help. Update in few mins here. Cheers.

 

[Windy borough]

@Akuini I tried the code, it works and it doesn't work at the same time. I'm getting run time error 70 permission denied.

What's happening is that once the selection is made from cboMonths the listbox lstData displays the relevant sheet. Works fine. I can then enter data and it updates the lstData too. But once I change the selection of cboMonths it gives run time error 70 permission denied. The lstData.List = ws.Range("A9:F375").Value line is highlighted in yellow.

Also, if I enter data in jan and don't click get data the listbox doesn't update itself. I think this might be a different function to auto update. Anyway, back to the point, if select may or any other month from cboMonths and enter the data, data is entered correctly and if I click get data then, it doesn't show the relevant sheet and it shows the first sheet which is january 2019 so cboMonths says may but lstData shows january. I can then change to june or any other value from cboMonths and then back to may and then shows correct sheet. But then get run time 70 error permission denied.

What could be causing this?

It's almost sorted, almost!

I appreciate your help and effort to make this work. I do.

Cheers.

You need to populate the listbox in the cboMonths_Change. Something like this:

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

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

lstData.ColumnCount = 7
[COLOR=#0000ff]lstData.List = ws.Range("A9:F375").Value[/COLOR]

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

End Sub

 

[Akuini]

@Akuini I tried the code, it works and it doesn't work at the same time. I'm getting run time error 70 permission denied.

What could be causing this?

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.

 

[Windy borough]

@Akuini Thanks for the reply, much appreciated.

I'm glad you asked me to explain in simple words what the userform is suppose to do, lol I can't do it in excel terms btw.

Click userform. Select month from cboMonths in which you want to enter data. Fill in the fields and data is in. Done. Option 2, Fill in the fields and click the checkbob transfer to profitloss and data is entered in selected month and selected fields are also entered in profitloss sheet. Total 13 sheets.

Why ListBox? Simply put because once the data is entered and something needs to seeing, quit the form, go to the correct sheet, scroll down and see the entry. ListBox simplifies this and gives the sheet on the form in real time. That's it.

cboMonths sets the sheet active for the data to go in. You probably know this already. So the userform is based on cboMonth becasue, because it's where it's decided where the data will be entered. January 2019 entries will go in January 2019 and once it's February 2019 user will select February 2019 for the rest of the month to enter data in the sheet. Every month then has it's own full entries and profitloss sheet has entries, selective entries. Profitloss sheet doesn't require to have every month in it so date source and amount value is entered in Profitloss.

To be fair and honest, user dmt32 mentioned that I should go with a single page sheet and then use data validation to transfer the data to another sheet. I believe his suggestion is correct. But I don't know how to go about that even if I somehow manage to make userform and a single sheet database in excel. I'll be lost then how to transfer the January 2019 data form that sheet to another sheet once the month has changed. So I made this multi sheet workbook, picking up the idea from some video/blog that it will simplify the data from the start. That was my understand when I started working on this.

I also have made a single page form without combobox for months and it will use single sheet to store data, it's not finished yet but I know that I'll be stuck once the data is entered and all that, that how to how filter and copy whole months data to another sheet in same workbook, that's going by dmt32 suggestion.

I wanted to have the ability to use search option to search using multipe values/columns and listbox is suppose to show the search results but I haven't got the code for it so it's not even started. I watched a few videos and tried to read up but problem is with all videos/blogs it's one sheet always and I have multiple (13) and a cboMonths to select/activate the selected sheet. But userform is very simple. Open select month enter data then click get data and data from the selected month on cboMonths gets shown in lstData. You then change selection from cboMonths to another month and lstData should clear itself and if june 2019 was selected either by clicking get data or just by changing to june 2019 all data from june 2019 should display in lstData and if the selection is changed again then functions cycle through this. User can enter data in jan and lstData display jan, then if change to feb lstData display feb and son and forth. Get data button can serve as refresh button if lstData doen't update in real time once the entry is made.

I can post another link of the file which I've made so far if you need to look at it and try it in real time. Thank you for your help and understanding, I appreciate it very much.

Cheers.

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.

 

[Windy borough]

You can have a look at the file here.

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.

 

[Windy borough]

OK I can not edit the OP but I should remember to post the link of the post because I asked this question somewhere else too. I tried to edit the OP but can't find the option. But the link to the other form is this.

Here's to hoping I didn't violated any rules.