Thread: Prevent file save until conditions are met

Hi Guys,

I am new here and have a small problem, I am so glad to have found this forum

Well I want to prevent users from saving the sheet if the following condition is not met:

1.If any cell in column K contains "Query" in it then the cell next to it should have minimum 20 characters. (The current macro prevents users from saving when cell next to query is blank or "NA")
2.It should check for all cells that contain the phrase "Query" before letting the user save the sheet. (Currently it saves the sheet after the first condition is met and does not continue to look further)
3. Since multiple users will be using the sheet simultaneously unable to save error should come to only that user who has missed fulfilling the above condition and not to everyone.
4. The macro should not make the sheet unresponsive while saving since multiple users will be saving the sheet constantly.

This is the macro I am using currently:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rFound As Range
    Dim strFind As String
    Dim lLoop As Long
     
    On Error Resume Next
    With Sheets(1)
    
        For lLoop = 1 To 2 'Change as needed
           strFind = Choose(lLoop, "Query", "NA") 'Add to as needed
            Set rFound = .Range("K2:K50").Find(What:=strFind, After:=.Range("K2"), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False)
            On Error GoTo 0
            If Not rFound Is Nothing Then
                If IsEmpty(rFound(1, 2)) Or rFound(1, 2) = "NA" Then
                    MsgBox "Cannot Save, Missing Entry for " & _
                    strFind & " in " & Sheets(1).Name & " Range " & rFound(1, 2).Address
                    Cancel = True
                End If
            End If
        Next lLoop
        
    End With
     
End Sub

I am really really great-full for all your help

Thanks a lot

Welcome to the forum.

Please take a few minutes to read the forum rules, and then change your QUOTE tags to CODE tags.

Thanks.

Hi,

It's probably better if you upload the actual workbook so that we can see the request in context. However before doing so are you sure that you're getting the results you expect?

Excel is essentially a single user system (and I leave aside the subject of the 'sharing' functionality which still requires conflicts to be resolved), meaning that the first person to open the workbook retains control. Anyone else opening it is only opening a read only copy and they cannot make changes and save the workbook with the same name. Your comment in 3 concerns me since you seem to be expecting subsequent users to be able to update the workbook.

HTH