Unable to get if statement to work

fialko · 07-16-2011, 01:43 AM

Sorry folks i know this is a basic question and i've tried examples from other threads but still struggling

I have a spreadsheet that takes info from a user populated form and writes to a spreadsheet which works fine. I now need it to write some data to a separate sheet dependant on a combo box as well and assume its an if statement but i cant get it to work

Below is what i have written which works fine to both sheets without the if. Basically if a user selects sent to chennai in the form i need it to also transfer values to another spreadsheet, any other selection i need it to carry on and clear the form please

'If Me.cboact = "Sent to Chennai" Then
'With ws

' .Cells(lRow, 2).Value = Me.txtCdl.Value
' .Cells(lRow, 3).Value = Me.cboyn.Value
'.Cells(lRow, 4).Value = Me.txtcom.Value
'Else

'clear form
Me.txtname.Value = ""
Me.txtdob.Value = ""
Me.cboyn.Value = ""
Me.txtrec.Value = ""

**Mordred** · 07-16-2011, 02:03 AM

Hi fialko, without seeing all of your procedure, I can only assume that you haven't appropriately ended your If statement or your With statement.

Sub fialco()
    If Me.cboact = "Sent to Chennai" Then
        With ws
            .Cells(lRow, 2).Value = Me.txtCdl.Value
            .Cells(lRow, 3).Value = Me.cboyn.Value
            .Cells(lRow, 4).Value = Me.txtcom.Value
        End With
    Else
    '''The rest of your 'Else' code here
    End If
End Sub

fialko · 07-16-2011, 02:27 AM

Hi and thanks for helping, here is the rest of the code. Sorry to be a pain, not professing to really know what i am doing trying to learn. Here is rest of code to the end. I have tried what you said in your reply but i am obviously doing something wrong as i get compile error when i try to pass the data. Thanks again

If Me.cboact = "Sent to Chennai" Then
With ws
 
  .Cells(lRow, 2).Value = Me.txtcdl.Value
  .Cells(lRow, 3).Value = Me.cboyn.Value
  .Cells(lRow, 4).Value = Me.txtcom.Value
  End With
Else

'clear form
Me.txtname.Value = ""
Me.txtdob.Value = ""
Me.cboyn.Value = ""
Me.txtrec.Value = ""
Me.cbovet.Value = ""
Me.cboCdl.Value = ""
Me.cboact.Value = ""
Me.txtcom.Value = ""
Me.txtcdl.Value = ""

'save the workbook
ActiveWorkbook.Save

MsgBox "Data has been saved"
Unload Me

'End If

End Sub

**Mordred** · 07-16-2011, 03:17 AM

HI fialco, you are not "being a pain" so no worries

! In the code you provided your 'End if' is commented out. Amended:

If Me.cboact = "Sent to Chennai" Then
    With ws
    .Cells(lRow, 2).Value = Me.txtcdl.Value
    .Cells(lRow, 3).Value = Me.cboyn.Value
    .Cells(lRow, 4).Value = Me.txtcom.Value
    End With
Else
    'clear form
    Me.txtname.Value = ""
    Me.txtdob.Value = ""
    Me.cboyn.Value = ""
    Me.txtrec.Value = ""
    Me.cbovet.Value = ""
    Me.cboCdl.Value = ""
    Me.cboact.Value = ""
    Me.txtcom.Value = ""
    Me.txtcdl.Value = ""
    'save the workbook
    ActiveWorkbook.Save
    MsgBox "Data has been saved"
    Unload Me
End If
End Sub

This was no tested