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

[mykulpasskwa]

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.

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

 

[DanteAmor]

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.

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:

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

 

[mykulpasskwa]

This worked perfectly! Thank you so much. You moved my project up by about a month. Thank you again!

 

[DanteAmor]

This worked perfectly! Thank you so much. You moved my project up by about a month. Thank you again!

I'm glad to help you. Thanks for the feedback.