+ Reply to Thread
Results 1 to 6 of 6

Why does a "do until" loop stop the subroutine?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    642

    Why does a "do until" loop stop the subroutine?

    Hello,
    Any idea why the first loop stops the sub, it doesn't proceed to the second loop..

        Do Until IsDate(EnteringDay) = True
            EnteringDay = InputBox(prompt:="What's the date? Enter in this format 01/01/2015", _
            Title:="Weight Watchers", Default:=Format(Now(), "dd/mm/yyyy"))
                If IsDate(EnteringDay) = False Then _
                    EnteringDay = InputBox(prompt:="What's the date? Enter in this format 01/01/2015", _
                    Title:="Weight Watchers", Default:=Format(Now(), "dd/mm/yyyy"))
        Loop
           
        Do Until IsNumeric(EnteringWeight) = True
            EnteringWeight = InputBox(prompt:="What's your weight? Enter in this format 75.00", _
            Title:="Weight Watchers", Default:="75.00")
                If IsNumeric(EnteringWeight) = False Then _
                    EnteringWeight = InputBox(prompt:="What's your weight? Enter in this format 90.00", _
                    Title:="Weight Watchers", Default:="90.00")
        Loop
    Please * if you like the answer

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,283

    Re: Why does a "do until" loop stop the subroutine?

    It is not proceeding because you are asking for a string, and keeping it a string.

    But also, you will always have problems with ambiguous dates - VBA is US-Centric for dates, so 5/7/2015 will be May, not July.

    So try

        While Not IsDate(EnteringDay)
            EnteringDay = CDate(InputBox(prompt:="What's the date? Enter in this format: Nov 12, 2015"))
        Wend
            
        EnteringWeight = Application.InputBox(prompt:="What's your weight? Enter in this format 75.00", _
            Title:="Weight Watchers", Default:="75.00", Type:=1)
    
        MsgBox EnteringDay & " " & EnteringWeight
    Better would be to use a calendar control - here's a workbook with an example that will work:

    Simple Calendar.xlsm
    Last edited by Bernie Deitrick; 09-28-2015 at 11:24 AM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    642

    Re: Why does a "do until" loop stop the subroutine?

    @Bernie, Thx for your help! I'll use your method for the weight question also. But how do I verify that the user enters a valid date. Right now running your code, if I enter no date at all (into the input box) the code just stops.. the do until loop was intended originally to force the user to enter a correct value into the inputbox..

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,283

    Re: Why does a "do until" loop stop the subroutine?

    You need to handle that error:

        On Error GoTo ErrHandler
    getDate:
        While Not IsDate(EnteringDay)
            EnteringDay = CDate(InputBox(prompt:="What's the date? Enter in this format: Nov 12, 2015"))
        Wend
        
        EnteringWeight = Application.InputBox(prompt:="What's your weight? Enter in this format 75.00", _
        Title:="Weight Watchers", Default:="75.00", Type:=1)
        
        MsgBox EnteringDay & " " & EnteringWeight
        
        Exit Sub
    ErrHandler:
        Resume getDate

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Why does a "do until" loop stop the subroutine?

    Give them a userform with 3 comboboxes, day, month, year

  6. #6
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    642

    Re: Why does a "do until" loop stop the subroutine?

    @Kyle - I'm trying to refrain from form in this specific case. Thanks anyway.
    @Bernie - It works just fine! and i'll also seize the opportunity to learn more about "ErrHandler"..

+ 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. Loop that waits for a subroutine to execute
    By Aristizabal95 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-02-2013, 11:38 PM
  2. [SOLVED] A Loop does not loop throught. It stops after two cells.
    By AB33 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-14-2012, 01:14 PM
  3. Subroutine loop -- Stops after first, active sheet
    By za20001 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-02-2011, 02:51 PM
  4. Moving from For...Next loop to subroutine and back to loop
    By zabrahamson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2011, 01:26 PM
  5. Deleting range of rows in subroutine loop
    By driffert in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-09-2010, 01:49 PM
  6. [SOLVED] Executing a subroutine from within a loop
    By Jeff@DE in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2006, 06:10 AM
  7. [SOLVED] Calling a subroutine in a loop
    By Jeff@DE in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2006, 06:00 AM

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