Code Correction Needed

    Hi,


    Need help to correct my code. My code while closing the excel checks for if a particular cell have a defined value. "Refer Attached GL Exposure Sheet" then it checks for the worksheet. "GL Exposure Sheet". If the sheet is not attached it gives a pop up message.


    My problem is even if the cell is blank the pop up message is still displayed which i dont want.


    Current code is

    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Dim wsSheet As Worksheet
On Error Resume Next
'Set wsSheet = Sheets("GL Exposures Sheet")
'On Error GoTo 0
If Sheets("GL").Range("C42").Value = "Refer GL Exposures Sheet" And Worksheets("GL Exposures Sheet") Is Nothing Then
MsgBox "GL Exposures Sheet is Missing"
'ActiveWorkbook.Activate
Else
End If



    It will be great help if anyone can help me out.


    Regards,
    Mahesh

    Re: Code Correction Needed


    Check the sheet name in the TAB to make sure there is no leading or trailing spaces and no spelling mistakes.

    [SIZE=1]It's like asking a mechanic to fix your car, without actually taking your car to him. Post your code/file and you'll get much quicker and more accurate solutions to your problem.[/SIZE]

    Re: Code Correction Needed


    Quote from danerida;733240

    Check the sheet name in the TAB to make sure there is no leading or trailing spaces and no spelling mistakes.


    It is right. the problem is if we deleted the value "Refer GL Exposure sheet" and keep the cell blank still the query is checking the worksheet "GL Exposure Sheet". and giving the pop msg.


    The function i want is when closing an excel it shoukd check a value the related workseet attachedd or not.
    1. in Cell 42 if refer sheet is given value and worksheet not attached give me pop msgs - this is working fine
    2. in Cell 42 if refer sheet is given value and worksheet is attached no pop msgs - this is working fine
    Problem is if we leave the blank also it is showing the pop which is not neeeded
    in Cell i am not giving any comment still the code checks for attachment which will not be attached by default and gives pop msg - this is not needed.
    What needed is

    Re: Code Correction Needed


    m4mahi


    Have you read the Forum Rules?


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.
    Please make an immediate edit of your post to add the missing tags. Be sure to use them in future posts.


    [COLOR="navy"]How to use code tags[/COLOR]


    [noparse]

    Code
    [/noparse]
[COLOR="navy"]your code goes between these tags[/COLOR]
[noparse]

    [/noparse]


    Or, just highlight all of the code and press the [COLOR="#FF0000"]#[/COLOR] button to add the code tags.


    Thanks.

    Re: Code Correction Needed


    Roy help me out. now how to edit the thread i am not able to . Sorry i missed it in future i will follow.

    Re: Code Correction Needed


    Is that all the code?


    By the way, you can't really use this to check for the existence of a worksheet.

    Code
    Worksheets("GL Exposures Sheet") Is Nothing


    PS What happens if you remove On Error Resume Next?

    Boo!:yikes:

Participate now!

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

Register Yourself Login