Results 1 to 6 of 6

Test for file open from Command Button on userform - not displaying open already message

Threaded View

nigelog Test for file open from... 06-02-2017, 05:02 AM
Norie Re: Test for file open from... 06-02-2017, 05:06 AM
nigelog Re: Test for file open from... 06-02-2017, 05:36 AM
nigelog Re: Test for file open from... 06-02-2017, 07:40 AM
Norie Re: Test for file open from... 06-02-2017, 08:39 AM
nigelog Re: Test for file open from... 06-02-2017, 09:45 AM
  1. #1
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,293

    Test for file open from Command Button on userform - not displaying open already message

    Hi all

    From a userform users can open numerous workbooks from command buttons. I am trying to stop users from opening a second copy if the workbook already open. I found and adapted the following code and it DOES stop a second copy of the workbook being opened but instead of displaying the "File already open" message, it displays the "opening file message. Have I missed something small???
    Any pointers appreciated




    Function IsOpen(wbName As String) As Boolean
        Dim Wb As Workbook
        On Error Resume Next
        Set Wb = Workbooks(wbName)
        If Err = 0 Then IsOpen = True
    End Function
     
    Sub TestTKSERVICE()
        Const sFilName As String = "P:\My Documents\Truck Files 2008\TK Service.xlsm"
         
        If IsOpen(sFilName) Then
            MsgBox "Truck Service file is already open !"
            Exit Sub
        Else: MsgBox "Opening Truck Service file"
    
            ChDir "P:\My Documents\Truck Files 2008"
            Workbooks.Open Filename:=sFilName, Notify:=False
        End If
    End Sub
    Last edited by nigelog; 06-02-2017 at 09:45 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. On current Userform, click OK Command Button to open new Window
    By eclairez7 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-21-2016, 09:53 AM
  2. Changing Command Button Caption with userform Open
    By andrew c. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-06-2011, 08:09 PM
  3. Open file with command button... but...
    By jfoerch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2011, 10:33 AM
  4. How to open URL or local html file with command button?
    By proepert in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-04-2010, 08:03 AM
  5. Userform Command Button to open new userform
    By JamesT1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2010, 12:02 PM
  6. Open .ppt file from excel command button
    By DrEvilAces in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-19-2009, 06:03 AM
  7. Command button code to write to worksheet and then open userform
    By Reidm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2008, 10:33 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1