Form Control is Not updating range

[Ajunk]

I have a user form that accepts user input and is supposed to put it on a worksheet. The form control has 2 textboxes, and IngredientName and Percentage. The user enters info into these boxes and clicks "Add Ingredient". This is supposed to place IngredientName in Cell G10, and Percentage in Cell H10. It isn't doing this.
Furthermore, the user must be able to add ingredients until Cells G:H16 are full, then unload the form. It is unloading the Form every time the AddIngredient is clicked.


Please help, here is the code.

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

 

[My Aswer Is This]

So your saving rows 1 to 16 of column "G" are empty and the script is still not working?
If you leave both textboxes empty and press the button do you get a message box popping up?

And your sure column "G" has no data in it?

You need to remove all data from column "G" and then run script and see what happens.

 

[Ajunk]

I am going to try your recommendations at home, I will have to build a new form control but will copy my code from my original post.

Thanks again for dealing with my incompetence.

 

[Ajunk]

I made the userForm and the Script works perfectly - But I don't know why it is not working on the other spreadsheet.

There is data in columns G and H, but it is below that range that the UserForm edits. Does this matter?

If so, I must maintain the data in these columns.

Also, just added some data to Columns G and H and the UserForm doesn't edit the range.

So how do I maintain data below these ranges and make the Form Work?

Thanks again.

 

[My Aswer Is This]

What this script is doing is it's counting to see how many rows in column "G" have data.
And if 16 rows already have data then it will not do any more updating. It does not matter where on the column the data is.

So you tell me what your wanting to do.

Are you saying you only want to load data into rows 1 through 16 or what?

 

[Ajunk]

Yes, actually, I only want to load data into Rows 10 -16.

And I must maintain the data that is in rows 17 and below.

 

[My Aswer Is This]

That may be interesting. I will have to think about that some.

 

[My Aswer Is This]

Would you always want to start on row 10?
What about if you already have data on row 10?

 

[Ajunk]

Yes always start on row 10.
The command button that shows the Userform also clears the contents of rows 10-16, thereby allowing the first blank row to be 10.

 

[My Aswer Is This]

I will get back with you tomorrow.

 

[My Aswer Is This]

Try this:
There may be another way but if there is I don't have a better answer.
I need you to put another Textbox on your UserForm and name it "Maximum"
And this textbox will be used to keep up with what row to put the values in.
It will always start on row 10 and row 16 will be the lastrow your able to enter values into.
I have it set so you will not see the TextBox but it will be there it will set to not visible

And then use the below two scripts in your Userform.

The initialize code will always set the new Textbox named "Maximum" to 10 to begin with when we open the Userform.

Private Sub AddIngredient_Click()
Dim r As Long        'used for sending data to worksheet
r = Maximum.Value  'New part added
'  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
Cells(r, 7) = IngredientName.Text   'send to first blank in column G
Cells(r, 8) = Percentage.Text
    If r > 15 Then
        Unload Me
        Exit Sub
    End If

IngredientName.Text = ""
Percentage.Text = ""
IngredientName.SetFocus
Maximum.Value = Maximum.Value + 1 'New part added
End Sub
Private Sub UserForm_Initialize()
Maximum.Value = 10  'New part added
Maximum.Visible = False 'New part added
End Sub