I am using a Form and Listbox to update a small range of cells (13 rows and 2 Columns) in a Sheet. Not a skilled VBA programmer I have used Google to find an appropriate code. And it seems to work, but is based on the cells being at the top of the sheet "A:A". I can't seem to change the code to refer to a given range in another sheet (say EXTRAS!B80:C92)... I expect this is a simple solution but my own skills are limited... can anyone help me with this.
Here is the code I have used. I have two textbox entries to update the selected row in the list box (double click) and a Commandbutton to save the changes and copy to the range.
'Open Form and fill listbox
Private Sub UserForm_Initialize()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 2).Value
Next i
End Sub
'Select Row in Listbox
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm1.TextBox1.Text = Me.ListBox1.Column(0)
UserForm1.TextBox2.Text = Me.ListBox1.Column(1)
End Sub
'Save edited data
Private Sub CommandButton1_Click()
UserForm1.ListBox1.Column(0) = Me.TextBox1.Text
UserForm1.ListBox1.Column(1) = Me.TextBox2.Text
Dim x
x = Me.ListBox1.List
ThisWorkbook.Worksheets("Sheet1").Range("A2").Resize(UBound(x) + 1, 2).Value = x
End Sub
Bookmarks