VBA Message help

[DipDip]

Hi All,
I have used the following code I found online in my current project so that if the file it is trying to write to is being read by someone else, then it displays a prompt. It keeps on re-trying until the file is no longer read only. Finally it will write and save to the file and display a message saying it has been submitted.

Private Sub workbookRW()


Dim xlApp As Object
Dim wbTEST As Object
Dim wbRO As Boolean
Dim start As Date


Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True


Set wbTEST = xlApp.Workbooks.Open("h:\Test\Test.xlsx")


start = Now


If wbTEST.ReadOnly Then
     
    Do Until Not wbTEST.ReadOnly
    
        wbTEST.Close savechanges:=False
        
        Do Until Now > start + TimeValue("0:00:05")
             
        Loop
        
        Debug.Print "If not closed, close the original ReadWrite version now."
        
        Set wbTEST = xlApp.Workbooks.Open("h:\Test\Test.xlsx")
        start = Now
        
    Loop
     
End If


Debug.Print "Read write version should be ready now."


ExitRoutine:
    Set wbTEST = Nothing
    Set xlApp = Nothing


End Sub

However, as I have excel hidden so that only the userform shows, the debug.print line doesn't work. So i tried to change it to a msgbox, but that doesn't work for me, as it then requires the user to click on the okay button, wait 5 seconds to see if it has gone through or not and then repeat.

I also tried a show userform method, but that didn't work. I don't know why.
I had was
Pleasewait.Show

in place of

Debug.Print "If not closed, close the original ReadWrite version now."

and Unload Pleasewait

in place of

Debug.Print "Read write version should be ready now."

The Pleasewait userform is a simple label stating that they will have to wait as the file is being used by someone else.

Can someone please help me? Thanks in advance.


Dips.

 

[Domenic]

Try...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] workbookRW()

    [COLOR=darkblue]Dim[/COLOR] xlApp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] wbTEST [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] wbRO [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] start [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] xlApp = CreateObject("Excel.Application")
    
    [COLOR=darkblue]Set[/COLOR] wbTEST = xlApp.Workbooks.Open("h:\Test\Test.xlsx")
    
    [COLOR=darkblue]If[/COLOR] wbTEST.ReadOnly [COLOR=darkblue]Then[/COLOR]
    
        Me.Hide [COLOR=green]'hide main userform[/COLOR]
        Pleasewait.Show 0 [COLOR=green]'vbModeless[/COLOR]
    
        [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] [COLOR=darkblue]Not[/COLOR] wbTEST.ReadOnly
        
            wbTEST.Close savechanges:=[COLOR=darkblue]False[/COLOR]
            
            start = Now
            [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] Now > start + TimeValue("0:00:05")
                 
            [COLOR=darkblue]Loop[/COLOR]
            
            [COLOR=darkblue]Set[/COLOR] wbTEST = xlApp.Workbooks.Open("h:\Test\Test.xlsx")
            
        [COLOR=darkblue]Loop[/COLOR]
        
        Unload Pleasewait
        Me.Show [COLOR=green]'show main userform[/COLOR]
         
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    xlApp.Visible = [COLOR=darkblue]True[/COLOR]


ExitRoutine:
    [COLOR=darkblue]Set[/COLOR] wbTEST = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] xlApp = [COLOR=darkblue]Nothing[/COLOR]


[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Notice that I've made the workbook visible at the end so that you won't see the workbook being continuously opened and closed while the workbook is read only. Hopefully this should be more efficient as well.

Hope this helps!