Macro Help??

[uk747]

Tried posting before but probably not explained well.

Have a macro in Book1 which transfers data to Book2. Each user has a copy of Book1 and Book2 is stored on a shared drive. The data transfers ok, however if two or more users are transferring at the same time I want it to say "Workbook not available Please try again" then when they click it again it will transfer.

My problem is the first pop up regarding the book being busy is OK then sometimes i get - 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. I also tried Application.DisplayAlerts = False and Application.DisplayAlerts = True at the start and beginning but still get the popup.

Any help appreciated Thanks

Full Code below not sure if anything to do with ", vbOKOnly, "" or vbInformation, "Saved Data" maybe they need editing somehow

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]

Re: Macro Help?? File now available. Choose Read-Write

Any other way to avoid the popup as Display Alerts didnt work

File now available - Book 2 now available for editing, Choose Read-Write to open it for editing.

 

[uk747]

Re: Macro Help?? File now available. Choose Read-Write

Any other way to avoid the popup as Display Alerts didnt work

File now available - Book 2 now available for editing, Choose Read-Write to open it for editing.

maybe to simplify things is it possible just to do a private sub on Book1 so that if Choose Read-Write to open it for editing appeared it would select cancel automatically so the user wouldnt even see the box and have time to select read-write

 

[uk747]

 

[uk747]

using excel 2003 not sure if that makes any difference.