UserForm that updates Excel Spreadsheet Records

[NeedSomeSeriousHelp77]

Here's my dilemna.

I have a shared workbook, where at times mutliple users will be in to make updates to different records in different worksheets.

I want to use a UserForm to allow the users to select the records, and then update the records.

I'd like to do this for a couple reasons

(1) as a form of control so that users are not updating the same rows and therefore overwriting each others work

(2) so that users can update the row through the userform which will only allow users to fill in values for particular fields.

There are 6 worksheets in the workbook - all of which have 14 identical column headings.

The userform will need to select one worksheet at a time (out of the 6), then allow the user to scroll the records of that worksheet by the "INDEX" field (i.e. using a combo box).

For each "INDEX", the userform should populate the values of 5 columns (out of the 12), in text boxes. The "INDEX" is the unique identifier that will never be overwritten, only the values of the 5 columns will be overwritten.

After the users update these 5 different column values, they will then save the changes worksheet. The changes should update the current rows, and not add additional rows.

Please let me know if you need any other information.

The names of the worksheets are sub,can,com,un,in
The names of the columns that need to be edited are isc,hand,can,com,sub
The column that will be used to pull the records is the INDEX.

Thank you,

 

[NeedSomeSeriousHelp77]

This is what I have so far, but the combo drop down is only showing the first INDEX entry from each sheet. I believe my issue lies with the line ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Range("A2").Value. I have to select the range from A2 to the last value in the column.

Private Sub UserForm_Initialize()
Dim ws As Worksheet

ListBox1.ColumnCount = 1 ' Listbox with one columns of values

For Each ws In Worksheets
ListBox1.AddItem (ws.Name)
ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Range("A2").Value
Next ws

End Sub

Private Sub cmdAdd_Click()
If txtisc.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("H2").Value = txtisc.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtisc.Value ' Update Listbox with new value
End If
If txthandoff.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("I2").Value = txthandoff.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txthandoff.Value ' Update Listbox with new value
End If
If txtcanada.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("J2").Value = txtcanada.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtcanada.Value ' Update Listbox with new value
End If
If txtcomplete.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("K2").Value = txtcomplete.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtcomplete.Value ' Update Listbox with new value
End If
If txtsub.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("L2").Value = txtsub.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtsub.Value ' Update Listbox with new value
End If
End Sub

Private Sub ListBox1_Click()
cboINDEX.Value = ListBox1.List(ListBox1.ListIndex, 1)
txtisc.Value = ListBox1.List(ListBox1.ListIndex, 2)
txthandoff.Value = ListBox1.List(ListBox1.ListIndex, 3)
' Update TextBox1 with selected A1 value
End Sub

 

[NeedSomeSeriousHelp77]

This is what I have so far, but the combo drop down is only showing the first INDEX entry from each sheet.

I believe my first issue lies with the line ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Range("A2").Value. I have to select the range from A2 to the last value in the column.

My second issue has to do with:

Private Sub ListBox1_Click()
cboINDEX.Value = ListBox1.List(ListBox1.ListIndex, 1)
txtisc.Value = ListBox1.List(ListBox1.ListIndex, 1)
txthandoff.Value = ListBox1.List(ListBox1.ListIndex, 1)
' Update TextBox1 with selected A1 value
End Sub

I need to show the columns in the different text boxes. Right now it's set up to show the index value in all the text boxes, but I need to change it so that the index value shows in the index text box, the isc value shows in the isc text box, the handoff value shows in the handoff text box etc.


Here is the overall code:

Private Sub UserForm_Initialize()
Dim ws As Worksheet

ListBox1.ColumnCount = 1 ' Listbox with one columns of values

For Each ws In Worksheets
ListBox1.AddItem (ws.Name)
ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Range("A2").Value
Next ws

End Sub

Private Sub cmdAdd_Click()
If txtisc.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("H2").Value = txtisc.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtisc.Value ' Update Listbox with new value
End If
If txthandoff.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("I2").Value = txthandoff.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txthandoff.Value ' Update Listbox with new value
End If
If txtcanada.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("J2").Value = txtcanada.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtcanada.Value ' Update Listbox with new value
End If
If txtcomplete.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("K2").Value = txtcomplete.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtcomplete.Value ' Update Listbox with new value
End If
If txtsub.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("L2").Value = txtsub.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtsub.Value ' Update Listbox with new value
End If
End Sub

Private Sub ListBox1_Click()
cboINDEX.Value = ListBox1.List(ListBox1.ListIndex, 1)
txtisc.Value = ListBox1.List(ListBox1.ListIndex, 2)
txthandoff.Value = ListBox1.List(ListBox1.ListIndex, 3)
' Update TextBox1 with selected A1 value
End Sub

 

[NeedSomeSeriousHelp77]

Okay, i've made some progress on the code, but I would still like to clean the code up and get suggestions. I am using Excel 2003. Any thoughts would be appreciated. Thank you.

Private Sub UserForm_Initialize()
Dim ws As Worksheet

ListBox1.ColumnCount = 6 ' Listbox with one columns of values

For Each ws In Worksheets
ListBox1.AddItem (ws.Name)
ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Range("A2").Value
ListBox1.List(ListBox1.ListCount - 1, 2) = ws.Range("H2").Value
ListBox1.List(ListBox1.ListCount - 1, 3) = ws.Range("I2").Value
ListBox1.List(ListBox1.ListCount - 1, 4) = ws.Range("J2").Value
ListBox1.List(ListBox1.ListCount - 1, 5) = ws.Range("K2").Value
ListBox1.List(ListBox1.ListCount - 1, 6) = ws.Range("L2").Value
Next ws

End Sub

Private Sub cmdAdd_Click()
If txtisc.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("H2").Value = txtisc.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtisc.Value ' Update Listbox with new value
End If
If txthandoff.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("I2").Value = txthandoff.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txthandoff.Value ' Update Listbox with new value
End If
If txtcanada.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("J2").Value = txtcanada.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtcanada.Value ' Update Listbox with new value
End If
If txtcomplete.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("K2").Value = txtcomplete.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtcomplete.Value ' Update Listbox with new value
End If
If txtsub.Value <> vbNullString Then
Sheets(ListBox1.Value).Range("L2").Value = txtsub.Value ' Update worksheet
ListBox1.List(ListBox1.ListIndex, 1) = txtsub.Value ' Update Listbox with new value
End If
End Sub

Private Sub ListBox1_Click()
cboINDEX.Value = ListBox1.List(ListBox1.ListIndex, 1)
txtisc.Value = ListBox1.List(ListBox1.ListIndex, 2)
txthandoff.Value = ListBox1.List(ListBox1.ListIndex, 3)
txtcanada.Value = ListBox1.List(ListBox1.ListIndex, 4)
txtcomplete.Value = ListBox1.List(ListBox1.ListIndex, 5)
txtsub.Value = ListBox1.List(ListBox1.ListIndex, 6)

' Update TextBox1 with selected A1 value
End Sub