Ensuring Combobox has Value entered if another ComboBox has aspecific value

Discussion in 'Microsoft Excel Programming' started by Gareth Hardman, May 8, 2012.

  1. Hi,

    I am new to this group and reletavily new to VBA code and Userforms so
    please forgive my potential huge knowledge gaps...

    I am trying to ensure that if one particular combobox has a value i.e.
    'Breach' in it, that before the form can be 'submitted' successfully
    another combobox has a value in it. I feel I maybe close with the
    code below (but I could be a million miles away as well!):

    I am attempting to use code like:

    'check for DPA 1st Box
    If Trim(Me.DPA.Value) = "" Then
    Me.DPA.SetFocus
    MsgBox "Please enter whether DPA was successful or not",
    vbExclamation, "Not Quite Complete..."
    Exit Sub
    End If

    ' Check DPAIF Box
    If Trim(Me.DPA.Value) = "Breach" Or "Process Fail" Then
    If Trim(Me.DPAIFBox.Value) = "" Then
    Me.DPAIFBox.SetFocus
    MsgBox "Please select a reason why DPA was not completed successfully"
    Exit Sub
    End If

    I am getting the error message: "Compile Error: Block IF without End
    IF"

    If the first DPA combobox says "Yes" or "N/A" then it is not essntial
    that the DPAIFBox has a value. Hope all this makes sense.


    Help would be grately appreciated - and I apoloigise if I have not
    conformed to any forum etiquette :)
     
    Gareth Hardman, May 8, 2012
    #1
    1. Advertisements

  2. Gareth Hardman

    Auric__ Guest

    Re: Ensuring Combobox has Value entered if another ComboBox has a specific value

    Gareth Hardman wrote:

    > I am new to this group and reletavily new to VBA code and Userforms so
    > please forgive my potential huge knowledge gaps...
    >
    > I am trying to ensure that if one particular combobox has a value i.e.
    > 'Breach' in it, that before the form can be 'submitted' successfully
    > another combobox has a value in it. I feel I maybe close with the
    > code below (but I could be a million miles away as well!):
    >
    > I am attempting to use code like:
    >
    > 'check for DPA 1st Box
    > If Trim(Me.DPA.Value) = "" Then
    > Me.DPA.SetFocus
    > MsgBox "Please enter whether DPA was successful or not",
    > vbExclamation, "Not Quite Complete..."
    > Exit Sub
    > End If
    >
    > ' Check DPAIF Box
    > If Trim(Me.DPA.Value) = "Breach" Or "Process Fail" Then
    > If Trim(Me.DPAIFBox.Value) = "" Then
    > Me.DPAIFBox.SetFocus
    > MsgBox "Please select a reason why DPA was not completed successfully"
    > Exit Sub
    > End If


    Need another "End If" right here.

    > I am getting the error message: "Compile Error: Block IF without End
    > IF"
    >
    > If the first DPA combobox says "Yes" or "N/A" then it is not essntial
    > that the DPAIFBox has a value. Hope all this makes sense.
    >
    >
    > Help would be grately appreciated - and I apoloigise if I have not
    > conformed to any forum etiquette :)


    --
    That would be a bug that needs to be found, not strangled to death.
     
    Auric__, May 8, 2012
    #2
    1. Advertisements

  3. Hi Auric,

    I have tried adding an additional End IF statement, however I now get
    a 'Runtime Error 13: Type mismatch' error message. When I select
    Debug, it highlights the folowing line of code in yellow:

    If Trim(Me.DPA.Value) = "Breach" Or "Process Fail" Then

    Any further help on this would be grateful :)
     
    Gareth Hardman, May 9, 2012
    #3
  4. On May 9, 8:09 am, Gareth Hardman <> wrote:
    > Hi Auric,
    >
    > I have tried adding an additional End IF statement, however I now get
    > a 'Runtime Error 13: Type mismatch' error message.  When I select
    > Debug, it highlights the folowing line of code in yellow:
    >
    > If Trim(Me.DPA.Value) = "Breach" Or "Process Fail" Then
    >
    > Any further help on this would be grateful :)


    Hi - I have now found a solution to the query - thanks for looing into
    it for those that may have :)
     
    Gareth Hardman, May 9, 2012
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JulieD

    there must be a better way - ensuring userforms are closed

    JulieD, Sep 6, 2004, in forum: Microsoft Excel Programming
    Replies:
    10
    Views:
    300
    JulieD
    Sep 7, 2004
  2. Ensuring UserForm Textbox Entry is numeric

    , Oct 8, 2004, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    464
  3. Richard H

    Ensuring a cell is copied in text format

    Richard H, Dec 14, 2004, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    257
    Richard H
    Dec 14, 2004
  4. Ensuring Conditional formating is not lost

    , Mar 9, 2005, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    162
  5. ccarmock

    Ensuring Macros are running

    ccarmock, Oct 1, 2005, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    194
    Guest
    Oct 1, 2005
Loading...

Share This Page