VBA Option Button that selects spreadsheet

[lhk201]

I have created a user form that looks like the following:

1. Enter Name....

2. Enter Telephone Number....

3. Enter Address.....

4. Select Department..... (from option buttons)

- Primary
- Secondary
- Accounts
- Overseas
- Associations

The department selection will determine which sheet to open and enter the rest of the information into the next blank lines in the corresponding colums, i.e. Name in A, Telephone Number in B, Address in C.

Is this possiable using the process/layout I have suggested and what would be the correct code to use?

Thanks for taking the time to read this and any advise you can give

 

[tigeravatar]

lhk201,

Assuming your form has a commandbutton that is clicked once everything is filled in, you could use the following:

Private Sub CommandButton1_Click()
    
    Dim nRow As Long
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "OptionButton" Then
            If ctrl.Value = "True" Then
                With Sheets(ctrl.Caption)
                    nRow = .Cells(Rows.Count).End(xlUp).Row + 1
                    .Cells(nRow, "A").Value = Trim(Me.TextBox1.Text)
                    .Cells(nRow, "B").Value = Trim(Me.TextBox2.Text)
                    .Cells(nRow, "C").Value = Trim(Me.TextBox3.Text)
                    Exit Sub
                End With
            End If
        End If
    Next ctrl
    
    MsgBox "Must select a department"
    
End Sub

Hope that helps,
~tigeravatar

 

[lhk201]

Thank you for your reply!

I couldn't quite get this to work. What should I be changing to adapt it to my document.