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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,274
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:

Ajunk

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,274
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:

Ajunk

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,274
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:

Ajunk

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,274
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
 

Ajunk

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

Forum statistics

Threads
1,081,976
Messages
5,362,511
Members
400,678
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top