Problem with VBokcancel returning either 1 or 2

  • I am no expert by any means in VBA. I have created some code which does what it should but I have a minor problem
    I have a message box displayed with vbOKCancel. On clicking cancel I get returned in a cell 2, if clicking ok 1 is returned. I know that is a result of clicking the button as I tried bypassing the message box and the code worked fine but equally I know clicking cancel it Exits the sub. Is there anyway I can stop the numbers appearing? Or do I just have the 1 or 2 finishing in any blank cell?


    It may just be me with the way I have created the code.


    Thanks a lot

  • Re: Problem with VBokcancel returning either 1 or 2


    Hello,


    It would be great ...if you could post your code ... to see what might need to be modified ...


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Problem with VBokcancel returning either 1 or 2


    Quote from Carim;793771

    Hello,


    It would be great ...if you could post your code ... to see what might need to be modified ...


    HTH


  • Re: Problem with VBokcancel returning either 1 or 2


    Hello,


    The structure of your code looks fine ...


    So could you attach a sample workbook in order to further analyze your problem ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Problem with VBokcancel returning either 1 or 2


    Thanks so much for coming back Carim.


    I am doing this spreadsheet to try and help a nearby neighbour with his weather data. I am still waiting on him for exactly which data he wants and where, this was really just an example.


    Please don't worry, as long as the code is fundamentally ok I am happy to accept this, as long as there were no obvious errors which was the main thing. Initially in my code the 1 or 2 was appearing in whichever was active cell which was taking out entered data which was a bit annoying. Once I discovered the cause I selected a cell range at the end of the code.


    Equally my neighbour will be delighted with the end result from what he was doing previously and besides I may need help further down the line! :)


    Once again many thanks

  • Re: Problem with VBokcancel returning either 1 or 2


    Hello again,


    Do not hesitate to come back to the Forum ...:wink:


    There are many talented contributors who will happy to give you a hand ... should you need it ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Problem with VBokcancel returning either 1 or 2


    The problem is the fact you are using Selection = which will populate the selected cells with the value of the MsgBox result. If you don't want that, use a variable to store the result and check that variable before continuing, or simply use:


    Code
    If MsgBox(" based on your selected criteria you will be copying the data below to it's appropriate table." _
    & vbCrLf & "" & vbCrLf & "HIGH : " & Range("h4").Value & vbCrLf & "LOW : " & Range("I4").Value & vbCrLf & "AVERAGE : " & Format(Range("j4").Value, "0.0") & vbCrLf & "RAINFALL : " & Range("k4").Value _
    & vbCrLf & "RAINY DAYS : " & Range("l4").Value _
    & vbCrLf & "" & vbCrLf & "The data will be sent to " & Range("l1").Value & " : " & Format(Range("l2").Value, "mmmm-yy") _
    & vbCrLf & "Based on your Start date of : " & Format(Range("i2").Value, "dd-mmm-yy") & " and your end date of : " & Format(Range("k2").Value, "dd-mmmm-yy"), vbOKCancel, "WEATHER DATA UPDATE") = vbOK Then

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Problem with VBokcancel returning either 1 or 2


    Brilliant Rory - thanks so much, exactly what I wanted.
    Had almost got to the point of taking out the message box statement totally

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!