I have a worksheet called "Data" which stores 9 columns of address fields. The sheet is locked to prevent accidental deletion of cells. All amendments have to be carried out using a Userform
This sub defines the data range:
Private Sub UserForm_Initialize()
Dim LastRow as Long
LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Data").Range("A1:I" & LastRow).Name = "ListName"
ComboBox1.RowSource = "ListName"
ComboBox1.ListIndex = 0
End Sub
The next sub changes the form contents when Combobox 1 is changed:
Private Sub ComboBox1_Change()
With ComboBox1
TextBox30.Value = Range(.RowSource).Cells(.ListIndex + 1, 1)
TextBox31.Value = Range(.RowSource).Cells(.ListIndex + 1, 2)
TextBox32.Value = Range(.RowSource).Cells(.ListIndex + 1, 3)
TextBox33.Value = Range(.RowSource).Cells(.ListIndex + 1, 4)
TextBox34.Value = Range(.RowSource).Cells(.ListIndex + 1, 5)
TextBox35.Value = Range(.RowSource).Cells(.ListIndex + 1, 6)
TextBox36.Value = Range(.RowSource).Cells(.ListIndex + 1, 7)
TextBox37.Value = Range(.RowSource).Cells(.ListIndex + 1, 8)
TextBox38.Value = Range(.RowSource).Cells(.ListIndex + 1, 9)
End With
End Sub
The last sub should replace the worksheet contents with the value of the textboxes on the form
Sub CommandButton4_Click()
With ComboBox1
Range(.RowSource).Cells(.ListIndex + 1, 1).Value = TextBox30.Value
Range(.RowSource).Cells(.ListIndex + 1, 2).Value = TextBox31.Value '
Range(.RowSource).Cells(.ListIndex + 1, 3).Value = TextBox32.Value
Range(.RowSource).Cells(.ListIndex + 1, 3).Value = TextBox32.Value
Range(.RowSource).Cells(.ListIndex + 1, 4).Value = TextBox33.Value
Range(.RowSource).Cells(.ListIndex + 1, 5).Value = TextBox34.Value
Range(.RowSource).Cells(.ListIndex + 1, 6).Value = TextBox35.Value
Range(.RowSource).Cells(.ListIndex + 1, 7).Value = TextBox36.Value
Range(.RowSource).Cells(.ListIndex + 1, 8).Value = TextBox37.Value
Range(.RowSource).Cells(.ListIndex + 1, 9).Value = TextBox38.Value
End With
Unload UserForm5
End Sub
The first line (Range(.RowSource).Cells(.ListIndex + 1, 1).Value = TextBox30.Value) is executed in the sub above and the amended value of Textbox30 is pasted on the Data sheet in column A, overwriting the previous value. None of the lines after this are executed. I've even tried moving lines around and each time only the first line is processed.
Can anyone enlighten me as to where I've gone wrong please.