Hello,
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
Display More
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
Display More
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
Display More
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
Display More
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.
Cheers.
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,