Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to check if another workbook is opened.
Hi to all and each one. I'm looking to make a macro in VBA for the purposed posted below: When I open the workbook "X", the macro should check if the workboo "Y" is opened already. If not, either a message box should appear o the workbook "Y" should be open. Thanks in advance, Catali -- Catali ----------------------------------------------------------------------- Catalin's Profile: http://www.excelforum.com/member.php...fo&userid=3689 View this thread: http://www.excelforum.com/showthread.php?threadid=56857 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to check if another workbook is opened.
maybe you could adapt something like this
Sub test() On Error GoTo err Workbooks("Y").Activate GoTo cont: err: MsgBox "workbook Y not open" Exit Sub cont: MsgBox "workbook y is open" End Sub -- Gary "Catalin" wrote in message ... Hi to all and each one. I'm looking to make a macro in VBA for the purposed posted below: When I open the workbook "X", the macro should check if the workbook "Y" is opened already. If not, either a message box should appear or the workbook "Y" should be open. Thanks in advance, Catalin -- Catalin ------------------------------------------------------------------------ Catalin's Profile: http://www.excelforum.com/member.php...o&userid=36896 View this thread: http://www.excelforum.com/showthread...hreadid=568571 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to check if another workbook is opened.
Hi Catalin Tri this:
in your Class module named Class1 : Public WithEvents Appl As Application Public bflag As Boolean Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook) If Wb.Name = "X.xls" Then For r = 1 To Windows.Count If InStr(1, Windows(r).Caption, "Y.xls") 0 Then bflag = True Exit For Else: bflag = False End If Next r End If If bflag Then MsgBox "Workbook Y.xls is currently opened !" Else MsgBox "You should open Workbook Y.xls first !" End If End Sub In your standard module : Dim Apl As New Class1 Sub OpenFileX() Set Apl.Appl = Application filetoopen = Application.GetOpenFilename("Text Files (*.xls), *.xls") If filetoopen < False Then Workbooks.Open filetoopen If InStr(1, filetoopen, "X.xls") < 0 Then If Apl.bflag = False Then ActiveWorkbook.Close False End If End If Apl.bflag = False Debug.Print InStr(1, filetoopen, "X.xls") End Sub Then Call openxfile procedure... Note that the Workbook name is case sensitives Thanks,, halim Gary Keramidas menuliskan: maybe you could adapt something like this Sub test() On Error GoTo err Workbooks("Y").Activate GoTo cont: err: MsgBox "workbook Y not open" Exit Sub cont: MsgBox "workbook y is open" End Sub -- Gary "Catalin" wrote in message ... Hi to all and each one. I'm looking to make a macro in VBA for the purposed posted below: When I open the workbook "X", the macro should check if the workbook "Y" is opened already. If not, either a message box should appear or the workbook "Y" should be open. Thanks in advance, Catalin -- Catalin ------------------------------------------------------------------------ Catalin's Profile: http://www.excelforum.com/member.php...o&userid=36896 View this thread: http://www.excelforum.com/showthread...hreadid=568571 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to check if another workbook is opened.
Hi Catalin,
after I try myself please change procedure of Class1 to : Public WithEvents Appl As Application Public bflag As Boolean Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook) If Wb.Name = "X.xls" Then For r = 1 To Windows.Count If InStr(1, Windows(r).Caption, "Y.xls") 0 Then bflag = True Exit For Else: bflag = False End If Next r If bflag Then MsgBox "Workbook Y.xls is currently opened !" Else MsgBox "You should open Workbook Y.xls first !" End If End If End Sub menuliskan: Hi Catalin Tri this: in your Class module named Class1 : Public WithEvents Appl As Application Public bflag As Boolean Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook) If Wb.Name = "X.xls" Then For r = 1 To Windows.Count If InStr(1, Windows(r).Caption, "Y.xls") 0 Then bflag = True Exit For Else: bflag = False End If Next r End If If bflag Then MsgBox "Workbook Y.xls is currently opened !" Else MsgBox "You should open Workbook Y.xls first !" End If End Sub In your standard module : Dim Apl As New Class1 Sub OpenFileX() Set Apl.Appl = Application filetoopen = Application.GetOpenFilename("Text Files (*.xls), *.xls") If filetoopen < False Then Workbooks.Open filetoopen If InStr(1, filetoopen, "X.xls") < 0 Then If Apl.bflag = False Then ActiveWorkbook.Close False End If End If Apl.bflag = False Debug.Print InStr(1, filetoopen, "X.xls") End Sub Then Call openxfile procedure... Note that the Workbook name is case sensitives Thanks,, halim Gary Keramidas menuliskan: maybe you could adapt something like this Sub test() On Error GoTo err Workbooks("Y").Activate GoTo cont: err: MsgBox "workbook Y not open" Exit Sub cont: MsgBox "workbook y is open" End Sub -- Gary "Catalin" wrote in message ... Hi to all and each one. I'm looking to make a macro in VBA for the purposed posted below: When I open the workbook "X", the macro should check if the workbook "Y" is opened already. If not, either a message box should appear or the workbook "Y" should be open. Thanks in advance, Catalin -- Catalin ------------------------------------------------------------------------ Catalin's Profile: http://www.excelforum.com/member.php...o&userid=36896 View this thread: http://www.excelforum.com/showthread...hreadid=568571 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to stop Excel remembering/loading macro from previously opened Workbook | Excel Discussion (Misc queries) | |||
Macro won't run in Excel 2000 when workbook opened via URL | Excel Programming | |||
How to check workbook is already opened before passing the workbook obj to a subroutine in Word | Excel Programming | |||
Launching Excel macro for every workbook opened. | Excel Programming | |||
Code to check if workbook is the last one opened | Excel Programming |