Userform for Entering Data in Excel VBA

[Mima]

Hello All,
I have to make a Userform in Excel VBA for entering value from "Textbox1" and write that value in the specific places(in a table) in the worksheet. But when I enter a number, it changes all the cells to the last number. I can't find my error. I would be appreciated if someone could help me.

I attached my excel file. Thanks in advance

[patel]

there are many many errors in your code, see comments


Private Sub CommandButton1_Click()

IniRow = 6 ' this value is constant, you have to check the last non empty row
IniCol = 2
Time = ComboBox3.List(ComboBox3.ListIndex)
Concentration = ComboBox2.List(ComboBox2.ListIndex)
Name = ComboBox1.List(ComboBox1.ListIndex)
Measurment = TextBox1.Value
' you have to check the optionbuttons to find the true


If Name = "Encanto Park Lake" Then
Worksheets("Encanto Park Lake").Activate
If Nutrient = OptionButton1.Value And Concentration = "20" And Time = "First" Then ' nutrient is not defined
Lines(1) = Measurment
Else
Worksheets("Rio Salado").Activate ' this is missing
If Nutrient = OptionButton1.Value And Concentration = "2" And Time = "First" Then
Lines(2) = Measurment

End If
End If
End If



Cells(IniRow, IniCol).Value = Lines(1)
Cells(IniRow, IniCol + 1).Value = Lines(2)
CloseAgrowth

End Sub


Private Sub CommandButton2_Click()
CloseAgrowth
End Sub
to do
1) select the correct Sheet
2) find the true optionbutton or use combobox for selection
3) find the last used row in column B or J or R , based on true optionbutton
4) paste entered value in the last row and correct column

[fara]

What are InRow and InCol ?

[Mima]

Thank you for your comment. I am a beginner in VBA programming.

[Mima]

Thank you for your comment. I am a beginner in VBA programming.
I tried to change my macro base on your comments but it still write zero in all cells.
Would you please help me more?

Private Sub CommandButton1_Click()
Dim Name As String
Dim Concentration As Integer
Dim Measurment As Integer
Dim Data As Integer
Dim Nutrient As String
Dim Time As String

Dim i As Integer, j As Integer
Dim Lines(6) As Integer







Time = ComboBox3.List(ComboBox3.ListIndex)
Concentration = ComboBox2.List(ComboBox2.ListIndex)
Name = ComboBox1.List(ComboBox1.ListIndex)
Measurment = TextBox1.Value



If Name = "Encanto Park Lake" Then
Worksheets("Encanto Park Lake").Activate
If Nutrient = OptionButton1.Value And Concentration = "20" And Time = "First" Then
Lines(1) = Measurment
Else
If Nutrient = OptionButton1.Value And Concentration = "2" And Time = "First" Then
Lines(2) = Measurment

End If
End If
End If

Range("B6").Value = Lines(1)
Range("C6").Value = Lines(2)


If Name = "Rio Salado River" Then
Worksheets("Rio Salado").Activate
If Nutrient = OptionButton1.Value And Concentration = "20" And Time = "First" Then
Lines(3) = Measurment
Else
If Nutrient = OptionButton1.Value And Concentration = "2" And Time = "First" Then
Lines(4) = Measurment

End If
End If
End If



Range("B6").Value = Lines(3)
Range("C6").Value = Lines(4)

CloseAgrowth

End Sub



there are many many errors in your code, see comments


Private Sub CommandButton1_Click()

IniRow = 6 ' this value is constant, you have to check the last non empty row
IniCol = 2
Time = ComboBox3.List(ComboBox3.ListIndex)
Concentration = ComboBox2.List(ComboBox2.ListIndex)
Name = ComboBox1.List(ComboBox1.ListIndex)
Measurment = TextBox1.Value
' you have to check the optionbuttons to find the true


If Name = "Encanto Park Lake" Then
Worksheets("Encanto Park Lake").Activate
If Nutrient = OptionButton1.Value And Concentration = "20" And Time = "First" Then ' nutrient is not defined
Lines(1) = Measurment
Else
Worksheets("Rio Salado").Activate ' this is missing
If Nutrient = OptionButton1.Value And Concentration = "2" And Time = "First" Then
Lines(2) = Measurment

End If
End If
End If



Cells(IniRow, IniCol).Value = Lines(1)
Cells(IniRow, IniCol + 1).Value = Lines(2)
CloseAgrowth

End Sub


Private Sub CommandButton2_Click()
CloseAgrowth
End Sub
to do
1) select the correct Sheet
2) find the true optionbutton or use combobox for selection
3) find the last used row in column B or J or R , based on true optionbutton
4) paste entered value in the last row and correct column

[p45cal]

Change the name on the tab of sheet Rio Salado to Rio Salado River to match what you've put in the combobox, then put this to replace your CommandButton1_Click event handler:

Private Sub CommandButton1_Click()
Dim NutrientOffset As Long

If OptionButton1 Then NutrientOffset = 0
If OptionButton2 Then NutrientOffset = 1
If OptionButton3 Then NutrientOffset = 2
NutrientOffset = NutrientOffset * 8
Worksheets(ComboBox1.Value).Range("B6").Offset(ComboBox3.ListIndex, NutrientOffset + ComboBox2.ListIndex) = TextBox1.Value
CloseAgrowth
End Sub
There are no checks to ensure that the user has selected a nutrient or put a value in the Textbox.
If this is an assignment I suspect that handing this in will raise an eyebrow, maybe two!

[Mima]

Thank you very much

[Mima]

Thank you very much for your help.
I really really appreciate it.




Change the name on the tab of sheet Rio Salado to Rio Salado River to match what you've put in the combobox, then put this to replace your CommandButton1_Click event handler:

Private Sub CommandButton1_Click()
Dim NutrientOffset As Long

If OptionButton1 Then NutrientOffset = 0
If OptionButton2 Then NutrientOffset = 1
If OptionButton3 Then NutrientOffset = 2
NutrientOffset = NutrientOffset * 8
Worksheets(ComboBox1.Value).Range("B6").Offset(ComboBox3.ListIndex, NutrientOffset + ComboBox2.ListIndex) = TextBox1.Value
CloseAgrowth
End Sub
There are no checks to ensure that the user has selected a nutrient or put a value in the Textbox.
If this is an assignment I suspect that handing this in will raise an eyebrow, maybe two!