Prevent file save until conditions are met

[Varun_21]

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

[shg]

Welcome to the forum.

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

Thanks.

[Richard Buttrey]

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

[Varun_21]

Hi Richard,

I have added the sheet with comments with this reply. The third point of my original post refers to multiple users on a shared workbook. Since the sheet will be shared and multiple users will work on it I was just skepticle if the error will be ment for the user who did not meet the condition or to everyone on the sheet. I guess I was just getting a bit paranoid there, as far as I understand ti should work on the logic of user edit and give the error message to that specific user. Please tell me if I am wrong here.

The main purpose of the macro is to get the user to enter a detail comment of the Query he faces in any particular case. He/She should not be able to proceed or Save till the time he gives a detailed comment in the cell adjescent to Query. This should not (I hope) hamper the work of others working on the shared workbook.

Thanks