Msgbox Retry/cancel buttons are confusing me [Solved/Closed]

Posts
2440
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 13, 2018
- - Latest reply: TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 13, 2018
- Jun 3, 2011 at 07:50 AM
Hello,

I can't figure out what's wrong with my code. Hopefully someone can enlighten me.
Here is the code I'm using:

Dim x As Integer
Sub AdjustChart()
    Application.ScreenUpdating = False
        
    ActiveSheet.ChartObjects("Grafiek 1").Activate
    ActiveChart.PlotArea.Select

    StartMonth = "Jan 2009"
CM:
    CurrentMonth = InputBox("Voer laatste datum in (mmm jjjj)")
    On Error GoTo ErrorMessage
  
    DiffMonths = DateDiff("M", StartMonth, CurrentMonth)
    x = 3 + DiffMonths
    
    ActiveChart.SeriesCollection(1).XValues = "=Data!R3C3:R3C" & x
    ActiveChart.SeriesCollection(1).Values = "=Data!R38C3:R38C" & x
    ActiveChart.SeriesCollection(2).XValues = "=Data!R3C3:R3C" & x
    ActiveChart.SeriesCollection(2).Values = "=Data!R37C3:R37C" & x
    ActiveChart.SeriesCollection(3).XValues = "=Data!R3C3:R3C" & x
    ActiveChart.SeriesCollection(3).Values = "=Data!R39C3:R39C" & x
    ActiveChart.SeriesCollection(4).XValues = "=Data!R3C3:R3C" & x
    ActiveChart.SeriesCollection(4).Values = "=Data!R40C3:R40C" & x
     
    Application.ScreenUpdating = True
    
    Exit Sub

   
ErrorMessage:
    MsgBox ("De opgegeven datum komt niet overeen met het type (mmm jjjj), als in ""Jan 2011""."), vbRetryCancel, "Foutmelding"
    If vbRetry Then GoTo CM
    If vbCancel Then Exit Sub
End Sub

On the 8th line: CurrentMonth = InputBox("Voer laatste datum in (mmm jjjj)")
Users need to input a date with format MMM YYYY. When they don't an error occurs. The code redirects to ErrorMessage with a Retry and Cancel button. When Retry is chosen the code redirects to CM.
So far so good.
Now for the part that is confusing me:
When Cancel is chosen, the code also redirects to CM while I want to exit the sub. Why does this happen?

After choosing Retry the Inputbox appears for the second time, but when another wrong entry has been made I would expect the code to redirect to ErrorMessage again to make the Msgbox to appear again with the buttons Retry and Cancel.
Instead of this a VB error occurs (error 13, types doesn't match) highlighting this line in yellow: DiffMonths = DateDiff("M", StartMonth, CurrentMonth)
Why doesn't some sort of loop occur?:

Inputbox = error
goto ErrorMessage
Click on Retry
goto CM
Inputbox = error
goto ErrorMessage
etc...

One last thing. The inputbox comes with an OK and a Cancel button. Why doesn't the code exit itself when Cancel is chosen?

Could someone please shine some light on my button issue?

Best regards,
Trowa

See more 

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
0
Thank you
Hi Trowa

I think you are not assigning the value from msgbox to any variable

   
   I = MsgBox("De opgegeven datum komt niet overeen met het type (mmm jjjj), als in ""Jan 2011"".", vbRetryCancel, "Foutmelding")
   If (i = vbRetry) Then
      GoTo cm
   ElseIf I = vbCancel Then
      Exit Sub
   End If
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Try this Trowa . Basically replaced "Goto CM" with "Resume CM" and some minor adjustments

 Dim x As Integer  

Sub AdjustChart()  
    Application.ScreenUpdating = False  
          
    ActiveSheet.ChartObjects("Grafiek 1").Activate  
    ActiveChart.PlotArea.Select  

    StartMonth = "Jan 2009"  
CM:  
    CurrentMonth = InputBox("Voer laatste datum in (mmm jjjj)")  
    If (CurrentMonth = vbNullString) Then Goto Exit_AdjustChart 
    On Error GoTo ErrorMessage  
    
    DiffMonths = DateDiff("M", StartMonth, CurrentMonth)  
    x = 3 + DiffMonths  
      
    ActiveChart.SeriesCollection(1).XValues = "=Data!R3C3:R3C" & x  
    ActiveChart.SeriesCollection(1).Values = "=Data!R38C3:R38C" & x  
    ActiveChart.SeriesCollection(2).XValues = "=Data!R3C3:R3C" & x  
    ActiveChart.SeriesCollection(2).Values = "=Data!R37C3:R37C" & x  
    ActiveChart.SeriesCollection(3).XValues = "=Data!R3C3:R3C" & x  
    ActiveChart.SeriesCollection(3).Values = "=Data!R39C3:R39C" & x  
    ActiveChart.SeriesCollection(4).XValues = "=Data!R3C3:R3C" & x  
    ActiveChart.SeriesCollection(4).Values = "=Data!R40C3:R40C" & x  
       
    Goto Exit_AdjustChart 
     
ErrorMessage:  
 I = MsgBox("De opgegeven datum komt niet overeen met het type (mmm jjjj), als in ""Jan 2011"".", vbRetryCancel, "Foutmelding")  
   If (i = vbRetry) Then  
      Err.Clear  
      Resume CM  
   End If  

Exit_AdjustChart: 
    On Error goto 0
    Application.ScreenUpdating = True  
End Sub
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Hi Trowa, you message finally showed up. The point that you raised I think are being addressed in the code at http://ccm.net/forum/affich-593748-msgbox-retry-cancel-buttons-are-confusing-me#6

After inputbox there is a check to see what value is being passed. If it is "". then control is directed to exit of sub. The reason I have a "goto", instead of direct "exit sub" was due to fact that you were freezing the screen update before error could occur. So when exit sub occurs, we should reset the it back to true ( I think). Of course a simple IF block could have done the trick, but I tend to believe that all exit from function and sub should be done at one spot (or as much as possible as I find it easier to run debug)

Also I think only thing that you need to check is retry. As if user selects retry you want to send the user back to select a valid value. For any thing else, I am guessing user wants to get out. So that is the reason in ErrorMessage, I am only testing for retry and if it is a retry that send the control back. If you notice I have used a Resume Cm instead of Goto CM. In case of a loop, first time error handler will work but later on it will give you an error. By clearing the error and saying resume from that label we avoid that situation.

Last is one more call to on error goto 0. This is to remove the On Error goto ErrorMessage handle. This resets error handler back to VBA default. So when error occurs, you get the error message as if you never had an error handler

Sorry for not being clear in explanation above, but hope it at least helps is some what.

thanks
TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 13, 2018
-
Hi Riz,

You did it again; made my code run as intended.

Thanks for your explanation as well, gives me a better understanding of error handling.

Best regards,
Trowa
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
You are very welcome Trowa. You made me learn error handling in a loop situation, so thank you too for giving me that chance

No need to change the code, but I think our approach in error handling was not the best approch. We should have verifed that data is good data or at least as good as we think it should be. And then should have error handler as last resort of defense. May be this approach to would be better
   Do
      CurrentMonth = InputBox("Voer laatste datum in (mmm jjjj)")
      CurrentMonth = Trim(CurrentMonth)
      If (CurrentMonth = vbNullString) Then GoTo Exit_AdjustChart
      If (IsDate(CurrentMonth)) _
      Then
         Exit Do
      End If
      Beep
   Loop While True


or this more exhaustive approach where pretty much leaving nothing to chance.
   Do
      CurrentMonth = InputBox("Voer laatste datum in (mmm jjjj)")
      CurrentMonth = Trim(CurrentMonth)
      If (CurrentMonth = vbNullString) Then GoTo Exit_AdjustChart
      If (Len(CurrentMonth) = 8) _
      Then
         If (IsDate(CurrentMonth)) _
         Then
            If Not IsNumeric(Left(CurrentMonth, 3)) _
            Then
               If (Mid(CurrentMonth, 4, 1) = " ") _
               Then
                  If IsNumeric(Right(CurrentMonth, 4)) _
                  Then
                     Exit Do
                  End If
               End If
            End If
         End If
      End If
      Beep
   Loop While True
   
TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 13, 2018
-
Thanks for sharing Riz.

The second one looks artistic.

Best regards,
Trowa