Msgbox Retry/cancel buttons are confusing me

Solved/Closed
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - May 27, 2011 at 10:47 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - 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

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 27, 2011 at 10:57 AM
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
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
May 30, 2011 at 09:14 AM
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
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
May 30, 2011 at 09:17 AM
I've been trying to reply for the past half hour. Have no idea my why message doesn't stick.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 30, 2011 at 09:42 AM
Ok I tested it, and I think you were trying to say, in 2nd go round it error out. Let me see why
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 30, 2011 at 09:55 AM
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
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 30, 2011 at 05:08 PM
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