Excel VBA userform code help please.

Windy borough

New Member
Joined
Nov 3, 2018
Messages
35
Hi,

I have a workbook with 13 sheets. 12 are for each month of the year and one is P/L sheet. I have a userform working perfectly without any problems. I had to design another form or updated userform with the added functionality of search using: date, source, rent, admin, and out. I have no idea how to configure a search to be honest in a userform. That's not even the problem right now. The userform which is working fine is very basic with only two options. I tried my best to design the userform to achieve what I need it to do and to the best of my ability I think I designed it correctly. I simply copy and pasted the code from older userform to the new userform and I think, I think it should work, emphasis is on I think. Obviously it didn't worked and here I am. Looking for one or few good Samaritans to help me out with this. I'm stuck very badly.

I can not get my head around why the same code works in old userform and not in the new one. It could be that I've added a ListBox1 in new userform or it could be the Multipage1 which is the problem. But I couldn't think of any more elegant solution to what I need the form to do. Only other thing I can think of is to have multiple userforms. Which I think is not the best option because it's a tiny file with literally tiny amount of data. I'll post both new and old userform codes.


Old UserForm1 (All options working fine in this)

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

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

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

End Sub


Private Sub CommandButton1_Click()

Dim dcc As Long
Dim abc As Worksheet, pfl As Worksheet

Set abc = ThisWorkbook.Worksheets(Me.ComboBox1.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.TextBox1.Value
.Cells(dcc + 1, 3).Value = Me.TextBox2.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

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.TextBox1.Value
.Cells(dcc + 1, 3).Value = Me.TextBox2.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

TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""

End Sub

Private Sub ComboBox1_Change()

Dim SheetName As String
Dim ws As Worksheet
Dim LastRow As Long

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

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

End Sub

I have used the same code for the new UserForm2 in exact same sections and it's not working. I'd really appreciate it very much if somebody can provide the code for it. I tried to post the Screenshots of the userform(s) but it's not uploading properly. Only thing which I'm surprised is that under search tab on new userform the combobox is pulling the lists of the months. How that happened, I have no idea!

I've uploaded the file to goodle drive.

I really appreciate all the help I got just last week and I'll appreciate it very much again. Thanks in advance. Cheers.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
OK I have everything working correctly on main userform2. Month selection is working, data entered is entering where it should. Balance (cell value) showing correctly as well. So the form can be used to enter data. What's needed now is how to configure the search option. Its multipate tab1. I have no clue whatsoever how to go about it. I tried to watch a few youtube vidoes but it's no help. If anybody can have some sort of input regarding the tab1 search option I'd appreciate it very much. Thanks in advance. Cheers.
 
Upvote 0
Another update: I've got the combobox in search tab1 working as well now. Now I'm lost to be honest. Anybody please?? I need search using date, source, and amount. Selected month from the combobox is where the search will look for result. I'd appreciate it very much if anybody can help me out here. Cheers.
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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