VBA - Disable Prompts

Pwbrown7333

New Member
Joined
Nov 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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....
VBA Code:
   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.....
VBA Code:
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Pwbrown7333

New Member
Joined
Nov 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
application.DisplayAlerts
Okay... did a quick lookup... so are you suggesting modifying to something like
VBA Code:
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
VBA Code:
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?
VBA Code:
ElseIf Range("A1") <> Range("B1") Then
ActiveWorkbook.Close savechanges:=False
Application.Quit

to
VBA Code:
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

Well-known Member
Joined
Aug 31, 2016
Messages
4,319
Using this second statement is important because it 'turn on' the alerts for other workbooks as well the one you are working in.

VBA Code:
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.
 
Solution

Pwbrown7333

New Member
Joined
Nov 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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...
VBA 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

New Member
Joined
Nov 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Think we have isolated the problem... will post a new thread but thank you Logit as this did ultimately resolve this particular issue.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,032
Messages
5,767,750
Members
425,430
Latest member
corinaas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top