VBA Finding a Term on a Hidden Sheet and Loading the Individual Fees to a Userform

mykulpasskwa

Board Regular
Joined
Mar 20, 2018
Messages
66
Good Afternoon,

I have a vba problem I just can't wrap my head around and I'd appreciate any help. I've thought about using index/match, vlookup, or find, but I just get confused the more I think about it.

The ultimate goal is to have a user double click a term which opens a userform and they can enter Fee1, Fee2, and Fee3 into text boxes which saves to the very hidden Data page. If there were fees previously entered then they load into their respective textboxes within the user form, and if the user enters a new number then it updates the range on the Data page.

What I can't figure out is if the user clicks a term (e.g. 5678) on the Dash sheet, then I need vba to find term 5678 on the Data page and load the offset fees into the user form. The Dash sheet has the total of the Fees but the Data sheet contains the breakdown of that total.

Goal:
User double clicks a term which opens a userform that has individual fees. When the form is closed the sum of the fees are stored on the Dash sheet and the individual fees are saved on the Data sheet. When the user again double clicks the term, vba finds the cell that was double clicked on the fees page and loads the individual fees into the appropriate textbox. The user can then modify and/or close the form with the entered fees.
JFp3H1K


I can't seem to get the picture to load, but here is the link to a couple of screenshots. I put the numbers in the userform, but they currently don't save or load to/from anything:
https://imgur.com/a/JFp3H1K
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try thisPut the siguienet code in the events of your Dash sheet
-----
SHEET EVENT

Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        UserForm1.Show
        Cancel = True
    End If
End Sub

-----
Code in userform1:

Code:
Dim sh2 As Worksheet, f As Range    'Up to the code


Private Sub CommandButton1_Click()
    f.Offset(, 1).Value = TextBox1.Value
    f.Offset(, 2).Value = TextBox2.Value
    f.Offset(, 3).Value = TextBox3.Value
    ActiveCell.Offset(, 2).Value = Val(TextBox1.Value) + Val(TextBox2.Value) + Val(TextBox3.Value)
    Unload Me
End Sub


Private Sub UserForm_Activate()
    Set sh2 = Sheets("[COLOR=#0000ff]Data[/COLOR]")
    Set f = sh2.Range("B:B").Find(ActiveCell.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        TextBox1.Value = f.Offset(, 1).Value
        TextBox2.Value = f.Offset(, 2).Value
        TextBox3.Value = f.Offset(, 3).Value
    Else
        MsgBox "Data does not exists"
        Unload Me
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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