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

Question

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.

majjam · Accepted Answer · 2017-04-03 14:58:20Z

2

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

I did the following:

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

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.

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.

edited Apr 3, 2017 at 14:58

answered Feb 23, 2017 at 19:28

majjam

1,3182 gold badges16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

RayRay Over a year ago

Thank you Majjam, I have only just seen this response so my apologies for my late reply. I shall have a look in to your comment and let you know how I get on. Thanks again

majjam Over a year ago

No problem @Grigson, all the best and let me know if you have any questions

RayRay Over a year ago

sorry to be a pain @Majjam I cannot get the file as it is rar'd and I don't have winrar on my work laptop. I can create a validation dropdown but I am unsure where the 'Worksheet change' code goes / how it gets activated? Do I need to create a button to get VBA up? sorry for my ignorance :)

majjam Over a year ago

@Grigson, something weird happened there, I've replaced the link with a dropbox one, that should be ok now

RayRay Over a year ago

Thanks @Majjam I can retrieve it now, I shall look in to it more when I'm back at work tomorrow.

|

KoderM16 · Accepted Answer · 2017-02-22 10:57:57Z

2

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.

answered Feb 22, 2017 at 10:57

KoderM16

1574 silver badges16 bronze badges

1 Comment

RayRay Over a year ago

Thanks for the reply KoderM16, I will end up doing that if the it turns out to be more hassle then it's worth haha. It's just because the fields on sheet 1 are clearer to read and update than on sheet 2 which would make the amending a bit easier if I could do this

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related