Help!

[raseen20]

Dear All,

I've tried to make 2 userforms for a succession plan office project.

Userform1 (Data Entry)
1st part of the form extracts data from the "master database" sheet.
2nd part of the form is used to enter data into the "succession database" sheet.

This userform works perfect.

Userform2 (Data Edit)
This form is used to first extract and show the data entered into the "succession databse" sheet. The information is shown in textboxes and I expect the changes made to those boxes will overwrite the existing initially entered information in the "succession databse". But that's not happening
Since this is office work, i'm being able to share limited information only. The codes for my USERFORM2 (one used to edit) is given below



'to extract information from succession database
Private Sub ListBox1_Click()
txt17.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 1, False)
txt38.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 2, False)
txt18.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 3, False)
txt19.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 4, False)
txt20.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 5, False)
txt21.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 6, False)
txt22.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 7, False)
txt23.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 13, False)
txt24.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 14, False)
txt25.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 15, False)
txt26.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 16, False)
txt27.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 17, False)
txt28.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 18, False)
txt29.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 8, False)
txt30.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 9, False)
txt31.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 10, False)
txt32.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 11, False)
txt33.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 12, False)
txt34.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 20, False)
txt35.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 21, False)
txt36.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 22, False)
txt37.Value = Application.VLookup(Me.ListBox1, Sheets("Succession Database").Range("A:w"), 23, False)
End Sub


'to edit/overwrite information
Private Sub CommandButton1_Click()
Range("A1").Select
With ActiveCell.Offset(ListBox1.ListIndex, 0)

.Offset(RowCount, 0).Value = Me.txt17.Value
.Offset(RowCount, 1).Value = Me.txt38.Value
.Offset(RowCount, 2).Value = Me.txt18.Value
.Offset(RowCount, 3).Value = Me.txt19.Value
.Offset(RowCount, 4).Value = Me.txt20.Value
.Offset(RowCount, 5).Value = Me.txt21.Value
.Offset(RowCount, 6).Value = Me.txt22.Value
.Offset(RowCount, 12).Value = Me.txt23.Value
.Offset(RowCount, 13).Value = Me.txt24.Value
.Offset(RowCount, 14).Value = Me.txt25.Value
.Offset(RowCount, 15).Value = Me.txt26.Value
.Offset(RowCount, 16).Value = Me.txt27.Value
.Offset(RowCount, 17).Value = Me.txt28.Value
.Offset(RowCount, 7).Value = Me.txt29.Value
.Offset(RowCount, 8).Value = Me.txt30.Value
.Offset(RowCount, 9).Value = Me.txt31.Value
.Offset(RowCount, 10).Value = Me.txt32.Value
.Offset(RowCount, 11).Value = Me.txt33.Value
.Offset(RowCount, 19).Value = Me.txt34.Value
.Offset(RowCount, 20).Value = Me.txt35.Value
.Offset(RowCount, 21).Value = Me.txt36.Value
End With
Unload Me
End Sub


PLEASE HELP ME!

 

[Andrew Poulsom]

Why are you using ActiveCell and what's RowCount?

 

[raseen20]

Why are you using ActiveCell and what's RowCount?

Andrew,
Thank you so much for such a prompt reply. Well first up, I'm a novice and the coding that i showed you below is basically a compilation of fragments from youtube and google.
Don't really know how i ended up using Activecell and Rowcount; stuff taken from the net.
Is there any way I can attach the excel file for you to have a look?

 

[Andrew Poulsom]

What range is the source of your ListBox?

 

[raseen20]

What range is the source of your ListBox?

The source(s) for my listbox are the 3 first columns (Employee ID, Full Name, Department) cumulatively termed as "ABNameDept2". The count of rows is unlimited till the end of the worksheet. Columncount of the listbox is at 3 to show all 3 columns at once. I wanted to have a combobox with just the employee's name to filter/dropdown and auto-retrieve the rest of the information but couldn't make anything happen so opted for the listbox

 

[Andrew Poulsom]

Does this work for you?

Private Sub CommandButton1_Click()
    With Sheets("Succession Database").Range("A2").Offset(ListBox1.ListIndex, 0)
        .Offset(, 0).Value = Me.txt17.Value
        .Offset(, 1).Value = Me.txt38.Value
        .Offset(, 2).Value = Me.txt18.Value
        .Offset(, 3).Value = Me.txt19.Value
        .Offset(, 4).Value = Me.txt20.Value
        .Offset(, 5).Value = Me.txt21.Value
        .Offset(, 6).Value = Me.txt22.Value
        .Offset(, 12).Value = Me.txt23.Value
        .Offset(, 13).Value = Me.txt24.Value
        .Offset(, 14).Value = Me.txt25.Value
        .Offset(, 15).Value = Me.txt26.Value
        .Offset(, 16).Value = Me.txt27.Value
        .Offset(, 17).Value = Me.txt28.Value
        .Offset(, 7).Value = Me.txt29.Value
        .Offset(, 8).Value = Me.txt30.Value
        .Offset(, 9).Value = Me.txt31.Value
        .Offset(, 10).Value = Me.txt32.Value
        .Offset(, 11).Value = Me.txt33.Value
        .Offset(, 19).Value = Me.txt34.Value
        .Offset(, 20).Value = Me.txt35.Value
        .Offset(, 21).Value = Me.txt36.Value
    End With
    Unload Me
 End Sub

 

[raseen20]

Does this work for you?

Private Sub CommandButton1_Click()
    With Sheets("Succession Database").Range("A2").Offset(ListBox1.ListIndex, 0)
        .Offset(, 0).Value = Me.txt17.Value
        .Offset(, 1).Value = Me.txt38.Value
        .Offset(, 2).Value = Me.txt18.Value
        .Offset(, 3).Value = Me.txt19.Value
        .Offset(, 4).Value = Me.txt20.Value
        .Offset(, 5).Value = Me.txt21.Value
        .Offset(, 6).Value = Me.txt22.Value
        .Offset(, 12).Value = Me.txt23.Value
        .Offset(, 13).Value = Me.txt24.Value
        .Offset(, 14).Value = Me.txt25.Value
        .Offset(, 15).Value = Me.txt26.Value
        .Offset(, 16).Value = Me.txt27.Value
        .Offset(, 17).Value = Me.txt28.Value
        .Offset(, 7).Value = Me.txt29.Value
        .Offset(, 8).Value = Me.txt30.Value
        .Offset(, 9).Value = Me.txt31.Value
        .Offset(, 10).Value = Me.txt32.Value
        .Offset(, 11).Value = Me.txt33.Value
        .Offset(, 19).Value = Me.txt34.Value
        .Offset(, 20).Value = Me.txt35.Value
        .Offset(, 21).Value = Me.txt36.Value
    End With
    Unload Me
 End Sub

It doesn't work; shows run-time error '424': object required
the debug command highlights the following line;
With Sheets("Succession Database").Range("A2").Offset(ListBox1.ListIndex, 0)

 

[Andrew Poulsom]

Where is your ListBox and where is that VBA code? Maybe you need Me.ListBox1.