New user here. I tried to use the search option here to find any similar threads but I couldn't see any results similar to mine so I'm posting this in a hope that somebody might be able to help me out with this.
I'm stuck with this for almost a week now. I have tried to read blogs and watch some YouTube videos but nothing. I do not have any knowledge of Excel VBA, let me put this out there first.
To the issue at hand. I have a UserForm4 which is MultiPage, two tabs. One is data entry and other is search edit and delete. Almost all the functions on data entry work, apart from print, save and clear. But data can be entered and all good. On the search edit delete tab I have a listbox and search function. I, for the life of me can not make the Get Data button to show the appropriate sheet. I've gotten it to the point that it works but it only shows the data of the sheet which is open in the background. I have a handful of sheet and using a combobox to activate/select the sheet in which data goes. This data entry function as mentioned above is working fine.
User select the month's name and upon selection the data entered goes in that sheet. Jan, Fab so on and so forth. This works regardless of what sheet is opened in the background. January might be opened and if combobox selection of month is Feb then data will go in Feb, no problem! Same can not be said for ListBox, it only shows data from the sheet which is opened in the background. I have tried so much and due to the lack of knowledge I'm stuck at this. I have to make the search function somehow but I can not get pass the ListBox.
I'd appreciate very much any help you guys/girls can give. I would need a code to be honest. I'd be great full if this can be achieved. I also have posted on another forum and no help. I understand and respect that. But I need this sorted and need help. So if anybody here can help me out, I'd appreciate it very much.
I can either give link of the uploaded file or post the code. I'll post the code and update with file link if somebody needs it.
Last but not least, my terminologies of excel and vba are at zero. Yes since I've started making this form I'm trying to understand too but, but to be honest and fair my background is not of excel or vba programming. I hope, I really hope this won't put anybody off from helping me lol.
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
This one I watched a video on how to get range from a sheet to listbox. Luckily the person who made the video used the exact same thing which I needed so I just copied his example and it works fine with the button. Here's that 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
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 Call cmdGetData_Click 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
I'm hoping this can be achieved and when the month is selected form the cboMonths to enter data then lstData shows up to date data and if cboMonths value is changed and that sheet is "not" opened in the worksheet, lstData still shows the selection made from cboMonths. That's all, for now. Thanks in advance for your help.
P.S. I'm using Excel 2016
[USER="20913"]StephenR[/USER] Thanks a bunch for the clean up of OP, I appreciate it. Yes I did said I asked this question elsewhere and this is the link to the cross post,