+ Reply to Thread
Results 1 to 3 of 3

Simple "If Then" Check Responding Unexpectedly

  1. #1
    Registered User
    Join Date
    01-11-2017
    Location
    St. Louis, Missouri, USA
    MS-Off Ver
    2010
    Posts
    1

    Simple "If Then" Check Responding Unexpectedly

    Hello Everyone,

    I have what I think is a simple line of code that is not responding as I would expect and it's driving me crazy. I am building a workbook with forms where a user can input premium and retention estimates on insurance policies. I built in a check to make sure that the input box values are numeric before continuing and that worked fine but I ran into issues when I put in a line to check that the "High Estimate" values for premium and retention were higher than the "Low Estimate" values. The form is set up as follows with code for the "NextButton" click below it:

    Suggested Limit
    [ *LimitBox* ]
    Retention Amounts
    Low ----------------- High
    [ *LowRetBox* ] [ *HighRetBox* ]
    Premium Amounts
    Low ------------------ High
    [ *LowPreBox* ] [ *HighPreBox* ]

    *NextButton*

    Code (the or statement in the first If is because I have a check box to include the premium estimate as a part of another coverage line):

    Private Sub NextButton_Click()
    Dim LimNum As Long
    Dim LowRetNum As Long
    Dim HighRetNum As Long
    Dim LowPreNum As Long
    Dim HighPreNum As Long

    If (IsNumeric(LimitBox) And IsNumeric(LowRetBox) And IsNumeric(HighRetBox) And IsNumeric(LowPreBox) And IsNumeric(HighPreBox)) Or (IsNumeric(LimitBox) And IsNumeric(LowRetBox) And IsNumeric(HighRetBox) And CheckBox1 = True) Then

    If (HighRetBox > LowRetBox) And (HighPreBox > LowPreBox) Then

    LimNum = CLng(LimitBox)
    LowRetNum = CLng(LowRetBox)
    HighRetNum = CLng(HighRetBox)

    If CheckBox1 = False Then
    LowPreNum = CLng(LowPreBox)
    HighPreNum = CLng(HighPreBox)
    End If

    ActiveCell.Value = LimNum
    ActiveCell.Offset(2, 0).Select
    ActiveCell.Value = LowRetNum
    ActiveCell.Offset(0, 1).Value = HighRetNum
    If CheckBox1 = False Then
    ActiveCell.Offset(2, 0).Value = LowPreNum
    ActiveCell.Offset(2, 1).Value = HighPreNum
    Else
    ActiveCell.Offset(2, 0).Value = "Incl. in DO"
    ActiveCell.Offset(2, 1).Value = "Incl. in DO"
    End If

    CrimeForm.Hide

    Else
    MsgBox "Please assure High Retention & Premium values are larger than low values"
    End If

    Else
    MsgBox "Please enter only numbers"
    End If

    End Sub



    The problem is that the checks are all over and I can't figure out the association. I can have HighPreBox greater than LowPreBox and HighRetBox greater than LowRetBox and it still throws the else statement msgbox.

    Please help.

    Thank you,
    Bob

  2. #2
    Registered User
    Join Date
    11-11-2015
    Location
    Southend
    MS-Off Ver
    2013
    Posts
    52

    Re: Simple "If Then" Check Responding Unexpectedly

    I dont have time to look closely at the moment but a) you have no indents, b) your ifs are too complicated

    use

    if isnumeric(LoeRetBox) then
    debug.print "LoeRetbox is ok"
    else
    `show error message
    exit sub
    end if


    if isnumeric(Limitbox) then
    debug.print "Limitbox is ok"
    else
    `show error message
    exit sub
    end if
    etc


    you will soon find the logic problem
    Last edited by Graham Griggs; 01-11-2017 at 08:23 PM.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,083

    Re: Simple "If Then" Check Responding Unexpectedly

    rrdz89 & Graham Griggs,
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to Quick Post button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Last edited by protonLeah; 01-12-2017 at 01:36 AM.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 01-23-2014, 11:02 AM
  2. Excel 2010 Spreadsheet Constantly "Not Responding"
    By motenoob in forum Excel General
    Replies: 3
    Last Post: 09-25-2013, 04:25 PM
  3. Userform closing unintentionally when saving "not responding" workbook
    By MrsAlice in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-09-2012, 03:35 AM
  4. Excel and Outlook become "NOT RESPONDING" when running Macro
    By g1987 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2012, 03:28 PM
  5. "File listings stop unexpectedly - error 1004".
    By jeskit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2011, 05:45 AM
  6. "Not responding".. when Sheets("xx1").Select
    By arancia in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-02-2010, 10:23 AM
  7. [SOLVED] create links to check boxes marked "good" fair"and "bad"
    By pjb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2006, 09:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1