+ Reply to Thread
Results 1 to 9 of 9

EXCEL VBA - EXIT SUB freeze workbook when it's TRUE

  1. #1
    Registered User
    Join Date
    09-22-2009
    Location
    Yep
    MS-Off Ver
    Excel 365 Pro Plus
    Posts
    34

    Red face EXCEL VBA - EXIT SUB freeze workbook when it's TRUE

    Hi All
    I searched for something similar but I didn't find any. I have a situation here. See described below.
    I have a code that update a template with a simple condition. If the condition is TRUE abort the UPDATE process.
    If is FALSE continue running the code:

    ...

    Sheet24.Activate
    With ActiveSheet
    LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
    If LastCol > 34 Then
    MsgBox "myText" & Chr(10) & "Update Aborted!", vbCritical, "Number of weeks exceeded."
    Exit Sub
    End If
    End With

    ...

    When I find the condition TRUE, the code STOP, EXIT but my workbook stay frozen.
    To use it again, need to close it and re-open it.
    When the condition is FALSE, everything works fine.

    Question:
    How to make the code really STOP when the TRUE is found but allowing me to use the workbook without closing it.

    I appreciate any help

    Thanks and regards
    A Mota

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: EXCEL VBA - EXIT SUB freeze workbook when it's TRUE

    Duplicate post
    Last edited by gmr4evr1; 04-22-2015 at 07:28 PM. Reason: I posted a duplicate
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: EXCEL VBA - EXIT SUB freeze workbook when it's TRUE

    I think you need the Exit Sub or maybe End Sub to be after End If and End With

  4. #4
    Registered User
    Join Date
    09-22-2009
    Location
    Yep
    MS-Off Ver
    Excel 365 Pro Plus
    Posts
    34

    Re: EXCEL VBA - EXIT SUB freeze workbook when it's TRUE

    No make sense. I can't insert an END SUB outside of my IF conditions or after finish WITH.
    Need to have END SUB only if the condition match otherwise the code doesn't run.

    Any help?
    Thanks

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: EXCEL VBA - EXIT SUB freeze workbook when it's TRUE

    Mota is this sub being called by another sub? Hard to figure out what's wrong if we don't have more context.


    First, wrap your code in code tags (highlight the code and hit the # symbol in the menu above)

    Can you post the whole macro? The small piece you posted is not enough and there is nothing wrong with it.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  6. #6
    Registered User
    Join Date
    09-22-2009
    Location
    Yep
    MS-Off Ver
    Excel 365 Pro Plus
    Posts
    34

    Re: EXCEL VBA - EXIT SUB freeze workbook when it's TRUE

    Thanks Arkadi
    This is the main SUB and I call another SUB from this if the condition is FALSE as you can see below.
    When FALSE worked perfect.

    ...

    Sub Refresh()
    Dim sFile$
    Dim LastCol As integer

    LastCol = 0
    Sheet24.Activate
    With ActiveSheet
    LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
    If LastCol > 34 Then
    MsgBox "Please close it and " & Chr(34) & "Adjust" & Chr(34) & " the Metric." & Chr(10) & "Update Aborted!", vbCritical, "Number of weeks exceeded."
    Exit Sub
    End If
    End With


    sFile = ActiveWorkbook.Path & "\" & "Excess.xls*"
    If Dir(sFile) = Empty Then
    OutPut = MsgBox("File: " & "EXCESS" & vbCr & "...was not found. Do you want to IGNORE it?", vbYesNo + vbQuestion, "File Doesn't Exist")
    End If

    Call DataInventory 'Populate Data to Inventory Projection

    MsgBox "Metric Updated!!!"

    End Sub


    ...

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: EXCEL VBA - EXIT SUB freeze workbook when it's TRUE

    So you are using a button (or some other method) to call the sub Refresh, which calls something else below? I don't understand why after exit sub the workbook would freeze. Does the messagebox show up? and freezes after you hit ok?

    A sample workbook would be great, maybe we can find a problem in IT?

  8. #8
    Registered User
    Join Date
    09-22-2009
    Location
    Yep
    MS-Off Ver
    Excel 365 Pro Plus
    Posts
    34

    Re: EXCEL VBA - EXIT SUB freeze workbook when it's TRUE

    Yes. I have a button to call SUB REFRESH. If condition is TRUE msgbox pop up and I need to hit "OK" to leave the procedure.
    After that. this workbook and any other opened workbook, stop working. I have some drop down menu to change the chart values
    but doesn't update any chart as well.

    ...
    MsgBox "Please close it and " & Chr(34) & "Adjust" & Chr(34) & " the Metric." & Chr(10) & "Update Aborted!", vbCritical, "Number of weeks exceeded."

  9. #9
    Registered User
    Join Date
    09-22-2009
    Location
    Yep
    MS-Off Ver
    Excel 365 Pro Plus
    Posts
    34

    Re: EXCEL VBA - EXIT SUB freeze workbook when it's TRUE

    I didn't attached the file because is too large. Have more than 50 sheets. Everything is working fine but this condition (EXIT SUB).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Proper syntax to exit a loop if IsError is true
    By gvaltat in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-02-2014, 06:16 PM
  2. beforerightclick,exit sub, cancel=true, macro looping problem
    By newty in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-29-2014, 07:31 AM
  3. AllowFormattingColumns:=True Doesn't Allow Freeze Panes in Excel 2007
    By davidzzzz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-12-2014, 11:51 AM
  4. IF true then Exit sub ?
    By haitham1984 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-26-2008, 03:26 AM
  5. [SOLVED] Excel: How can I freeze some of a workbook
    By Freeze unused cells in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1