200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Greetings,
I am having instance when EXCEL was in edit mode and have to switch to MS Word for editing a document.
Is there any VBA code that detect EXCEL is in EDITING Mode from other ACTIVE Application (Other Application software is in actve running or editing mode).
Excel will not permit VBA code to run while it is in edit mode. Hence it is impossible for VBA code to detect that mode.
The need for such detection would arise when a user starts running VBA code in another application which refers to the Excel workbook which he didn't finish editing. Such a condition is more likely to occur during testing than in real life. You might exclude the possibility by creating a new instance of Excel (not using any existing one in which the workbook might be open) and open a fresh copy of the workbook in that new instance. You might use parts of the following code.
Option Explicit
Private Sub GetExcel()
' ==============================================================
' for this code to run, a reference to
' Miscrosoft Excel Object Library must be set
' ==============================================================
Const Ffn As String = "D:\My Documents\Workbook.xlsx"
Dim XlApp As Excel.Application
Dim Wb As Excel.Workbook
' there will be no error here if the Object Library is available
Set XlApp = CreateObject("Excel.Application")
' Set Wb = XlApp.Workbooks.Add(xlWBATWorksheet) ' single sheet only
' Wb.Sheets(1).Name = "New Tab"
' ' The name will be "Sheet1" which is confusing:
' ' The workbook has no name until it is saved.
' ' Therefore it takes the name of its first sheet.
' ' However, the "Name" of the first sheet is "New Tab"
' ' The name given to the nameless workbook is the worksheet's "CodeName".
' MsgBox Wb.Name
On Error Resume Next
Set Wb = XlApp.Workbooks.Open(Ffn, ReadOnly:=True)
If Err Then
MsgBox "I couldn't open the workbook" & vbCr & _
Ffn & vbCr & _
"Check the specified name and path.", _
vbCritical, "File not found"
Else
MsgBox Wb.Name
' do what you want to do with the workbook here
' you probably will prefer to keep it invisible.
' Therefore never Activate it and never make any Selection in it.
' For example:
' Wb.Worksheets(1).Activate
' Cells(1, "A").Select
' ActiveCell.Value = 123
' all of the above has the simple meaning of:
' Wb.Worksheets(1).Cells(1, "A").Value = 123
Wb.Close SaveChanges:=False
End If
Set XlApp = Nothing
End Sub
The remmed-out part of the code shows how to add a new workbook with a sinlke sheet (like your own attempt does). Below that is the code for opening an existing workbook. In my tests the workbook opened regardless of whether it was already open or closed and even if it was in edit mode.