Form Control is Not updating range

Ajunk

New Member
Joined
Oct 19, 2016
Messages
19
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.


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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
"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.
 
Upvote 0
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:

Code:
If r > 17 Then
 Unload Me
  Exit Sub
End If
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top