asked on

Help with Adding text from a form to a worksheet

I am new at VBA, having a hard time figuring a few things out.

Overview:
- "Master" is the main sheet I am working from
- "Material List" is just a reference sheet I want available to view
- There are (4) hidden sheets that you can unhide using Cntl+Shift+W. I don't want these viewed from the typical user (just me when needed)
- On "Master" selecting a Material from Column B drop down list pulls in values from "Material List"

Here's my problems:
- On UserForm1, the add button should add the values from the TextBox to the NEXT AVAILABLE row on "Material List" from 78 to 90.
- A user should be able to add a few new Materials to the list and then access them from the Master sheet in the dropdown list on ColumnB
- I need some math done on TextBox6 before copying to "Material". The formula is [TextBox6 * 0.0160185]
- Once the new material is added using the button on the form, you should return to cell "A10" on the sheet "Master"

Please advise how to fix or make the changes and I will look to see how you did it for future reference.

FYI, there is a little sensitive information in this sheet so I am keeping in private, your cooperation with that would be appreciated.

Thank you,
Copy-of-Dryer-Calculator---JWC-Rev-.xlsm

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jon Carlson

ASKER

This worked great, thank you for your help!!

Jon Carlson

ASKER

Worked Great!

Subodh Tiwari (Neeraj)

You're welcome Jon! Glad it worked.

Roy Cox

Because you have not changed the names of your TextBoxes and kept TextBox1,TextBox2 etc. you can use this trick. By using this method it reduces typing and if you need to add a column and corresponding TextBox the code can simply be amended by changing the 8 to the new number of TextBoxes.

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim lr As Long
    Dim iX As Integer
    Dim Ctl As Control

    Set ws = Sheets("Materials")
    With ws
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        'Add Data to Materials
        For iX = 1 To 8
            .Cells(lr, iX) = Me("TextBox" & iX)
            .Cells(lr, 6).Value = .Cells(lr, 6).Value * 0.0160185
        Next iX
    End With

    'Clearing the TextBoxes after submitting the data
    For Each Ctl In Me.Controls
        If TypeOf Ctl Is MSForms.TextBox Then Ctl = Empty
    Next Ctl
End Sub


Private Sub CommandButton2_Click()
    Me.Hide
    Application.Goto Sheets("Master").Range("M10"), Scroll:=True
End Sub

Open in new window