Excel.. Exit Edit Mode [RESOLVED.. finally :-P]

[SpeedyDog]

Ok, I've been working on a program it just opens excel, and you can edit stuff, and has an update action, which transfer to and from the excel sheet that you can see and edit...

Well if you are like in a cell typing... say you double clicked on it and you get a cursor inside of it in excel. well if you then push the update button it hangs the application and if u try to do anything you get one of those "Component Busy" Error Messages.

So Here's the question:
1) Is there away to say "exit out of edit mode"... so basically when I go to update I can say, get out of edit mode, because it updates perfectly fine if you arn't in Type(Edit) Mode on a cell.

2) This just came to mind as I was typing this. Is there away to remove the Component Busy Error Message.. by somehow just not displaying it rather just do something else :-P ... I just hate how it looks when stuff hangs...

Well, I hope someone can help me as soon as possible... Thanks

SpeedyDog

[RobDog888]

Speedy, is the update button on the spreadsheet or is it a Vb Command button on a VB Form?

I dont think you can get rid of the Component Busy error since it comes from
VB and is not accessable. The only thing you may be able to do is use
FindWindow APi in a timer event to look for the Component Busy dialog. Then
maybe you could click switch or whatever it says.

[SpeedyDog]

The Update Button is on the VB PROGRAM... basically it just copies data from 1 excel sheet to anotehr... and vise versa back in... ummm and it locks up if you are in edit mode..

-SpeedyDog

[RobDog888]

What if, before you make the copy/paste, you change the selection to
another row and then back to the sorce/target row? That should take it out
of edit mode.

[SpeedyDog]

xlSheetInput.Cells(1, 1).Select

ERROR:
Select Method or Range Class Failed.


ahhhh!!!!... why doesn't that work?!

-SpeedyDog

[RobDog888]

Ok, here is the perfect solution. Disable editing on just your application object.
It will not effect any other instances before or after, that the user may or
may not have opened already or will open.

Option Explicit
 
Private moApp As Excel.Application
Private moWB As Excel.Workbook
    
Private Sub Command1_Click()
    moWB.Sheets("Sheet1").Cells(1, 1).Value = "Test"
    moWB.Sheets("Sheet1").Cells(1, 1).Copy
    moWB.Sheets("Sheet2").Activate
    moWB.Sheets("Sheet1").Paste Destination:=moWB.Sheets("Sheet2").Cells(1, 1)
End Sub
 
Private Sub Form_Load()
    Set moApp = New Excel.Application
    moApp.Visible = True
    Set moWB = moApp.Workbooks.Add
    If moApp.EditDirectlyInCell = True Then
        moApp.EditDirectlyInCell = False
    End If
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    If moApp.EditDirectlyInCell = False Then
        moApp.EditDirectlyInCell = True
    End If
    moWB.Close False
    moApp.Quit
    Set moWB = Nothing
    Set moApp = Nothing
End Sub

[SpeedyDog]

This doesn't work?....

goto sheet 2, and start typing in say 3,5 for example just type in randomness, don't exit out of edit mode... and then push Command1... it deosn't write text, or copy it until AFTER you exit out of edit mode :-/

-SpeedyDog

[RobDog888]

If you noticed that I have code in the Form_Load event to disable the edit
capability. Then if the user tries to edit anything in Excel, it wont go into
editmode.

Private Sub Form_Load()
    Set moApp = New Excel.Application
    moApp.Visible = True
    Set moWB = moApp.Workbooks.Add
    If moApp.EditDirectlyInCell = True Then
        moApp.EditDirectlyInCell = False 'TURNS EDIT MODE OFF
    End If
End Sub

[SpeedyDog]

that doens't turn off Edit mode for my version of Excel? EXCEL XP version 10.x .... I even ended up taking out that if statement and just making it false.. and I can still edit :-/ But people need to beable to edit...

I need to EXIT the edit mode, when they click the update...

-SpeedyDog...

P.S.
Project DueDate for client is Monday :-/ (edit: wow, acutally not till the 1-10-2005)

I mean, things work fine, just it hangs if they don't exit out of edit mode when they go to update, and since they are paying $3,000/month for this program I figured I should get that fixed :-P

[RobDog888]

So if I get it working for you do I get $3k???

[SpeedyDog]

lol no, unfortunately, I'm just the 16 year old programmer they hire for 11 dollars an hour :-P

-SpeedyDOg

[RobDog888]

So, to test it out for Excel XP, if you add all my code to a new vb project will
it work the way I posted? Start the vb project and after the workbook is
open and set, click in the spreadsheet to try to send it into edit mode. It
should not go into edit mode and the copy/paste code should work.

[SpeedyDog]

I can go into edit mode still, by dbl clicking on a cell.... I will test it agian just for the fun of it, I did make a new project and added the Excel 10.0 Library. It was a real method. But anyways, that's not how it needs to be done, it needs to like EXIT edit mode... I've almost thought of this...

turning off 'Screen Update', Temp Saving, Closing, Reopening, THEN DOING UPDATE, turn on 'Screen Update'

That seems to uneffiencient, and also, gets weird about making lots of different temp files and saving as different locations then where it really is .. alll that weird stuff...

-Speedy

P.S. Still looking for a more effiecent way, like just a method that will get me out of edit mode. I can't just save and reopen, because it doesn't always WANT to save (based on options)... so ummm thats why I would have to do the temp file..

[SpeedyDog]

see:

EDIT: I removed the image from my server, it's not important.

-SpeedyDog

[RobDog888]

This is one that I found that looked promissing, but

[SpeedyDog]

PivotTable Object...?

[RobDog888]

I know, but I thought there may be a similar method for the sheet object.
That is all there was on M$ so it doesn't look good at all.

[SpeedyDog]

no there isn't. My Saving Method doesn't even work :-/
So now, I'm no where...


errr... well it works in that little thing that you have setup, just doesn't work in my program :-/ I will look at it tomorrow...

-SpeedyDog

[RobDog888]

Ok, and have a Happy New Year!

8 hours and 20 mins to go!
I will be on tomorrow for a little while. I am going to be watching the Trojans
win and eating allot of food all day!

[SpeedyDog]

Well yup, defanitly still stuck, the save method works with your little simple code, but doesn't seem to work with my code, I can't do anything with the workbook at all, any method with out it hanging... so saving doesn't even seem to work :-/ .... Ummm, I guess I will just hope they exit out of edit mode before they click on the update button :-/ .... Or I could move their cursor ... click on a cell, then move their cursor back :-P (instantaniously)


-SpeedyDog

[RobDog888]

Yes, if you use APIs to move the mousepointer and make it click another cell
or send an escape keypress that may work too.

[betelgeuse]

SpeedyDog, I'm having the exact same problem. I am developing an app that uses the Microsoft Office Framer control (http://support.microsoft.com/default...B;EN-US;311765). I simply want to access the "saved" property of the Active Document to know whether or not to display a save dialog. However, you can't can't access ANY of the properties or methods of the object when a user is editing a cell in Excel without it hanging up.

I am working with a Microsoft support rep, so hopefully he can give me a solution. I'll post it here if he does.

[betelgeuse]

Speedy, I found this solution over here --> http://www.visualbasicforum.com/show...oto=nextnewest

VB:
--------------------------------------------------------------------------------
Option Explicit
 
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
 
Private Sub Command1_Click()
App.OleServerBusyRaiseError = True
App.OleServerBusyTimeout = 1000
 
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
 
With xlApp
    .Visible = True
    .Workbooks.Add
End With
 
'Sleep for a bit so the tester can put a cell into edit-mode
Sleep 10000
 
On Error Goto ErrorHandler
 
With xlApp
    .ActiveSheet.Cells(5, 5).Value = 47
    .Workbooks.Close
    .Quit
End With
 
Exit Sub
 
ErrorHandler:
    If Err = &H80010001 Then
       AppActivate "Microsoft Excel"
       'Break the edit-mode.
       'SendKeys "{Esc}"
       MsgBox "Excel is in edit-mode"
      Resume
    End If
End Sub

This won't exit edit mode, but it does stop it from hanging and allow you to prompt the user to exit edit mode first. The "AppActivate" line generates a run-time for me, but that's probably because I'm using the framer control. I'll still post a solution from MS if I get it, but I'm going to use this in the meantime.

[SpeedyDog]

Wow, this is a good enough solution for me, I'm doing the AppActivate... sometimes I use to get an invalid argument call on it or something, and not sure how I fixed it, I added in a doevents and I also made the excel name longer, like I knew that an Input book was opened so I had it do the call with "Microsoft Excel - Input" so that was good for me. That may have been why. This fixed my problem, because I can turn the Timeout time really low while doing the updates... but if it's that low any other time, while just messing with the sheet it would error and crash the sheet basically, so, during just EDIT TIME I have the Timeout set to like 60,000 because, if they are to sit in edit mode for awhile, this is when the timeout starts... so it gives them plenty of time.. but when the program is ready to update, I set it to 2000. This basically will trigger an error as soon as it's been busy for 2 seconds, and being busy is usually caused by being in EDIT MODE. like I have a timer on my form, and while it's in EDIT mode on a cell, the timer doesn't run at all, very weird how hoggy the excel becomes over the program.

So here is what I have, which I think I will still modify:

Public Sub CancelUpdate()
        DoEvents
        App.OleServerBusyTimeout = 60000
        frmMain.Enabled = True
        frmMain.lblStatus.Caption = "Update canceled"
        SetIcon Normal
        DoEvents
        Sleep 200
        DoEvents
        AppActivate "Microsoft Excel - input"
        Sleep 200
        SendKeys "{ESC}"
        Sleep 200
        AppActivate "Predictive Model Viewer"
        MsgBox "Update Canceled! " & vbCrLf & "Usually caused by being left in edit mode in excel.", vbCritical, "Update Canceled"
End Sub

This works out very well for me, I'm still going to change some things maybe, because I'm not sure if I need to display a msg or anything, I may just do the Escape on excel, and just like... be done with it, I may add that in the options menu to display an error on that, or just "continue as normal" I think I also plan to set my Systray Icon to have a new icon called ERROR, well it displays the icon with like a little red X on it, to show that an error had just occured.

Well thanks for all of your help, also, betelgeuse, if you get that stuff from M$, or any other information about it, feel free to post it here, or give me a PM, I would be happy to see other ways of going about it still.

-Setting as RESOLVED

-SpeedyDog

[SpeedyDog]

ok, I totally fixed it, it will basically exit out of edit mode, not even show an error message.

Instead of sending Keys Exit, which would clear it, I realized, it's better to send the Key Enter, because that will submit the data, which is probably what they wanted to do in the first place, so basically if there is an ever an error raised....

then I will call a sub such as Fix_Update or something. that will basically appactivate the excel sheet, then send the keys of enter, which makes it think that nothing ever happened, then resume with where the code set off, fixing the "TimeOut" issue...

Great Job Guys I hope other people will use this information as much as I have, it's been a great help

-SpeedyDog