Use form control to edit a named range

[Ajunk]

I have a worksheet with a named range that consists of 7 rows within 2 Columns.
Ie. G:10-H:17

The user inputs data into 2 text boxes in a form, one called "material", and one called "price"
the data inputed in "material" goes in to g:10 of the range and "price" in H:10.
The user can then add more materials and prices until the range is full.

So how do I get my textboxes to put the Data in the range? And once the range is full it should unload the form control.

My understanding of vba is very basic - hence why I am struggling with this.

Thank you

 

[CalcSux78]

Welcome to the forum!

Care to share what you have so far? Until then, here's a few links on Userforms:

• UserForms in Excel VBA - UserForm Basics; Add Controls dynamically at run-time using the Add Method; UserForm Controls (A Snapshot)
• VBA UserForm - A Guide for Everyone - Excel Macro Mastery

 

[Ajunk]

Here is the Code I have, and it only includes the error handling stuff at this point. Once again, when the user inputs data into the user form and clicks on "Add Ingredient" I need the data to go into the first blank field in the Range. The user can add up to 7 ingredients before the range is full. At that time I need the userform to close. The Userform includes a Material Name and Percentage, and these are to go into the 2 columns of the range.

I'm not sure how to post screen shots, and I think that would be helpful.

Private Sub AddIngredient_Click()
If IngredientName.Text = "" Then
MsgBox ("Please enter a name for the ingredient")
IngredientName.SetFocus
Exit Sub
End If

Range("g10") = IngredientName.Text

If IsNumeric(Percentage.Text) Then
Cells(NextRow, 2) = Percentage.Text
Else: MsgBox ("This box only accepts numeric values")
Percentage.SetFocus
Exit Sub
End If


IngredientName.Text = ""
Percentage.Text = ""
IngredientName.SetFocus

 

[CalcSux78]

This method should work as long as there is no data below Row 18 in column G.

Private Sub AddIngredient_Click()
Dim r As Long        'used for sending data to worksheet


'  Validate both text boxes before sending data
If IngredientName.Text = "" Then
    MsgBox ("Please enter a name for the ingredient")
    IngredientName.SetFocus
    Exit Sub
End If


If Not IsNumeric(Percentage.Text) Then
    MsgBox ("This box only accepts numeric values")
    Percentage.SetFocus
    Exit Sub
End If


' r will be the first blank after cell G9
r = WorksheetFunction.Max(10, Cells(Rows.Count, 7).End(xlUp).Offset(1).Row)
If r > 17 Then
    Unload Me
    Exit Sub
End If


Cells(r, 7) = IngredientName.Text   'send to first blank in column G
Cells(r, 8) = Percentage.Text


IngredientName.Text = ""
Percentage.Text = ""
IngredientName.SetFocus


End Sub

To post screenshots:
-If posting a portion of the worksheet, see post #2 on HTML Maker
-if posting Code, use the # button to wrap in code tags
-If posting an image file, upload to a cloud storage site (such as DrobBox, Box, OneDrive, etc) and click on the Insert Image button on the reply ribbon. There, you'll give the URL of the image.

 

[Ajunk]

Thank you very much for the help.
For some reason this code is not adding the data to my sheet, and this may be because there is data below G18.
In addition, the Form Control unloads after I click "Add Ingredient" I do not want it to do this.

Thanks again for all of your help.