Bestandnaam genereren bij afsluiten via Bestand Sluiten

[Noudthy]

Ik wil graag dat degene die mijn bestand sluit middels de "X" button rechtsboven in Excel een andere messagebox krijgt dan je standaard krijgt of dezelfde echter als er op Yes wordt geklikt wil ik dat mijn bestand bepaald waar deze op wordt geslagen en de naam bepalen.
Ik kom een eind met een eigen YesNoCancel messagebox gecreeerd in "Public Sub Workbook_BeforeClose" maar die doet niet wat ik wil. Cancel werkt niet, Als ik op Yes klik slaat hij niet op maar sluit wel af.

Ik doe vast wat verkeerd.... met hele avond puzzelen is het eigenlijke script behoorlijk gewijzigd. Hoop gezocht en gepuzzeld, maar ik kom er niet uit.

Public Sub Workbook_BeforeClose(Cancel As Boolean)

'Application.EnableEvents = False
'Application.ScreenUpdating = False
Application.DisplayAlerts = False
    
If Sheets("Document").Range("B7") = "Ja" Then       'Voor de beheerder (ja) om aanpassingen aan het origineel te doen
Sheets("Document").Range("M1") = ""                 'Versie terug naar leeg zetten (gebruik ik ipv datum en tijd als unieknr in bestand)
Exit Sub

    Else
    Dim Pad As String
    Bestandnaam = Sheets("Document").Range("M1")    'versie- annex volgnummer staat in M1
    Pad = Sheets("Document").Range("C3") & "\Calculatie " & Sheets("Calculatie").Range("C2") & " - " & Sheets("Calculatie").Range("B2") & " - " & Bestandnaam & ".xlsm"

    MsgBox "Bestand opslaan op lokatie en met naam: " & Pad & " ? ", vbYesNoCancel, "Afsluiten Excelbestand"
         
        If Response = vbYes Then
        ActiveWorkbook.SaveCopyAs Filename:=Pad
        Sheets("Document").Range("M1") = ""
 
        Else
            If Response = vbNo Then
            ThisWorkbook.Saved = True
            ActiveWorkbook.Saved = True
            ActiveWorkbook.Close
             
          'hier moet ik iets met Cancel, op dit moment bij klikke op cancel wordt document gesloten
          
            End If
        End If
End If

End Sub

 

[edmoor]

Wijzig :
'hier moet ik iets met Cancel, op dit moment bij klikke op cancel wordt document gesloten

In:
Cancel = True

 

[Noudthy]

Hoi Edmoor,

Logisch Cancel = True.
Vraag 1:
Maar waar zet ik deze? Ik heb hem voor de eerste If gezet, Voor de laatste End If, Na de laatse End If.
Ook onder een Else - If voor de Responce = vb Cancel.

Hij doet in al deze situaties op Yes en No ook een cancel opdracht.

Vraag 2:
Via kruisje wil ik bestand opslaan onder andere naam, maar ik krijg hierna toch de vraag van uit Excel zelf (PopUp) of ik het originele bestand op wil slaan, Pas als ik daar op Ja klik slaat hij beide uitvoeringen op. Klik ik daar op Nee dan wordt het tweede bestand met de andere naam en lokatie ook niet opgeslagen.

Hoe voorkom ik de vraag uit Excel zelf + waarom slaat hij het alternatieve bestand op als ik daar op Nee druk?
Before close script zou hij toch uit moeten voeren, voor hij de popup uit excel zelf geeft?
Als ik in mijn eigen pop up (msg box) op Ja klik, slaat hij het bestand dus al niet op

 

[edmoor]

Waar je dat moet zetten staat volgens mij duidelijk in #2.

 

[Noudthy]

Dank voor je snelle reaktie

Dat probeerde ik natuurlijk als eerste, je gaf dat ook aan.
Maar dan zit de Cancel=True in de IF vanvbNo en dus sluiten van workbook

Even stapje terug.
Wat ik wil is dat als ik op sluiten klik (kruisje rechstboven) dat, indien er in bestand wat is gewijzigd:
- een eigen VBA MSG box verschijnt met de Yes No en Cancel.
* Bij YES moet bestand weggeschreven worden onder een nieuwe naam op bepaalde lokatie
* Bij NO moet betreffende bestand sluiten, alle andere excelbestanden moeten open blijven
* Bij CANCEL moet er terug gegaan worden naar het bestand
- De opslaan pop up van Excel die je normaliter krijgt als je via rechtsboven (kruisje) het bestand sluit moet wegblijven.

Heb ik een verkeerde Sub Routine? of sucks mijn script

 

[edmoor]

Kijk eens wat die Cancel betekent in:
Public Sub Workbook_BeforeClose(Cancel As Boolean)

Plaats vervolgens die Cancel = True waar je dat wenst.

 

[Noudthy]

Na veel gepuzzel en verder uitzoeken heb ik mijn eigen vragen kunnen beantwoorden.

Ik draai nog wat testjes en zal later dan even hier de VBA script nog posten.

 

[snb]

Het lijkt mij veel handiger in de gebeurtenis Workbook_open het bestand al een naam te geven.
Dan loopt het bij workbook_close ook gesmeerd.

 

[Noudthy]

Geduld er komt wat prachtigs

 

[Noudthy]

Om het standaard afsluiten (kruisje rechtsboven) inhoudelijk aan te passen en automatisch van een andere naam te voorzien heb ik onderstaande codes.
Dus bestand krijgt een andere naam bij het opslaan met een volgnummer dan het origineel. Daarin wel de mogelijkheid voor "de beheerder" om het origineel te overschrijven.
E.e.a. incl messagebox
Hierdoor kan iedereen in het originele bestand werken, zonder het origineel aan te passen/te veranderen door het "per ongeluk" op te slaan bij het afsluiten.

Ter beeldvorming: het bestand is een calculatie spreadsheet dat op naam klant en offertenummer wordt opgeslagen. Dat deel is altijd uniek. Bij meerdere bewerkingen (opgeslagen bestanden) komt er automatisch een volgnummer achter. Waardoor eerdere versies van de calculatie niet verloren gaan.

In ThisWorkbook:

Public Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim Pad1 As String
    Dim Pad As String
    Dim Opslaan     As VbMsgBoxResult
    
    Application.EnableEvents = False
    
    If Sheets("Document").Range("B7") = "Ja" Then        'B7 kan door beheerder op Ja worden gezet, B7 staat standaard op Nee
      Pad = Me.FullName
    Else   
      
        
    Pad1 = Sheets("Document").Range("C2") & "\Calculatie " & Sheets("Calculatie").Range("C2") & " - " & Sheets("Calculatie").Range("B2") & " _ "
    
    VersieAantalXLS = TelBestandenXLS(Pad1)
    VersieTelXLS = VersieAantalXLS + 1
    
    Pad = Sheets("Document").Range("C2") & "\Calculatie " & Sheets("Calculatie").Range("C2") & " - " & Sheets("Calculatie").Range("B2") & " _ " & VersieTelXLS & ".xlsm"
    
    End If
        
        Opslaan = MsgBox("Bestand opslaan op lokatie en met naam: " & Pad & " ? ", vbYesNoCancel + vbDefaultButton2, "Afsluiten Excelbestand")
        
        If Opslaan = vbYes Then
    
            Call Workbook_BeforeSave(False, False)
            
        ElseIf Opslaan = vbNo Then

        Me.Saved = True
        
        ElseIf Opslaan = vbCancel Then
        Cancel = True
        End If
            
    Application.EnableEvents = True
End Sub

In module:

Function TelBestandenXLS(Naam As String) As Integer
    bestandXLS = Dir(Naam & "*.pdf")
    If bestandXLS = "" Then
        TelBestandenXLS = 0
    Else
        While bestand <> ""
            TelBestandenXLS = TelBestandenXLS + 1
            bestandXLS = Dir()
        Wend
    End If
End Function