Run Workbook_BeforeClose after worksheet_change

[pavuckar]

Hello there,

I Would like to ask for help with my Excel project.
This is my VBA code:

Private Sub Workbook_Open()
Range("H9").Value = Range("H9").Value + 1
Range("B23:B29").ClearContents
Range("name").MergeArea.ClearContents
Range("sirname").MergeArea.ClearContents
Range("G18").MergeArea.ClearContents
End Sub




Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False


ActiveSheet.Copy
NewFN = "C:\Users\Lukáš IvančÃ*k\Desktop\protokol" & Range("H9").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook


Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

I need that lines under Workbook_BeforeClose will run only if for example range cell (B33:43) will contain some value.
If not, nothing do. For example, When I open this excel file but data are not input to cell, excel will not create new file like in that case I insert some data to cell.

I am helpless, please give me some advice.
Thanks so much.

 

[Joe4]

Welcome to the Board!

Try this:

[COLOR=#333333]Private Sub Workbook_BeforeClose(Cancel As Boolean)[/COLOR]
[COLOR=#333333]
[/COLOR][COLOR=#ff0000]    If Application.WorksheetFunction.CountBlank(Range("B33:B43")) < 11 Then[/COLOR][COLOR=#333333]
        Application.DisplayAlerts = False[/COLOR]

[COLOR=#333333]        ActiveSheet.Copy[/COLOR]
[COLOR=#333333]        NewFN = "C:\Users\Lukáš IvančÃ*k\Desktop\protokol" & Range("H9").Value & ".xlsx"[/COLOR]
[COLOR=#333333]        ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook[/COLOR]

[COLOR=#333333]        Application.DisplayAlerts = True[/COLOR]
[COLOR=#333333]        ActiveWorkbook.Close
[/COLOR][COLOR=#ff0000]    End If[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

 

[pavuckar]

Welcome to the Board!

Try this:

[COLOR=#333333]Private Sub Workbook_BeforeClose(Cancel As Boolean)[/COLOR]
[COLOR=#333333]
[/COLOR][COLOR=#ff0000]    If Application.WorksheetFunction.CountBlank(Range("B33:B43")) < 11 Then[/COLOR][COLOR=#333333]
        Application.DisplayAlerts = False[/COLOR]

[COLOR=#333333]        ActiveSheet.Copy[/COLOR]
[COLOR=#333333]        NewFN = "C:\Users\Lukáš IvančÃ*k\Desktop\protokol" & Range("H9").Value & ".xlsx"[/COLOR]
[COLOR=#333333]        ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook[/COLOR]

[COLOR=#333333]        Application.DisplayAlerts = True[/COLOR]
[COLOR=#333333]        ActiveWorkbook.Close
[/COLOR][COLOR=#ff0000]    End If[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

Thanks for quick reply, but It still make a new excel file when selected cell are empty.

 

[Joe4]

Thanks for quick reply, but It still make a new excel file when selected cell are empty.

Workbook_BeforeClose event procedure are not triggered off of "selected cells", like Worksheet_Change and Worksheet_SelectionChange event procedures.

I need that lines under Workbook_BeforeClose will run only if for example range cell (B33:43) will contain some value.

I thought you said that you wanted it to run if there is a value in the Range("B33:B43"), so where is this "selected cell" requirement coming from?
You may need to explain this in more detail, as it isn't too clear. Especially the need for multiple event procedures, what they are supposed to be doing, and how they should be interacting. Or maybe just explain exactly what you want to happen (forgetting about all your code for now).

 

[pavuckar]

Ok, I try to explain it. This project is for computer repair protocol in excel. Normally it does:
1. When you open file it increase protocol number +1
2. When you close file it automatically save new file with name of protocol number.

And finally, I want if you just open protocol and close it or no data are insert to protocol. Excel not save new file, just end program.

 

[Joe4]

OK, this is the part that we need to focus on.

And finally, I want if you just open protocol and close it or no data are insert to protocol. Excel not save new file, just end program.

So, how exactly can we determine if "new data is inserted to the protocol", requiring a save?

 

[pavuckar]

Ok, lets talk about I insert data about (brand of computer, name of customer, call number). When I close file it make new file with name of protocol number for example 2017001 and with data i inserted.
And next I save original file. Another customer, I open original protocol file and in some case (nothing to repair) any data are insert do protocol and I close protocol without save (but my problem is excel still make new file ). But I do not want next file (for example 2017002). I Hope you understand me.

 

[Joe4]

I understand what you are trying to do, but I don't think you have provided us with the details that we need to do it.

We have to make a determination when the file should be saved or not. So I am asking you for what is the detailed logic behind it. How can we tell that a file should be saved or not?
So it looks like there is some data entry going on. Exactly what cells is that data entry occurring in? Are those cells blank to start?
Is it as easy as saying if a specific cell has a value, then save, but if it does not, then don't save? If so, what exactly are those cell addresses?

Programming is very literal, it can only do what we tell it. So we need to give it the exact details of how it is to work.

 

[pavuckar]

Oh sorry, I understand.
When I insert data for example in cell B33 it should be save.
When cell B33 is empty, without value It should not save.

 

[Joe4]

OK, that should be a simple update to the original code I posted.
If there is only one sheet in your workbook, it would look like this:

[COLOR=#333333]Private Sub Workbook_BeforeClose(Cancel As Boolean)[/COLOR]
[COLOR=#333333]
[/COLOR][COLOR=#ff0000]    If Range("B33") <> "" Then[/COLOR][COLOR=#333333]
        Application.DisplayAlerts = False[/COLOR]

[COLOR=#333333]        ActiveSheet.Copy[/COLOR]
[COLOR=#333333]        NewFN = "C:\Users\Lukáš IvančÃ*k\Desktop\protokol" & Range("H9").Value & ".xlsx"[/COLOR]
[COLOR=#333333]        ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook[/COLOR]

[COLOR=#333333]        Application.DisplayAlerts = True[/COLOR]
[COLOR=#333333]        ActiveWorkbook.Close
[/COLOR][COLOR=#ff0000]    End If[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

If there are multiple sheets in your workbook, one way to ensure it runs against the correct one is to activate the sheet before the IF statement, i.e.

Sheets("Sheet1").Activate