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]

I just setup a UserForm exactly the way you said and all works fine
I see nothing in this script provided that would cause the Userform to unload.

Are you sure you have you textboxes named the same way as shown in this script?
The two text boxes must be name "IngredientName" and "Percentage"

I suspect maybe you did not write this script or you would know that.

You show me in this script where does it say "Unload" Form

To name a textbox click on the text box and in the properties window type in the proper name into the Name box.

 

[Ajunk]

Thand you for responding.

The double checked the spelling and case of the names of the textboxes and they correlate with the code.

I may be inserting my code into the wrong place.

The code exists within a command button AddIngredient. Is this the correct place? It loads the user form but just isn't working.

Thanks again for your help.

 

[My Aswer Is This]

To get the userform to open you need to have a button on your worksheet which would say something like
Userform1.show

Then you must have another button on your userform with the script loaded into that button. When you press the button on your userform the script should run.

One thing any time you ask questions here you should point out if you have never before used a userform. It sounds like this is your first time.

When you show us a script like this it makes me think OK this guy knows a lot about writing scripts and using UserForms but I now do not believe that is our situation.

 

[Ajunk]

I am very new to VBA and form controls.
The script is not my script
I have the script isnide the command button on thE user form.

I know this is annoying but it is still not working.

Should I start over from scratch in a new workbook with a new user form?

 

[My Aswer Is This]

So you are able to get the UserForm to open is that correct?

And you have a Button on the Userform is that correct?
And on the Userform you have two textboxes is that correct?
And the Textboxes are named IngredientName" and "Percentage" Is that correct?

And in your button you have some code installed is that correct?

What is the name of your command button with the script?

Please answer all questions please

 

[Ajunk]

"So you are able to get the UserForm to open is that correct?"

Yes

"And you have a Button on the Userform is that correct?"

Yes

"And on the Userform you have two textboxes is that correct?"

Yes

"And the Textboxes are named IngredientName" and "Percentage" Is that correct?"

Yes

"And in your button you have some code installed is that correct?"

Yes

"What is the name of your command button with the script?"

AddIngredient.

Also, I used an activeX control command button which opens the form when clicked.

 

[My Aswer Is This]

OK.
I went back and read the script again. This script will only allow you to enter values down to row 17

Do you really want that feature?

If not remove these line of code:

If r > 17 Then
 Unload Me
  Exit Sub
End If

 

[Ajunk]

In short, yes I need the form to unload when row 17 is filled.

 

[Ajunk]

Also, the command button that loads the form clears the contents of the range that the form edits.

This may be the problem.

I think the syntax is:

Range("g10:h17").ClearContents

RecipeAdd.load

I'm not at work anymore so I can't view it, but I can check this in the morning.