PDA

View Full Version : How can I save setting for Application.EditDirectlyInCell?



frank_m
10-01-2010, 09:54 PM
When I open my workbook I am trying to save the current True/False setting Application.EditDirectlyInCell. When I open a certain workbook I want the setting to be False, and when I first tested the code below it seemed as if it worked, but only if the workbook I opened is in the same instance of Excel as another workbook that is set to True. I need it to work regardless of whether it is the same or different Excel instance.

Now when I try to test it again the the code is not working at all.

Is there a simple way to accomplish this?

Private Sub Workbook_Open()
If Application.EditDirectlyInCell = True Then
ThisWorkbook.Worksheets("DATA").Range("AE15").Value = "EditDirectlyInCell"
Else
ThisWorkbook.Worksheets("DATA").Range("AE15").Value = "DoNotEditDirectlyInCell"
End If
Application.EditDirectlyInCell = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Worksheets("DATA").Range("AE15").Value = "EditDirectlyInCell" Then
Application.EditDirectlyInCell = True
End If
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.EditDirectlyInCell = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
If ThisWorkbook.Worksheets("DATA").Range("AE15").Value = "EditDirectlyInCell" Then
Application.EditDirectlyInCell = True
End If
End Sub

macropod
10-01-2010, 11:51 PM
Hi Frank,

The 'EditDirectlyInCell' setting applies at the application level, not at the workbook level. That's why it's expressed as 'Application.EditDirectlyInCell'. Hence, if it's True for one workbook it must also be True for all workbooks and their worksheets - in the current instance. If there is another Excel instance, you'd have to explicitly query that instance. However, I can't see why you'd want to do that, since your code is trying to maintain the False state for the current workbook only - and that can only exist in one Excel instance.

For what you're trying to do within the current instance, try something along the lines of:

Option Explicit
Dim EditInCell As Boolean

Private Sub Workbook_Open()
EditInCell = Application.EditDirectlyInCell
Application.EditDirectlyInCell = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EditDirectlyInCell = EditInCell
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
EditInCell = Application.EditDirectlyInCell
Application.EditDirectlyInCell = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.EditDirectlyInCell = EditInCell
End Sub

frank_m
10-02-2010, 05:00 AM
Hi Macropod,

Since I first posted, I have come to realize that the code I posted does work. At least so far with limited testing.

Thanks so much for taking the time to write and post your code, and your informed explanation's.
In your code, the Boolean does not seem to be retaining it's value between window deactivate and activate, but it is very late here, and I'm relatively burned out, so best to take my analysis with a grain of salt for now.

In about 20 hours, I will likely get around to testing both yours and my code more thoroughly and I will let you know my further determinations then.

Thanks again

Cheers
:beerchug:

frank_m
10-02-2010, 08:56 AM
Hi again Macropod,

Upon further testing, your code works equally as well as my version. I was incorrect with my statement in my last post; the boolean value in your code does retain it's value between window deactivate and activate.

Unfortunately though, both yours and my code does under some circumstances fail to achieve my goal of maintaining the last saved setting while using other workbooks... I do need though need to wrap my head around what the exact circumstances are before I try to describe them. Unfortunately i'm not feeling sharp enough at the moment to do that, so,for that reason I am going to leave this thread as unresolved for a few more days, and I will revisit it with further thoughts then.

Ultimately I am only trying to avoid annoying my users by changing the application level setting that my users last used with their other spreadsheets. .

Thanks again for your help :friends:

Paul_Hossler
10-02-2010, 04:03 PM
I'd also include Mac's logic in the WB activate/deactivate just to make sure since one WB might have several windows open


Private Sub Workbook_Activate()

End Sub

Private Sub Workbook_Deactivate()

End Sub


Paul