Edit and delete data from database using userform in excel

Question

Hi Team,

Need some help!

I have been working on a userform for entering in and editing data in a database sheet which is located in my share drive folder (Z:\Export data.xlsx).In userform Sheet(MIS)

two tabs- client addition and exception client working fine but I'm stuck with the code for deletion and edition data.What i need when i enter client ID in client id text box

all information should display in the text boxes and I expect the changes made in those textboxes will overwrite the existing initially entered information in the database sheet.

and for deletion if would select client id the information should move to sheet2 of database sheet which is located on my share drive.

PLEASE HELP ME!

Answer 1

May be

Private Sub ComboBox1_Change()

On Error GoTo message

Dim r As Integer

With Sheets("October 2015")

    If ComboBox1 <> "" Then

        r = Application.Match(Val(ComboBox1), .Range("D:D"), 0)

        Me.TextBox1 = Sheets("October 2015").Range("C" & r)

        Me.TextBox2 = Sheets("October 2015").Range("G" & r)

        Me.TextBox3 = Sheets("October 2015").Range("H" & r)

        Me.TextBox4 = Sheets("October 2015").Range("K" & r)

        Me.TextBox5 = Sheets("October 2015").Range("N" & r)

        Me.TextBox6 = Sheets("October 2015").Range("O" & r)

        Me.TextBox7 = Sheets("October 2015").Range("P" & r)

        Me.TextBox8 = Sheets("October 2015").Range("Q" & r)

        Me.TextBox9 = Sheets("October 2015").Range("AK" & r)

        Me.TextBox10 = Sheets("October 2015").Range("AL" & r)

        Me.TextBox11 = Sheets("October 2015").Range("AN" & r)

        Me.TextBox12 = Sheets("October 2015").Range("AO" & r)

        Me.TextBox13 = Sheets("October 2015").Range("AP" & r)

        Me.TextBox14 = Sheets("October 2015").Range("AQ" & r)

        Me.TextBox15 = Sheets("October 2015").Range("AR" & r)

        Me.TextBox16 = Sheets("October 2015").Range("AS" & r)

        Me.TextBox17 = Sheets("October 2015").Range("AT" & r)

        Me.TextBox18 = Sheets("October 2015").Range("AU" & r)

        Me.TextBox19 = Sheets("October 2015").Range("AV" & r)

        Me.TextBox20 = Sheets("October 2015").Range("AW" & r)

        Me.TextBox21 = Sheets("October 2015").Range("AX" & r)

        Me.TextBox22 = Sheets("October 2015").Range("AY" & r)

      Exit Sub

message:

    MsgBox "Please Enter Correct Client Code"

    ComboBox1.SetFocus

    End If

End With

End Sub

Private Sub CommandButton1_Click()

On Error GoTo vervolg

With Sheets("October 2015")

    r = Application.Match(Val(ComboBox1), .Range("D:D"), 0)

    .Range("C" & r) = Me.TextBox1

    .Range("G" & r) = Me.TextBox2

    .Range("H" & r) = Me.TextBox3

    .Range("K" & r) = Me.TextBox4

    .Range("N" & r) = Me.TextBox5

    .Range("O" & r) = Me.TextBox6

    .Range("P" & r) = Me.TextBox7

    .Range("Q" & r) = Me.TextBox8

    .Range("AK" & r) = Me.TextBox9

    .Range("AL" & r) = Me.TextBox10

    .Range("AN" & r) = Me.TextBox11

    .Range("AO" & r) = Me.TextBox12

    .Range("AP" & r) = Me.TextBox13

    .Range("AQ" & r) = Me.TextBox14

    .Range("AR" & r) = Me.TextBox15

    .Range("AS" & r) = Me.TextBox16

    .Range("AT" & r) = Me.TextBox17

    .Range("AU" & r) = Me.TextBox18

    .Range("AV" & r) = Me.TextBox19

    .Range("AW" & r) = Me.TextBox20

    .Range("AX" & r) = Me.TextBox21

    .Range("AY" & r) = Me.TextBox22

End With

Dim cCont As Control

For Each cCont In Me.Controls

    If TypeName(cCont) = "TextBox" Then cCont = ""

Next

ComboBox1 = ""

ComboBox1.SetFocus

Exit Sub

vervolg:

MsgBox "No match is found"

ComboBox1.SetFocus

End Sub

Answer 2

I need this too. Please anyone that can help me with this. I have entering data for my user form now I need to be able to edit data using this form.

Thanks, Kelly

Hi Team,

Need some help!

I have been working on a userform for entering in and editing data in a database sheet which is located in my share drive folder (Z:\Export data.xlsx).In userform Sheet(MIS)

two tabs- client addition and exception client working fine but I'm stuck with the code for deletion and edition data.What i need when i enter client ID in client id text box

all information should display in the text boxes and I expect the changes made in those textboxes will overwrite the existing initially entered information in the database sheet.

and for deletion if would select client id the information should move to sheet2 of database sheet which is located on my share drive.

PLEASE HELP ME!

Answer 3

No, my question is not answered