Data Entry from Userform on multiple tabs

[John_McClane]

Hello Everyone,

Just wondered if I could get some help with this one:

I'd like to use this Pro tip: Add a UserForm to aid data entry in Excel - TechRepublic sheet as a template for a spreadsheet I would like to make. The userform is all good, I can edit the titles of everything and it does exactly what I want.

What I'd like it to do is have another field in the userform (I can do this bit, just struggling with the code) that is a dropdown box of all the tabs in the spreadsheet and it will fill the cells on the relevant tab that you have selected.

So, the first drop box would be tab: and you could select animals 1 or 2; if you select 1 it will save the details to animals 1 tab and vice versa.

Hope I've explained it properly!

Thank you in advance for any help.

John

 

[dmt32]

Hi
Give following a try:

Add a ComboBox to your form and name it cboSheets

Replace following line in Private Sub cmdAdd_Click procedure:

Set ws = Worksheets("Animals")

with this line:

Set ws = ThisWorkbook.Worksheets(Me.cboSheets.Text)

Add this procedure to your forms code page:

Private Sub UserForm_Initialize()
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        Me.cboSheets.AddItem sh.Name
    Next sh
    Me.cboSheets.ListIndex = 0
End Sub

Dave

 

[John_McClane]

Thank you - works perfectly!