Enter form details into different sheets on checkbox value

Hi Guys,

I've got one more problem to solve with my current project. An example of the workbook is attached.

What i want to do is change the code on my 'enter button' of the 'input form' which can be shown by clicking the 'Add New Hedge' button on the summary sheet.

The change i want to make is when the user clicks 'enter', if the 'settle now' checkbox is false to add the details of the form to the unsettled hedges form as it currently does.

If the 'settle now' checkbox value is true then i want the form details to be added to the next available row on 'settled hedges' worksheet, in the same way it currently does for adding into the next available row of the 'unsettled hedges' sheet. Also here, i would want the value of the 'returns' textbox added to be added into column L rather than the text "unsettled". This would also mean when the 'settle now' checkbox is true that the 'returns' textbox needs to have a value before adding any details.

Thanks,
Adam.

looking at the code that you have so far im sure you will be able to figure things out from here.

One approach to assinging the values to diffrent sheets based on the checkbox.

    Dim iRow   As Long
    Dim ws     As Worksheet
    Dim ws2     As Worksheet
    Dim ws3     As Worksheet
    Dim Wstemp  As Worksheet
    Set ws = Worksheets("Unsettled Hedges")
    Set ws2 = Worksheets("Session Log")
    Set ws3 = Worksheets("Settled Hedges")
    
    If Me.CheckBox1.Value = True Then
        Set Wstemp = ws
            With ws
            'find first empty row in database
            iRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            End With
        GoTo fullsheet
    Else
        Set Wstemp = ws3
            With ws3
            'find first empty row in database
            iRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            End With
        GoTo fullsheet
    End If
fullsheet:
        With Wstemp
        .Cells(iRow, 1).Value = Me.TextBox1.Value
        If Option_Back.Value = True Then
            .Cells(iRow, 2).Value = "Back"
        End If
        If Option_Lay.Value = True Then
            .Cells(iRow, 2).Value = "Lay"
        End If
        .Cells(iRow, 3).Value = Me.TextBox2.Value
        .Cells(iRow, 4).NumberFormat = "@"
        .Cells(iRow, 4).Value = Me.TextBox3.Value
        .Cells(iRow, 5).Value = Me.TextBox7.Value
        .Cells(iRow, 6).Value = Me.TextBox5.Value
        .Cells(iRow, 7).Value = Me.Combo_Sport.Value
        .Cells(iRow, 8).Value = Me.Combo_Client.Value
        .Cells(iRow, 9).Value = Me.TextBox4.Value
        .Cells(iRow, 10).Value = Me.Combo_Firm.Value
        .Cells(iRow, 11).Value = Me.TextBox6.Value
        .Cells(iRow, 12).Value = "Unsettled"
        End With


    With ws2
        'find first empty row in database
        iRow = .Cells(.Rows.Count, 1) _
               .End(xlUp).Offset(1, 0).Row
      

        'copy the data to the Session Log
        .Cells(iRow, 1).Value = Me.TextBox1.Value
        If Option_Back.Value = True Then
            .Cells(iRow, 2).Value = "Back"
        End If
        If Option_Lay.Value = True Then
            .Cells(iRow, 2).Value = "Lay"
        End If
        .Cells(iRow, 3).Value = Me.TextBox2.Value
        .Cells(iRow, 4).NumberFormat = "@"
        .Cells(iRow, 4).Value = Me.TextBox3.Value
        .Cells(iRow, 5).Value = Me.TextBox7.Value
        .Cells(iRow, 6).Value = Me.TextBox5.Value
        .Cells(iRow, 7).Value = Me.Combo_Sport.Value
        .Cells(iRow, 8).Value = Me.Combo_Client.Value
        .Cells(iRow, 9).Value = Me.TextBox4.Value
        .Cells(iRow, 10).Value = Me.Combo_Firm.Value
        .Cells(iRow, 11).Value = Me.TextBox6.Value
        .Cells(iRow, 12).Value = "Unsettled"
    End With

nice bit of code you had here well done.

cheers/

Thanks for that D_Rennie, still got a couple of issues though, hopefully someone can help.

First of all, the code you written had the details going into "Unsettled Hedges" when the checkbox was true and going into "Settled Hedges" when the checkbox was false and it should have been the opposite way around. This was easy enough to swap around but just thought i'd clear that up in case i was unclear in the first place.

One other issue that i am unsure of how to rectify is when the 'settle now' checkbox is unticked the 'returns' textbox does not need a value but currently the code at the top of the procedure is stopping the details from being entered.

Attached is the latest version of the workbook.

I'd very much appreciate any help.

Sorry Jeanrage, the attachment i posted had the code missing from the top of the procedure which checks the form for missing details. (no wonder i wasn't getting any replies!!).

    Dim Ctrl As MSForms.Control
    Dim CtrlArray As Variant
    Dim CtrlType As String
    Dim Msg As String
    Dim ReturnTo As String
    
      CtrlArray = Array("TextBox1", "TextBox2", "TextBox3", "TextBox4", _
                        "TextBox5", "TextBox6", "TextBox7", _
                        "Combo_Client", "Combo_Firm", "Combo_Sport")
      
      For Each Ctrl In Me.Controls
        CtrlType = TypeName(Ctrl)
        If CtrlType Like "TextBox*" Or CtrlType Like "Combo*" Then
          If Ctrl.Value = "" Then
             Msg = Msg & Switch(Ctrl.Name = CtrlArray(0), "Date", _
                                Ctrl.Name = CtrlArray(1), Label_Stake.Caption, _
                                Ctrl.Name = CtrlArray(2), "Avg.Price", _
                                Ctrl.Name = CtrlArray(3), "Phone Position", _
                                Ctrl.Name = CtrlArray(4), "Event/Market", _
                                Ctrl.Name = CtrlArray(5), "Hedging Reason", _
                                Ctrl.Name = CtrlArray(6), "Selection", _
                                Ctrl.Name = CtrlArray(7), "Client", _
                                Ctrl.Name = CtrlArray(8), "Firm", _
                                Ctrl.Name = CtrlArray(9), "Event Class") & vbCrLf
            If ReturnTo = "" Then ReturnTo = Ctrl.Name
          End If
        End If
      Next Ctrl
    
    If Me.Option_Back.Value = False And Me.Option_Lay.Value = False Then
       Msg = Msg & "Bet Type (Back or Lay)" & vbCrLf
    End If
    
    If Msg <> "" Then
       MsgBox "The following information is missing:" & vbCrLf & vbCrLf & Msg
       Exit Sub
    End If

I have re-attached file with correct code this time.

What i want is when the 'settle now' checkbox is ticked the form needs to check if the 'returns' textbox' has a value but when unticked it doesn't.

I've sorted the problem with checking the correct form controls whether the checkbox is ticked/unticked now. It might not be the best way of writing the code but it does the job so i am happy with it.

I still have the problem with the date format though. If i click 'Add New Hedge' button and then then click the calendar icon to open the calendar form and then choose a date. The format shows correctly in the textbox (dd/mm/yyyy) on the input form but when i fill in all the other details and click the enter buttton then the date is entered into column A of 'unsettled hedges' / 'settled hedges' and the session log sheet in US stlye (mm/dd/yyyy).

Can someone tell me what i need to change in my code to keep the european fomat when entered into the cells of the worksheets.

Attached is my latest version for your reference.

Thank You,
Adam.

Hello adam

sorry for the late reply.

for the number formatting does this help.

        With Wstemp
        .Cells(iRow, 1).Value = Me.TextBox1.Value
        .Cells(iRow, 1).NumberFormat = "dd/mm/yyyy"
        If Option_Back.Value = True Then

looks like you got everythink sorted. ill look into the file a little ater tonight or tomrow as the worksheet code i gave is messy.

cheers

Ive made some changes to the code that seam to fix the problem.(though it is getting late and i could be seeing things. )

            Dim WS As Worksheet
            
            Private Sub Button_Cancel_Click()
            Unload Me
            Sheets("background").Visible = False
            Sheets("Summary").Select
            End Sub
            
            Private Sub Button_Enter_Click()
                
                Dim Ctrl As MSForms.Control
                Dim CtrlArray As Variant
                Dim CtrlType As String
                Dim Msg As String
                Dim ReturnTo As String
                
                If CheckBox1.Value = True Then
                
                  CtrlArray = Array("TextBox1", "TextBox2", "TextBox3", "TextBox4", _
                                    "TextBox5", "TextBox6", "TextBox7", "TextBox8", _
                                    "Combo_Client", "Combo_Firm", "Combo_Sport")
                  
                  For Each Ctrl In Me.Controls
                    CtrlType = TypeName(Ctrl)
                    If CtrlType Like "TextBox*" Or CtrlType Like "Combo*" Then
                      If Ctrl.Value = "" Then
                         Msg = Msg & Switch(Ctrl.Name = CtrlArray(0), "Date", _
                                            Ctrl.Name = CtrlArray(1), Label_Stake.Caption, _
                                            Ctrl.Name = CtrlArray(2), "Avg.Price", _
                                            Ctrl.Name = CtrlArray(3), "Phone Position", _
                                            Ctrl.Name = CtrlArray(4), "Event/Market", _
                                            Ctrl.Name = CtrlArray(5), "Hedging Reason", _
                                            Ctrl.Name = CtrlArray(6), "Selection", _
                                            Ctrl.Name = CtrlArray(7), "Returns", _
                                            Ctrl.Name = CtrlArray(8), "Client", _
                                            Ctrl.Name = CtrlArray(9), "Firm", _
                                            Ctrl.Name = CtrlArray(10), "Event Class") & vbCrLf
                        If ReturnTo = "" Then ReturnTo = Ctrl.Name
                      End If
                    End If
                  Next Ctrl
                End If
                
                If CheckBox1.Value = False Then
                    
                    If TextBox1.Value = "" Then Msg = "Date" & vbCrLf
                    If TextBox2.Value = "" Then Msg = Msg & Label_Stake.Caption & vbCrLf
                    If TextBox3.Value = "" Then Msg = Msg & "Avg. Price" & vbCrLf
                    If TextBox7.Value = "" Then Msg = Msg & "Selection" & vbCrLf
                    If TextBox5.Value = "" Then Msg = Msg & "Event/Market" & vbCrLf
                    If Combo_Sport.Value = "" Then Msg = Msg & "Event Class" & vbCrLf
                    If Combo_Client.Value = "" Then Msg = Msg & "Client Name" & vbCrLf
                    If TextBox4.Value = "" Then Msg = Msg & "Phone Position" & vbCrLf
                    If Combo_Firm.Value = "" Then Msg = Msg & "Firm Used" & vbCrLf
                    If TextBox6.Value = "" Then Msg = Msg & "Hedging Reason" & vbCrLf
                End If
                
                If Me.Option_Back.Value = False And Me.Option_Lay.Value = False Then _
                    Msg = Msg & "Bet Type (Back or Lay)" & vbCrLf
                
                
                If Msg <> "" Then
                   MsgBox "The following information is missing:" & vbCrLf & vbCrLf & Msg
                   Exit Sub
                End If
                
                ScrOff
            
                If Me.CheckBox1.Value = True Then
                    Set WS = Worksheets("Settled Hedges")
                    Fullsheet
                Else
                    Set WS = Worksheets("Unsettled Hedges")
                    Fullsheet
                End If
                
                Set WS = Worksheets("Session Log")
                Fullsheet
                
            Unload Me
            
            Sheets("background").Visible = False
            
            Sheets("Summary").Select
            End Sub
            
            Private Sub Button_NewClient_Click()
                ScrOff
                frmInput.Hide
                frmNewClient.Show
            End Sub
            
            Private Sub Button_NewSport_Click()
            ScrOff
                frmInput.Hide
                frmNewSport.Show
            End Sub
            
            Private Sub Calendar1_Click() '###
            CalShow
            End Sub
            Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
            CalShow
            End Sub
            Private Sub CalShow()
            ScrOff
                frmInput.Hide
                With frmCalendar
                    .Show
                    With .Calendar1
                        Me.TextBox1.Text = VBA.Format(DateSerial(.Year, .Month, .Day), "DD/MM/YYYY")
                    End With
                End With
                frmInput.Show
            End Sub                      '###
            
            Private Sub UserForm_Activate()
            ScrOff
                Combo_Client.List = Sheets("Summary").Range("B7:B43").Value
                Combo_Firm.List = Sheets("Summary").Range("N7:N43").Value
                Combo_Sport.List = Sheets("Summary").Range("H7:H43").Value
            End Sub
            
            Private Sub CheckBox1_Click()
                If CheckBox1.Value = True Then
                    TextBox8.Visible = True
                    Label8.Visible = True
                End If
                If CheckBox1.Value = False Then
                    TextBox8.Visible = False
                    Label8.Visible = False
                End If
            End Sub
            
            Private Sub CommandButton2_Click()
            ScrOff
                frmInput.Hide
                frmNewFirm.Show
            End Sub
            
            Private Sub Option_Back_Click()
                If Option_Back.Value = True Then
                    Label_Stake.Caption = "Stake"
                    TextBox2.ControlTipText = "Enter stake"
                    TextBox8.ControlTipText = "Returns after any commission inclusive of stake"
                End If
            End Sub
            
            Private Sub Option_Lay_Click()
                If Option_Lay.Value = True Then
                    Label_Stake.Caption = "Liability"
                    TextBox2.ControlTipText = "Enter liability"
                    TextBox8.ControlTipText = "Returns after any commission inclusive of liability"
                End If
            End Sub
            
            Private Sub ScrOff()
            'Application.ScreenUpdating = False
            End Sub
            Private Sub Fullsheet()
            Dim iRow As Long
            
            With WS
                iRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                    .Cells(iRow, 1).Value = Me.TextBox1.Text '###
                    If Option_Back.Value = True Then
                        .Cells(iRow, 2).Value = "Back"
                    End If
                    If Option_Lay.Value = True Then
                        .Cells(iRow, 2).Value = "Lay"
                    End If
                    .Cells(iRow, 3).Value = Me.TextBox2.Value
                    .Cells(iRow, 4).NumberFormat = "@"
                    .Cells(iRow, 4).Value = Me.TextBox3.Value
                    .Cells(iRow, 5).Value = Me.TextBox7.Value
                    .Cells(iRow, 6).Value = Me.TextBox5.Value
                    .Cells(iRow, 7).Value = Me.Combo_Sport.Value
                    .Cells(iRow, 8).Value = Me.Combo_Client.Value
                    .Cells(iRow, 9).Value = Me.TextBox4.Value
                    .Cells(iRow, 10).Value = Me.Combo_Firm.Value
                    .Cells(iRow, 11).Value = Me.TextBox6.Value
                    If CheckBox1.Value = True Then
                        .Cells(iRow, 12).Value = Me.TextBox8.Value
                    End If
                    If CheckBox1.Value = False Then
                        .Cells(iRow, 12).Value = "Unsettled"
                    End If
                    End With
            Set WS = Nothing
            End Sub

cheers

Actually swapout

.Cells(iRow, 1).Value = Me.TextBox1.Text '###

with

.Cells(iRow, 1).Value = DateValue(Me.TextBox1.Value)

that looks better now the fog has cleared.
cheers