Editing data in a spreadsheet using a VBA Form

Engineer

New Member
Joined
Apr 11, 2009
Messages
21
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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Diablo II

Well-known Member
Joined
Sep 28, 2008
Messages
538
when you say form are you talking worksheet as a form or VBA userform?
 

Diablo II

Well-known Member
Joined
Sep 28, 2008
Messages
538
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:
[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:
[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

Code:
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

New Member
Joined
Apr 11, 2009
Messages
21
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,551
Messages
5,596,797
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top