If then statement with a button on a form

[amoverton2]

Aloha!!

New direction for my database... In my company we have 15 different departments (C100, C200, and so forth). When we get a new person I want to use a userform to input his data. However, upon depressing a macro enabled transfer/save info button it transfers their info to a certain sheet for the specific department or code they are going to working in (for all intents and purposes each codes sheet is labeled "C###"). On the userform there is a combo box to choose which code they are going to. I just don't know how to write the code based to send the info to the right sheet. Help please!

Mahalo,
Adam

 

[Snakehips]

@amoverton2 Try like below.

'Transfer Form Info
Dim Dept As String
'Get Department Code for sheet
Dept = Me.ComboBox1.Value
'use appropriate sheet
With Sheets(Dept)
 'Do stuff. Like
    .Range("D4") = Me.Textbox1.Value
    .Range("F4") = Me.Textbox2.Value
End With

 

[HighAndWilder]

Something like this where cboCodes is the name of the codes combobox.

BUT, I would have just one worksheet with a column containing the code and use a filter on that column to view the people in departments.
Doing it like this introduces much more flexibility in terms of data management and reporting.

Private Sub cmdSubmit_Click()
Dim intRow As Integer
Dim Ws As Worksheet

    Set Ws = Worksheets(Me.cboCodes.Value)
        
    intRow = Ws.Range("A1").CurrentRegion.Rows.Count + 1
    
    Ws.Range("A" & intRow & ":D" & intRow).Value = Array(Me.txtForename, Me.txtSurname, Me.cboGender, Me.cboDOB)

End Sub