[Solved] VBA : Need more efficient code for processing yes n

  • I have a form that has a yes checkbox, and a no checkbox. When users select one I need to copy the info from one form field to another formfield.
    There has to be a way to get the same results more efficiently.
    There seem to be too many ifs.........
    Here is my current code:


    Sub HBCBiweeklyChoice()


    Dim YesChoice As CheckBox
    Dim NoChoice As CheckBox



    Set YesChoice = ActiveDocument.FormFields("AOCYes").CheckBox
    Set NoChoice = ActiveDocument.FormFields("AOCNo").CheckBox


    Application.ScreenUpdating = False



    If YesChoice.Value = True And NoChoice.Value = True Then
    MsgBox "Please Choose Yes OR No" & vbCrLf & "This will place the hours in the correct field Above", vbExclamation, "Select Agency of Choice"
    End If


    If YesChoice.Value = False And NoChoice.Value = False Then
    MsgBox "You Must Pick One" & vbCrLf & "This Field Cannot Be Blank", vbExclamation, "Select Agency of Choice"
    End If
    If YesChoice.Value = True And NoChoice.Value = False Then
    With ActiveDocument
    .FormFields("HBCSMD").Result = ""
    .FormFields("HBCSMN").Result = ""
    .FormFields("HBCBiDay").Result = .FormFields("MaineCare_Day_Hrs").Result
    .FormFields("HBCBiNight").Result = .FormFields("MaineCare_Night_Hrs").Result
    End With
    End If
    If YesChoice.Value = False And NoChoice.Value = True Then
    With ActiveDocument
    .FormFields("HBCBiDay").Result = ""
    .FormFields("HBCBiNight").Result = ""
    .FormFields("HBCSMD").Result = .FormFields("MaineCare_Day_Hrs").Result
    .FormFields("HBCSMN").Result = .FormFields("MaineCare_Night_Hrs").Result
    End With
    End If
    Application.ScreenUpdating = True



    End Sub

  • Hi saintone and welcome,


    Maybe I'm not understanding your problem correctly.
    But can you not just use ONE checkbox.


    If it was checked then it would be like YesChoice = TRUE
    and if its not checked then
    NoChoice = TRUE


    This would make the code simply


    Sub HBCBiweeklyChoice()


    Dim chkChoice As CheckBox


    Set chkChoice = ActiveDocument.FormFields("AOCYes").CheckBox


    Application.ScreenUpdating = False


    If chkChoice.Value = True Then
    With ActiveDocument
    .FormFields("HBCSMD").Result = ""
    .FormFields("HBCSMN").Result = ""
    .FormFields("HBCBiDay").Result = .FormFields("MaineCare_Day_Hrs").Result
    .FormFields("HBCBiNight").Result = .FormFields("MaineCare_Night_Hrs").Result
    End With
    Else
    NoChoice.Value = True Then
    With ActiveDocument
    .FormFields("HBCBiDay").Result = ""
    .FormFields("HBCBiNight").Result = ""
    .FormFields("HBCSMD").Result = .FormFields("MaineCare_Day_Hrs").Result
    .FormFields("HBCSMN").Result = .FormFields("MaineCare_Night_Hrs").Result
    End With
    End If
    Application.ScreenUpdating = True
    End Sub


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

  • Unfortunately I am stuck with Yes and NO. I have to mimick the already created paper form. It is a form that was approved and issued to us by the state. So I do need a yes and a no and when the form is complete and there needs to be a check in one or the other boxes.
    My users are not very thorough and i also need to make sure that one or the other is checked, and not both or none.


    Am I Stuck with the code I had written???


    Thanks for your reply,


    Brian (St.One)

  • Hi Brian,


    The only revision to your code you could make is to use the ELSEIF and ELSE constructs.


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!