Solved

Control Pop up window

Posted on 2016-09-17
7
38 Views
Last Modified: 2016-10-07
I am trying to open a file and if the file is already open, then a pop up window appears ... mentioning the file is already open then we press No button. I want a vba to control this of pressing "No" button through excel vba code.

Dim str As String
str = "C:\Users\Desktop\Test.xlsm"

On Error GoTo Solution
Workbooks.Open str

'------
Exit Sub
Solution:

MsgBox "file is open"
Resume Next
0
Comment
Question by:Alok Kumar
  • 4
  • 3
7 Comments
 
LVL 17

Accepted Solution

by:
Roy_Cox earned 250 total points (awarded by participants)
ID: 41802693
I would suggest using a function to test if a workbook is open
Sub TestOpen()
If Not wbOpen("C:\Users\Desktop\Test.xlsm") Then
    Workbooks.Open "C:\Users\Desktop\Test.xlsm"
End If

End Sub

Alternative:

Private Function WorkbookIsOpen(FileName) As Boolean
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(FileName)
If Err = 0 Then
WorkbookIsOpen = True
Else
WorkbookIsOpen = False
End If
End Function

Open in new window

0
 
LVL 19
ID: 41802696
I agree with Roy -- rather than dismissing the dialog box if it appears, ensure it does not ~

realize that you need just the filename (Test.xlsm) to use the given code, not the path and filename
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41802719
Hi Crystal

I just copied and pasted the file name and forgot to edit

This is my usual example with this Function

Sub TestOpen()
If Not wbOpen("Suppliers.xls") Then
    Workbooks.Open "Suppliers.xls"
End If

End Sub

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points (awarded by participants)
ID: 41803133
Roy, I modified your code to open the file if it is not already open. It takes path\filename as a parameter, sets a variable for just the filename to test and opens path\filename if not already open.  It returns the workbook object so it can be used in the code for whatever called it. I would also make this public function so it can be called from anywhere :)
Public Function WorkbookOpenIfNot(pPathFileName) As Workbook
'160917 strive4peace
'based on code written by Roy Cox
'return a workbook object
   On Error GoTo Proc_Err

   Dim wb As Workbook
   Dim sFilename As String _
      , iPos As Integer
   iPos = InStr(pPathFileName, "\")
   If iPos > 0 Then
      sFilename = Mid(pPathFileName, iPos + 1)
   Else
      'maybe just the filename was passed
      sFilename = pPathFileName
   End If
   
   On Error Resume Next
   Set wb = Workbooks(sFilename)
   If Err.Number = 0 Then
      Set WorkbookOpenIfNot = wb
   Else
      'not already open -- try to open
      If iPos > 0 Then
         Err.Clear
         Set wb = Workbooks.Open(pPathFileName)
         If Not Err.Number > 0 Then
            Set WorkbookOpenIfNot = wb
         End If
      End If
   End If
Proc_Exit:
   On Error Resume Next
   'release object variables
   Set wb = Nothing
   Exit Function 'or Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   WorkbookOpenIfNot "

   Resume Proc_Exit
   Resume
End Function

Open in new window

here is a Sub to test it:
Sub testWorkbookOpenIfNot()
'160917 s4p
   Dim wb As Workbook
   Dim sPathFile As String
   
   '-------------------------------- pick ONE and comment the rest
   sPathFile = "JustFilename.xlsx"
   sPathFile = "C:\path\filename.xlsx"
   sPathFile = "bogus.xlsx"
   '------------------------------------
   
   Set wb = WorkbookOpenIfNot(sPathFile)
   If wb Is Nothing Then
      MsgBox sPathFile & " could not be opened"
   Else
      MsgBox wb.Name, , "Workbook name"
   End If
   Set wb = Nothing
   
End Sub

Open in new window

for information on error handling:

basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html
0
 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 250 total points (awarded by participants)
ID: 41803786
Hi Crystal.

Nice code, normally I would go with something like below, simply because the OP has the path already in the code.

Sub TestOpen()
If Not wbOpen("Suppliers.xls") Then
    Workbooks.Open "C:\Users\Desktop\Test.xlsm"
End If

End Sub

Open in new window


I'll have a closer look at your code later.
0
 
LVL 19
ID: 41803817
Roy, thanks. Your code is simpler. I like the idea and it is a nice function to add to my library.

btw, if you don't want to handle a return value (ie: x) then you can test the result without assigning it, for instance:
Sub testNoObjectVariable()
   On Error Resume Next
   Dim sName As String
   sName = ActiveWorkbook.Name
   Err.Clear
   If Workbooks(sName) Is Nothing Then
      MsgBox sName & " is not open" 'this will not execute
   End If
   sName = "bogus " & sName
   Err.Clear
   If Workbooks(sName) Is Nothing Then
      MsgBox sName & " is not open" 'this will (probably!) execute
   End If
End Sub

Open in new window

0
 
LVL 19
ID: 41833336
poster didn't come back -- probably new to VBA
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now