Sign in to follow this  
Followers 0
someone

Excel ActiveCell.Address

10 posts in this topic

Hey everyone,

I seem to asking a lot of questions lately but here it goes. I'm trying to continuously monitor the active cell in Excel to see when the user has clicked on a different cell.

ANYWAY, the problem is ActiveCell.Address will fail if you are editing the cell. I searched around and a solution using ControlGetFocus. See code below

Global $actual2
$oExcel = ObjGet("","Excel.Application")    
While 1
$focus = ControlGetFocus("Microsoft Excel - test")
If $focus = "EXCEL71" Then
$actual=$oExcel.ActiveCell.Address
if $actual2<>$actual Then
$actual2=$actual
;MsgBox(0, "", $actual)
EndIf
EndIf
WEnd

However if you run this code you can't double-click to modify a cell. I've experimented some using sleeps to slow down how often ControlGetFocus runs, however the sleep has to be larger then it takes you to double click (which is say 500 ms), and then it slows down the rest of my script and seems generally sloppy.

Any suggestions?


While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites



Are you monitoring changes to all cells in general, or just one cell in particular?

Share this post


Link to post
Share on other sites

I'm trying to monitor whenever the active cell has changed... basically I made a GUI that will read what cell is active and display the contents of that row, as well as some comments the user can add.. if that makes sense.


While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

So what happens if someone selects another cell above the current row? Do you want to know if any cells in the previous row changed? I'm still trying to understand the purpose of the GUI.

Share this post


Link to post
Share on other sites

Sorry I'm not making sense here... heres a better overview.

In each row of the excel sheet there is a ticket number on colum A. When a user clicks on any cell(not just a cell with the ticket #), I want my GUI to read the ticket number of the active row, and display it in the GUI (it does a little more but thats all it does with excel).

So I need to constantly monitor the active cell address and match it up against what the address was in order to determine if the address changed. If the address did change, the GUI will update. The problem with that is if a user double clicks on a cell to edit it, or goes into the forumal bar, ActiveCell.Address fails, making my script crash hard.

Thats why I was trying to use ControlGetFocus to only see the active cell address when the user wasn't editing the cell or doing something else that would cause ActiveCell.Address to crash. However when looping ControlGetFocus in my code above you CAN'T double click to edit a cell. Try it out its a little hard to explain.

If this doesn't make what I'm trying to do clear I'll post a full reproducer with GUI.

Thanks


While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

OK, I think I get it now. The GUI will always display the ticket number from the row wherever the cell is selected, but if someone is editing a cell, the script crashes. Sorry...it's Monday. :whistle:

Share this post


Link to post
Share on other sites

Ha don't worry about it its Monday for me too. If anyone has any brainstorms I'd be happy to hear them.

Thanks,

Andrew


While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

Bad morning bump...


While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

I've played with it a little more, and I came up with this, which may or may not work in your case:

Opt("RunErrorsFatal", 0) ;Turn off fatal errors
Global $actual2
$oExcel = ObjGet("","Excel.Application")
While 1
   $wintitle = $oExcel.Name & " - " & $oExcel.ActiveWorkbook.Name
   $focus = ControlGetFocus($wintitle)
   If $focus = "EXCEL71" Then
      $actual = $oExcel.ActiveCell.Address
      If $actual2 <> $actual Then
         $actual2 = $actual
         ;MsgBox(0, "", $actual)
      EndIf
   EndIf
WEnd

Share this post


Link to post
Share on other sites

For better or worse I found the answer in the helpfile :whistle: Objects are still over my head but mabe this means I'm getting better... you can use ObjEvent("AutoIt.Error","MyErrFunc") to trap any object errors allowing me to use my original code without it crashing.

Global $Address

$oExcel = ObjGet("","Excel.Application")
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")    ; Initialize a COM error handler

$wintitle = WinGetTitle("Microsoft Excel - test")
While WinExists($wintitle)
    Sleep(50)
    $actual = $oExcel.ActiveCell.Address
    If Not @error then
        If $Address <> $actual Then
         $Address = $actual
         ToolTip($Address)
        EndIf
    EndIf
WEnd

Func MyErrFunc()
    Local $err = $oMyError.number
    If $err = 0 Then $err = -1
    SetError($err)  ; to check for after this function returns
Endfunc

Thanks for sticking with me GMK


While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0