Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If a Workbook is already open?
Hi
I have a macro that opens a workbook for the user to modify. However, I need to stop the user from opening the workbook for a second time. I have used the normal read only attribute which works to a degree, but if the user chooses "No" I get an error in the macro. Also I don't want the user to be able to say "Yes" and loose all the modifications made by opening the workbook again. -- Kind Regards Mick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If a Workbook is already open?
I use this function to test if a file is open before I let the user try to
open it. Function WorkbookIsOpen(wbname) As Boolean ' Returns TRUE if the workbook is open Dim x As Workbook On Error Resume Next Set x = Workbooks(wbname) If Err = 0 Then WorkbookIsOpen = True _ Else WorkbookIsOpen = False End Function I call that function with this statement: SummaryFileOpen = WorkbookIsOpen(summaryFileName) and declare 'summaryFileName' this way Private Const summaryFileName As String = "Summary.xls" I got that function from the JW book on Excel Power Programming with VBA. KL "Mick" wrote in message ... Hi I have a macro that opens a workbook for the user to modify. However, I need to stop the user from opening the workbook for a second time. I have used the normal read only attribute which works to a degree, but if the user chooses "No" I get an error in the macro. Also I don't want the user to be able to say "Yes" and loose all the modifications made by opening the workbook again. -- Kind Regards Mick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If a Workbook is already open?
Thanks Ken
Ken Thanks for the code unfortunately my knowledge is limited and I can't get it to work. I have put my code below which I have modified with what you sent, in the hopes you can assist me further. I'm not sure if I have all the elements in the correct place or not. Basically I open OpenPRM.xls which this code is in and that in turn opens Toolbox.xls, which is hidden. It then opens MasterRoster.xls and then OpenPRM.xls then closes, leaving MasterRoster.xls in view. In essence all I want to do is stop this process running again if MasterRoster.xls is already open. If I run it as it stands I get a Run-time error 91 at the "ActiveWindow.Visible = False" line. If I rem it out it loads OK the first time. If I then open it a second time I get the message "do I want to reopen or not" If I answer Yes it does If I answer No it gives Run-time error 1004 and stops at the line "Workbooks.Open FileName:=ThisWorkbook.Path & "\MasterRoster.xls", UpdateLinks:=3". Is it something to do with where I have put "SummaryFileopen = WorkBookIsOpen(MasterRoster)" Private Const SummaryFileName As String = "MasterRoster.xls" Regards and thanks again Mick Function WorkBookIsOpen(MasterRoster) As Boolean 'returns TRUE if the workbook is open Dim WB As Workbook On Error Resume Next Set WB = Workbooks(MasterRoster) If Err = 0 Then WorkBookIsOpen = True _ Else WorkBookIsOpen = False End Function Private Sub CommandButton1_Click() Workbooks.Open FileName:=ThisWorkbook.Path & "\Toolbox.xls", UpdateLinks:=3, ReadOnly:=True, ignorereadonlyrecommended:=True ActiveWindow.Visible = False SummaryFileopen = WorkBookIsOpen(MasterRoster) Workbooks.Open FileName:=ThisWorkbook.Path & "\MasterRoster.xls", UpdateLinks:=3 Workbooks("OpenPRM.xls").Close SaveChanges:=False End Sub Regards Mick "Ken Loomis" wrote in message ... I use this function to test if a file is open before I let the user try to open it. Function WorkbookIsOpen(wbname) As Boolean ' Returns TRUE if the workbook is open Dim x As Workbook On Error Resume Next Set x = Workbooks(wbname) If Err = 0 Then WorkbookIsOpen = True _ Else WorkbookIsOpen = False End Function I call that function with this statement: SummaryFileOpen = WorkbookIsOpen(summaryFileName) and declare 'summaryFileName' this way Private Const summaryFileName As String = "Summary.xls" I got that function from the JW book on Excel Power Programming with VBA. KL "Mick" wrote in message ... Hi I have a macro that opens a workbook for the user to modify. However, I need to stop the user from opening the workbook for a second time. I have used the normal read only attribute which works to a degree, but if the user chooses "No" I get an error in the macro. Also I don't want the user to be able to say "Yes" and loose all the modifications made by opening the workbook again. -- Kind Regards Mick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to: Open closed workbook/Search data tables/Return data to open workbook | Excel Discussion (Misc queries) | |||
Opening Excel, Book1 opens, remains open with other workbook open | Excel Discussion (Misc queries) | |||
how do i open a data workbook when i open a timesheet workbook | Excel Discussion (Misc queries) | |||
excel 2003 saved file will not open without a blank workbook open | Excel Discussion (Misc queries) | |||
Search open sheets in workbook and insert into open sheet | Excel Discussion (Misc queries) |