0

I want to keep an Excel file open, even if a user clicks "X". I know that they could end the process from the task manager, but I do not think that will be the case.

Basically, an Excel sheet needs to be open 24/7 to acquire data in real time from another source. Someone or something is shutting down Excel once in a while and messing this up. I want to see if a little bit of simple VBA code will fix it.

From my research so far, I added this code:

Sub Workbook_BeforeClose(Cancel As Boolean)
    'Step 1: Check to see if cell C7 is blank
    If Sheets("Sheet1").Range("A1").Value = "" Then
    'Step 2: If cell is blank, cancel the close and tell user
        Cancel = True
        MsgBox "Cell A1 cannot be blank"
    'Step 3: If cell is not blank, save and close
    Else
        ActiveWorkbook.Close SaveChanges:=True
    End If
End Sub

And it works the first time I click X! But then, I just click it again and it lets me close everything without any message displaying. P.S. I have also tried this with just "cancel = True" in the sub. This also did not work.

What am I doing wrong here? Thanks a lot guys!

  • Are you sure there's no other code anywhere and nothing else going on? I just put this code into Excel 2013 and it works fine. – OpiesDad May 18 '16 at 20:26
  • Also, who is on the computer in question with the access to close excel? Why don't you just run this on a computer you control? – OpiesDad May 18 '16 at 20:50
  • Thanks for replying. I have re-made the file several times to try and ensure this was not the case -- so for you, it continually keeps you from closing your file until A1 is blank? –  May 18 '16 at 20:52
  • Possibly, it continually keeps me from closing the file until A1 is NOT blank i.e. it continually keeps me from closing the file WHILE A1 is blank – OpiesDad May 18 '16 at 20:57
  • That is exactly what I'm trying to get it to do. I just tried my code on a different machine and it still closes on the second try without protest. For your other question: It needs to run on the computer it is currently on. Also, we're not sure if it's a person or a random weird computer fluke closing it. Either way, I'd like to try to something similar to this code working for a first test to see if it fixes it. –  May 18 '16 at 20:59
  • Are you in Excel 2016? If so, see here: http://stackoverflow.com/questions/35607938/excel-2016-workbook-beforeclose-event-firing-every-other-time-bug This appears to be a bug. – OpiesDad May 18 '16 at 21:02
  • Hmm thank you, I do have the misfortune of working with the 2016 version. Do I just need to copy the code from your link? I'm having issues understanding how to fix this... –  May 18 '16 at 21:27
  • The link suggests that there is no fix. The comments from the OP state that he posted the issue elsewhere and as of April 13th there was no solution. They installed an update which made it a little better (so it triggered every other time), but still did not fix the issue. Might be out of luck until Microsoft deals with it. – OpiesDad May 18 '16 at 21:53
  • Wow that's crazy! Thank you for all your insight and responses. Do you think that there is any other way to do what I'm asking? –  May 19 '16 at 13:13
  • One thing you could do is hide Excel so that if someone is going on the machine, unless they go to task manager, they won't see it so couldn't close it. – OpiesDad May 19 '16 at 15:39
  • Or if you want to try something a bit strange, in the Before_close event you could open another version of the same workbook and then close the one they just tried to close. Test to see if the Before_close event works on this new workbook. – OpiesDad May 19 '16 at 15:40

1 Answers1

0

sorry i am a unix kind of person but does it really need to be opened the whole time or is it enough if it reopens as soon as it is closed. because then i would but it in a batch script. a simple loop.

for example this 

:loop 
start /wait test.txt
goto loop

will open text.txt in notepad because notepad is the default program for txt files.

you can also specify what program shall be used like:

:loop 
start /wait notepad test.txt
goto loop

i guess you could do the same with excel.

minzchickenflavor
  • 496
  • 5
  • 17
  • Hmmm, your idea might work. Can you tell me more? Forsure it would be great to use the two together. –  May 18 '16 at 20:16
  • like i said i am a unix/linux person and haven't used windows for quite a while but it should work – minzchickenflavor May 18 '16 at 20:46
  • So look into batch scripts to keep an excel file open... If I used your example, would I just=replace notepad with "excel" and test.txt with myfile? –  May 18 '16 at 20:50