save data to worksheet dependent on userform dropdown

**mhill** · 07-14-2009, 12:31 PM

Hi All, I'm a little out of my element(normally program PLC's). I have always been lucky on searching others programs and have been real successful at piecing them together to fit the application I needed.
The current workbook I am working on for my supervisor will allow him to keep track of personnel's weekly job assignments. The workbook and userform work fine when just using one worksheet.

What i would like to be able to do is use the "assigned to" combobox be the indicator as to which worksheet the data is saved to. I have tried to do it several different ways but have been un-successful. Have spent the last two days reading and searching for a solution but have yet to find or understand one.

Thanks in advance for any advice and direction anyone is able to give me.

**Palmetto** · 07-14-2009, 01:29 PM

See if the attached is of any help.

Here's what was done:

One worksheets holds a list of names and is the RowSource for a combobox. This list is a dynamic named range.
Each item in the list is the name of a worksheet

This basic code pass the value of the combobox as the string for the worksheet name.

Private Sub CommandButton1_Click()

    Dim strName As String
    
    strName = ComboBox1.Value
    
    With Worksheets(strName)
        Dim lrow As Range
        Set lrow = Worksheets(strName).Range("A65536").End(xlUp).Offset(1, 0)
        lrow.Value = TextBox1.Value
    End With
    
    Unload Me

End Sub

**mhill** · 07-15-2009, 09:59 AM

Thanks Palmetto. Not for sure what I am doing wrong but I am unable to incorporate "strname as string" into my workbook. It's probable something simple I'm overlooking or might be I've looked at this too long.
I'll keep on it till I'm finished though. thanks again

**Palmetto** · 07-16-2009, 03:52 PM

Hi mhill,

Getting anywhere?

I got a moment to look at your workbook and added my code to your command button (disabled yours for testing purposes).

See the attached.

I think it is more efficient (less code) to set the RowSource property of a list box to a named range, which I've done in your workbook. Had to add a sheet and create a list (dynamic named range) for testing.

HTH,

Palmetto