Msgbox Retry/cancel buttons are confusing me

Solved/Closed
Report
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Riz,

Thanks for your reply.

This solves 1 out of 3 questions.
The Cancel button on the Msgbox will now exit the sub. Question solved.

Remaining questions:
1. The Retry button on the Msgbox will only work 1 time (or the error handling will only work 1 time). The second time a wrong entry has been made a VB error occurs instead of displaying the defined error message with the option to retry. Users will be pretty confused when suddenly the VB window pops up. What can I do to prevent this?

2. By default the Cancel button on the Inputbox will result in the value "". Is there a way to exit the sub when Cancel is chosen?

It's probably a good idea to upload my file, but I understood that you cannot acces speedyshare. Google docs doesn't seem to be suited for codes. See if you can acces this site:
http://www.mediafire.com/?pe76y6e5ocu7flr

Your assistance is much appreciated.

Best regards,
Trowa
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
I've been trying to reply for the past half hour. Have no idea my why message doesn't stick.
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Ok I tested it, and I think you were trying to say, in 2nd go round it error out. Let me see why
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Hi Trowa, you message finally showed up. The point that you raised I think are being addressed in the code at https://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
0