I have to make a Userform in Excel VBA for entering value from \"Textbox1\" and
ID: 3552846 • Letter: I
Question
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 thetwo diffrent worksheet. But when I enter a number, it changes all the cells to the last number. I can't find my error. This is part of my macro and pictures of my Userform and worksheets.
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 IniRow As Integer, IniCol As Integer
Dim Lines(6) As Integer
IniRow = 6
IniCol = 2
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
If Name = "Encanto Park Lake" 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
Cells(IniRow, IniCol).Value = Lines(1)
Cells(IniRow, IniCol + 1).Value = Lines(2)
Cells(IniRow, IniCol).Value = Lines(3)
Cells(IniRow, IniCol + 1).Value = Lines(4)
CloseAgrowth
End Sub
Private Sub CommandButton2_Click()
CloseAgrowth
End Sub
Private Sub UserForm_Initialize()
ComboBox3.Clear
ComboBox3.AddItem "First"
ComboBox3.AddItem "Second"
ComboBox3.AddItem "Third"
ComboBox3.AddItem "Fourth"
ComboBox3.AddItem "Fifth"
ComboBox3.ListIndex = 0
ComboBox2.Clear
ComboBox2.AddItem "20"
ComboBox2.AddItem "2"
ComboBox2.AddItem "1"
ComboBox2.AddItem "0.5"
ComboBox2.AddItem "0.2"
ComboBox2.AddItem "0"
ComboBox2.ListIndex = 0
ComboBox1.Clear
ComboBox1.AddItem "Encanto Park Lake"
ComboBox1.AddItem "Rio Salado River"
ComboBox1.ListIndex = 0
TextBox1.Text = 1.352
End Sub
Explanation / Answer
In the second if condition, you have:
If Name = "Encanto Park Lake" 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
instead of
If Name = "Rio Salado" 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