Editing data in a spreadsheet using a VBA Form

[Engineer]

I have a large spreadsheet I use as a database to drive an Excel model. The sheet contains row by row data. i.e. I can lookup on the first column in the sheet and recall parameters across the row for use in the model (I hope this is making sense!) New rows of data can be added to the spreadsheet by means of a VB form I created which works well.

However the problem is that a lot of columns are used and it is a pain for the user to scroll across the sheet and enter data. I want to make it even slicker by creating a form which will re-call data from the table and give you the ability of editing it and entering it as a new row. My thoughts on this are a dropdown box generated from the first column of data in the spreadsheet and a choice from that will fill the text boxes of the form with the details from the relevant row then after editing create a new line in the table.

Have you any thoughts on how I can achieve this. At present I can't even get a dropdown box to generate from a range of cells in a column.

 

[Diablo II]

when you say form are you talking worksheet as a form or VBA userform?

 

[Engineer]

A VBA Userform

 

[Diablo II]

ok make a userform on the userform1 put a listbox1, on the listbox1 properties boundcolumn 1 and columnCount 7 for this sample,

now add this to the userform model by dubbel ckicking the userform1
and past this in it.

[CODE] 
Private Sub UserForm_Initialize()
Dim i%, y%
Dim Data(14, 7)
    For i = 1 To 14
        For y = 1 To 7
            Data(i - 1, y - 1) = Worksheets("Data").Cells(i, y)
        Next y
    Next i
    ListBox1.List = Data
End Sub

now in the listbox1 dubble ckick it and add this.

Private Sub ListBox1_Click()
Dim i%
[a1].Select
Dim LB As New Collection
LB.Add UserForm2.TextBox1
LB.Add UserForm2.TextBox2
LB.Add UserForm2.TextBox3
LB.Add UserForm2.TextBox4
LB.Add UserForm2.TextBox5
LB.Add UserForm2.TextBox6
LB.Add UserForm2.TextBox7
For i = 1 To 7
LB(i) = Worksheets("Data").Cells(ListBox1.ListIndex + 1, i)
Next i
UserForm2.Show
End Sub
[/CODE]

add a button to the userform1 name it cancel and add this code.

[CODE] 
 Private Sub CommandButton1_Click()
    Unload Me
End Sub

next make another userform2 add 7 textboxs from textbox1 to textbox7.
then add your ladels by each texboxs
then add two buttons ok button and a cancel button
in the ok button add this code.

Private Sub CommandButton1_Click()
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = TextBox1.Text

Range("b1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = TextBox2.Text

Range("C1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = TextBox3.Text

Range("D1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = TextBox4.Text

Range("E1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = TextBox5.Text


Range("F1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = TextBox6.Text

Range("G1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = TextBox7.Text

End Sub

[/CODE]

then in the cancel button add

Private Sub CommandButton3_Click()
  Unload Me
End Sub

now add data to test it out from a2 to g2 down to row 14
when you make it after you test it with your test form you make to test this you can set your rows in the code to work for you.

now you can add a button on the worksheet to start the userform1 then when you click on a line in the list the userform2 will show up with the Values for that line.

 

[Engineer]

Brilliant. Sorry I have taken so long to reply to your post but illness prevented me logging in until last night.

This works perfectly. It does exactly what I wanted it to do.