Remove File now available for editing

[uk747]

Currently have a Macro where a user transfers information to book2, if book 2 is currently having information transferred to it by another user the user will get a pop up saying it is being used and please try again.

However the first part happens then they get a pop up saying :-

File now available - Book 2 now available for editing, Choose Read-Write to open it for editing. (this bit I dont want)

I did try sharing book 2 but that didnt work either.

Currently I have the code below

If ActiveWorkbook.ReadOnly = True Then
MsgBox "The Workbook is being accessed by another user. Please try again. ", vbOKOnly, ""
'Exit the workbook.
ActiveWorkbook.Close False
 
Exit Sub
Else

then some code which transfers data to book 2

then

ActiveWorkbook.Save
        ActiveWindow.Close
 
        Workbooks(NewName).Activate
        CutCopyMode = False
        ThisWorkbook.Activate
 
 
 
        End If
 
 
        MsgBox "Data has been submitted sucessfully.", vbInformation, "Saved Data"
 
 
       Application.ScreenUpdating = True

All I need is to somehow avoid the Popup regarding available for editing. SO when 2 or more user transfers from Book 1 to Book 2 at the same time it will say "The Workbook is being accessed by another user. Please try again", then they run the macro again and if not used will transfer without the available for editing popup appearing

 

[nightcrawler23]

try adding Application.DisplayAlerts = False inthe starting and Application.DisplayAlerts = True in the end of your first code.

 

[uk747]

try adding Application.DisplayAlerts = False inthe starting and Application.DisplayAlerts = True in the end of your first code.

Application.DisplayAlerts = False
    
        If ActiveWorkbook.ReadOnly = True Then
            'Tell the user the workbook is not available at this time.
             MsgBox "The database is being accessed by another user. Please try again.  ", vbOKOnly, ""
            'Exit the workbook.
            ActiveWorkbook.Close False
    
Exit Sub
              Else
              
         
       Application.DisplayAlerts = True

still get the read write message do I need to add the false and true to the 2nd part of code as well

ActiveWorkbook.Save
        ActiveWindow.Close

        Workbooks(NewName).Activate
        CutCopyMode = False
        ThisWorkbook.Activate



        End If


        MsgBox "Data has been submitted sucessfully.", vbInformation, "Saved Data"


       Application.ScreenUpdating = True

 

[uk747]

Application.DisplayAlerts = False
 
        If ActiveWorkbook.ReadOnly = True Then
            'Tell the user the workbook is not available at this time.
             MsgBox "The database is being accessed by another user. Please try again.  ", vbOKOnly, ""
            'Exit the workbook.
            ActiveWorkbook.Close False
 
Exit Sub
              Else
 
 
       Application.DisplayAlerts = True

still get the read write message do I need to add the false and true to the 2nd part of code as well

ActiveWorkbook.Save
        ActiveWindow.Close
 
        Workbooks(NewName).Activate
        CutCopyMode = False
        ThisWorkbook.Activate
 
 
 
        End If
 
 
        MsgBox "Data has been submitted sucessfully.", vbInformation, "Saved Data"
 
 
       Application.ScreenUpdating = True

and are the Application.DisplayAlerts = False and Application.DisplayAlerts = True in the correct place

 

[nightcrawler23]

you havent shown ur full code.......anyways, the Application.DisplayAlerts statements should be just after start sub and end sub statements respectively.

 

[uk747]

you havent shown ur full code.......anyways, the Application.DisplayAlerts statements should be just after start sub and end sub statements respectively.

Still get same error. Tried with a user having Book2 open and get message Please Try again which is ok. Then run it when user came out of Book2 and says Saved successfully, but then get Read-Write pop UP


Full code below with Application.DisplayAlerts at start and end

Sub Macro1()
'
' Macro1

 
Application.DisplayAlerts = False
 
Application.ScreenUpdating = False
ActiveSheet.Unprotect
   
   
   Range("C101").Select
    ActiveCell.FormulaR1C1 = "Apple"
      
   NewName = ActiveWorkbook.Name
    
Workbooks.Open "S:Book2.xls"
     
        If ActiveWorkbook.ReadOnly = True Then
            'Tell the user the workbook is not available at this time.
             MsgBox "The database is being accessed by another user. Please try again.  ", vbOKOnly, ""
            'Exit the workbook.
            ActiveWorkbook.Close False
    
Exit Sub
              Else
         
                  
Workbooks(NewName).Sheets("Transfer").Range("B101:D101").Copy
Workbooks("Book2.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Workbooks("Book2.xls").Sheets("Sheet2").Range("B2:I5").Copy
Workbooks(NewName).Sheets("Transfer").Range("B105").PasteSpecial Paste:=xlPasteValues
         
             
   'Save and close YTD Spreadsheet
        
        ActiveWorkbook.Save
        ActiveWindow.Close
        
        Workbooks(NewName).Activate
        CutCopyMode = False
        ThisWorkbook.Activate
        Sheets("Transfer").Select
        
        Range("B6").Select
        Selection.ClearContents
        
 
        
        End If
        
        
        MsgBox "Thankyou.  The data you submitted has been saved sucessfully.", vbInformation, "Saved Data"
        
      
        
       Application.ScreenUpdating = True
       
      
      Application.DisplayAlerts = True
End Sub

 

[uk747]

Does it have anything to do with

MsgBox "The database is being accessed by another user. Please try again. ", vbOKOnly, ""


or MsgBox "Data has been submitted sucessfully.", vbInformation, "Saved Data"

the above two I copied from somehwere else so may need editing

Also as I still get the Read Write Error Do I need to remove the application.screendating = true at the end as thats where I get the error after it has transferred the data

 

[uk747]

Does it have anything to do with

MsgBox "The database is being accessed by another user. Please try again. ", vbOKOnly, ""


or MsgBox "Data has been submitted sucessfully.", vbInformation, "Saved Data"

the above two I copied from somehwere else so may need editing

Also as I still get the Read Write Error Do I need to remove the application.screendating = true at the end as thats where I get the error after it has transferred the data

I also removed the , vbOKOnly, "" from the end of the 1st message box and vbInformation, "Saved Data" from the end of the 2nd message box, didnt make any differnce, not even sure what thy did