CODEWITHSUNDEEP
vbalistexcel
RayRay
RayRay

Reputation: 27

Export data from one sheet to another via drop down list - VBA Excel Macro

I have an Excel workbook with two worksheets on. Sheet 1 is a form which gets filled in and it has a submit button (created with VBA) which takes the data and adds it to the next empty line on sheet 2. So sheet 2 is filled with previously submitted form information and sheet 1 can be cleared (again via a button created with VBA) ready for the next lot of information.

Each entry has a unique number for reference purposes, but what I would like to do is on sheet 1(the form) to have a drop down list of all the unique numbers which I can select one and for it to bring all the relevant information back in to the form so any edits can be made and a button to be able to save/overwrite the data instead of saving it as a new line.

So would like to be able to bring the data back to sheet 1 to edit/amend/save/overwrite.

My VBA knowledge is limited as this is the first project I have dealt with it on so I'm still learning the basics as I go.

Any advice or suggestions would be gratefully appreciated.

Many Thanks

Rachael.

Upvotes: 1

Views: 1977

Answers (2)

majjam
majjam

Reputation: 1326

I've put together a quick example demoing what you requested, it can be downloaded here.

I did the following:

  1. Added Form (with a few data editing fields) and Data (with example data) worksheets.
  2. Added a validation dropdown to the datasheet showing the ID + Name data from your Data worksheet.

  3. When the selected option in the validation dropdown is changed it triggers the Worksheet_Change event, running the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    'check that the Target cell is our dropdown
    If Target.Address = "$C$2" Then
        'get the value of the dropdown using the range method
        Dim dropDownValue As String
        dropDownValue = CStr(wsForm.Range(Target.Address).Value)

        'if that dropdown value exists (has a length of more than zero)
        If Len(dropDownValue) > 0 Then
            'get the corresponding record from the data sheet
            Dim index As Integer
            index = Left(dropDownValue, 1)

            wsForm.Range("C3").Value = index
            wsForm.Range("C4").Value = Application.WorksheetFunction.VLookup(index, wsData.Range("A:E"), 2, False)
            wsForm.Range("C5").Value = Application.WorksheetFunction.VLookup(index, wsData.Range("A:E"), 3, False)
            wsForm.Range("C6").Value = Application.WorksheetFunction.VLookup(index, wsData.Range("A:E"), 4, False)
        End If
 End If
End Sub

    Which uses vlookups to retrieve the information from the datasheet to populate the editing form.

  4. When the save button is clicked, the following code is run:

    Sub Button4_Click()
    Dim index As Integer
    index = wsForm.Range("C3")

    If index > 0 Then
        Dim foundIndexRange As Range
        Set foundIndexRange = wsData.Range("A:A").Find(index)

        If (Len(foundIndexRange.Value) > 0) Then
            foundIndexRange.Offset(0, 1).Value = wsForm.Range("C4").Value
            foundIndexRange.Offset(0, 2).Value = wsForm.Range("C5").Value
            foundIndexRange.Offset(0, 3).Value = wsForm.Range("C6").Value
        End If
        MsgBox "Record saved"
    Else
        MsgBox "Please choose from the dropdown"
    End If
End Sub

    Which uses the range.Find method to locate the range where our index is on the data sheet, then offset to overwrite our new values.

I hope that makes sense, please ask if you have any questions.

Upvotes: 2

KoderM16
KoderM16

Reputation: 157

Instead of bringing the data back to sheet 1, in sheet 2, you can turn on "Filters" and in the unique numbers column, filter/search for the number whose data you want to change. It will then only show the entry of data corresponding to that number. Then make the edits on sheet 2.

Hope this is useful.

Upvotes: 2

Related Questions