Here was my original Question,
How to check if "afile.xlsm:2" is already open VBA
I am building my Workbook on Excel 2013 and the above solution works. The Office has upgraded to 'Office 365'. I noticed after 'Excel 365' Opens the new window, it calls the open windows "aFile.xlsm - 1" & "aFile.xlsm - 2" compared to "aFile.xlsm:1" & "aFile.xlsm:2"
Since my Debugger is stating "Run-time error '9' Subscript out of range" on line
Windows("aFile.xlsm:1").Activate
, I tried changing my VBA Code to recognize "aFile.xlsm - 1" & "aFile.xlsm - 2" but to no prevail.
Function AlreadyOpen(sFname As String) As Boolean
Dim wkb As Workbook
'Dim sFname As String
sFname = "aFile.xlsm:2"
On Error Resume Next
Set wkb = Workbooks(sFname)
AlreadyOpen = Not wkb Is Nothing
Set wkb = Nothing
End Function
...omitted, what I think is unnecessary code related to this question.
Dim sFilename As String
sFilename = "aFile.xlsm:2"
If AlreadyOpen(sFilename) Then
Sheets("Sheet2").ListObjects("Table24").Range.AutoFilter Field:=5, Criteria1:=SearchString
Else
If myButton.Text = "SITE" Then
Sheets("Sheet1").Select
ActiveWindow.NewWindow
Windows("aFile.xlsm:1").Activate
Windows("aFile.xlsm:2").Activate
Windows.Arrange ArrangeStyle:=xlVertical
Sheets("Sheet2").Select
ActiveWindow.Zoom = 55
ActiveSheet.ListObjects("Table24").Range.AutoFilter Field:=5, Criteria1:=SearchString
End If
End If
Exit Sub
End Sub
How can I have this Code work on Excel 2013 and Excel 365? I'd rather not compile;
computername = Environ("computer name") 'Get computer name
username = Environ("user name") 'Get user name
into if statements.
Aucun commentaire:
Enregistrer un commentaire