VBA - Disable Prompts

[Pwbrown7333]

Disclosure: I am not a programmer by trade... picked up somethings here and there so will caution all that my coding may be "clunky" at best and could be using older language. Digging on line and multiple google questions and looking at options has probably not been the best way to go, but I am open to try different code ways... as long as I can understand and ultimately use.

Background: Files on a sharepoint site and the files require us to open the file in actual excel app and not use the online version (macros/vba/etc) that the online does not support
Issue: Mutiple people can get in the file and unless users watch for people, we have had issues with data being overwritten, etc.
Solution: Been able to develop some VBA that unless you are the first to open the file a prompt comes up tell you the file is already open and gives the choice to open read only or to close

Current issues:
1 - When converting to read only (code being used below), we get a prompt asking if we want to save before converting to read only. Either need to auto click NO or disable this prompt all together
Code currently used for converting....

   If MsgBox("Open in Read Only?", vbYesNo, "File Open By Another User") = 6 Then
                 ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
                 Else
                 ActiveWorkbook.Close savechanges:=False
                 Application.Quit

2 - If the user clicks "NO" and does open in read only.... if they do make modifications and close the file, it still asks if they want to "Would you like to merge your changes with the latest updates on the server?". Now, we have tested and if they click "Yes", we have not seen any changes to the file online, however we don't want to cause confusion. Therefore, we would like to either auto click "NO" or disable this prompt as well.
Code being used at the before close.....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim UserClose As Excel.Worksheet
Set UserClose = ActiveWorkbook.Worksheets("User")
UserClose.Activate
Worksheets("user").Visible = True
Worksheets("user").Select
Range("B1").Select
Worksheets("user").Select
Range("B1").Value = Environ$("username")
If Range("A1") = Range("B1") Then
If MsgBox("Save File?", vbYesNo, "Saving Option") = 6 Then
UserClose.Activate
Worksheets("user").Select
Range("A1").Clear
Worksheets("user").Select
Range("A4").Clear
Worksheets("user").Select
Range("B1").Clear
Worksheets("user").Visible = False
ThisWorkbook.Save
Application.Quit
Else
UserClose.Activate
Worksheets("user").Select
Range("A1").Clear
Worksheets("user").Select
Range("A4").Clear
Worksheets("user").Select
Range("B1").Clear
Worksheets("user").Visible = False
ActiveWorkbook.Close savechanges:=False
Application.Quit
End If
ElseIf Range("A1") <> Range("B1") Then
ActiveWorkbook.Close savechanges:=False
Application.Quit
End If

Any help/guidance/assistance is greatly appreciated!

 

[Logit]

application.DisplayAlerts

 

[Pwbrown7333]

application.DisplayAlerts

Okay... did a quick lookup... so are you suggesting modifying to something like

If MsgBox("Open in Read Only?", vbYesNo, "File Open By Another User") = 6 Then
                 ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
                 Else
                 ActiveWorkbook.Close savechanges:=False
                 Application.Quit

To

If MsgBox("Open in Read Only?", vbYesNo, "File Open By Another User") = 6 Then
                 ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
                 Else
                 application.displayalerts = False
                 ActiveWorkbook.Close savechanges:=False
                 application.displayalerts = True
                 Application.Quit

Or do I even need the "= True" portion since the default is "True" and when reopening it should go back to "True"?

The second is a bit more tricky, I guess....
Since it only happens when they are not the first in... thinking the last portion of the code would need to be changed?

ElseIf Range("A1") <> Range("B1") Then
ActiveWorkbook.Close savechanges:=False
Application.Quit

to

ElseIf Range("A1") <> Range("B1") Then
application.displayalerts = False
ActiveWorkbook.Close savechanges:=False
Application.Quit

Then here again, would I need a "=True" before "Quit"?

Really does look promising, just trying to fully understand where / how to use in this instance. Also, fingers crossed I used the VBA toggle correctly to post the code this time.

 

[Logit]

Using this second statement is important because it 'turn on' the alerts for other workbooks as well the one you are working in.

application.displayalerts = True

From your comments you already have the basic understanding how to use the command. Experiment with another workbook
or a copy of your existing workbook until you have it down pat.

 

[Pwbrown7333]

Okay... any ideas when the file is now opening and the user name is "master1" (obviously I changed the name for posting) it seems that the username in A1, regardless if empty or not is being overwritten with "master1" and none of the pop ups are working?

Full code...

Private Sub Workbook_Open()
Application.DisplayAlerts = True
Dim UserOpen As Excel.Worksheet
Set UserOpen = ActiveWorkbook.Worksheets("User")
Worksheets("user").Visible = True
UserOpen.Activate
Range("A4").Select
Range("A4").Value = Environ$("UserName")
Range("A1").Select
If IsEmpty(Range("A1").Value) = True Then
Range("A1").Value = Environ$("UserName")
'disable auto save start
Dim AutoSv As Boolean
        If Val(Application.Version) > 15 Then
            AutoSv = ActiveWorkbook.AutoSaveOn
            If AutoSv Then ActiveWorkbook.AutoSaveOn = False
AutoSv = ActiveWorkbook.AutoSaveOn
End If
'disable auto save end
Worksheets(ActiveSheet.Index + 1).Select
Worksheets("user").Visible = False
ElseIf IsEmpty(Range("A1").Value) = False And Range("A4").Value = "Master1" Then
MsgBox "Be careful", vbInformation, "User in the file: " & Range("A1").Value
Worksheets(ActiveSheet.Index + 1).Select
Worksheets("user").Visible = False
ElseIf IsEmpty(Range("A1").Value) = False And Range("A4").Value = "Master2" Then
MsgBox "Be careful", vbInformation, "User in the file: " & Range("A1").Value
Worksheets(ActiveSheet.Index + 1).Select
Worksheets("user").Visible = False
ElseIf IsEmpty(Range("A1").Value) = False Then
If MsgBox("Open in Read Only?", vbYesNo, "File Already Opened By: " & Range("A1").Value) = 6 Then
Application.DisplayAlerts = False
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
Application.DisplayAlerts = True
Worksheets(ActiveSheet.Index + 1).Select
Worksheets("user").Visible = False
Else
Application.DisplayAlerts = False
Application.Quit

End If
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim UserClose As Excel.Worksheet
Set UserClose = ActiveWorkbook.Worksheets("User")
UserClose.Activate
Worksheets("user").Visible = True
Worksheets("user").Select
Range("B1").Select
Worksheets("user").Select
Range("B1").Value = Environ$("username")
If Range("A1") = Range("B1") Then
If MsgBox("Save File?", vbYesNo, "Saving Option") = 6 Then
UserClose.Activate
Worksheets("user").Select
Range("A1").Clear
Worksheets("user").Select
Range("A4").Clear
Worksheets("user").Select
Range("B1").Clear
Worksheets("user").Visible = False
ThisWorkbook.Save
Application.Quit
Else
UserClose.Activate
Worksheets("user").Select
Range("A1").Clear
Worksheets("user").Select
Range("A4").Clear
Worksheets("user").Select
Range("B1").Clear
Worksheets("user").Visible = False
Application.DisplayAlerts = False
Application.Quit
End If
ElseIf Worksheets("user").Range("A1") <> Worksheets("user").Range("B1") Then
Application.DisplayAlerts = False
Application.Quit
End If




End Sub

 

[Pwbrown7333]

Now, not working either way.... no pop ups are coming and everyone can get into the file... like something went horribly wrong with the displayalerts somewhere....

 

[Pwbrown7333]

When closing the file, each way, the tab "users" is visible to do the checks and it seems no matter who opens the file, "A1" user's name is now defaulting to whomever has the file open. At first when trying, if UserA had it open first, when userB would open the file UserA name would be in A1, but now it seems like they are not opening the same file but separate files and A1 is empty when they open it.
Did a quick "save" after opening and looking at "A1" and if empty putting in the user name thinking it would force the other person to open the file that was "saved". However, in the top right, it is showing that another user is still in the file but they are not....

Almost like there is some type of extreme server lag?

 

[Pwbrown7333]

Think we have isolated the problem... will post a new thread but thank you Logit as this did ultimately resolve this particular issue.

 

[Logit]

Glad you are making progress.

Cheers !