CODEWITHSUNDEEP
c#excelvstoadd-invba
Emanuele Greco
Emanuele Greco

Reputation: 12731

Get in Excel current value in focused cell

I'm developing an Excel add-in. I need to get the value from A1 cell, so I use this code

value = workbook.Worksheets[1].Range["A1:A1"].Value2;

This works great but if :
- Previous Cell value is "1"
- User types "2" but does not press Enter or Tab
- User activates ribbon pressing it's button

Value2 got from this code sample is "1" (previous value) not "2" (current value shown in cell but not validated). This is wrong in my situation, I need "2".
How can I fix this?

I tried forcing selection of another cell 

 Application.ActiveCell.Offset[1, 0].Select();

or

 Application.ActiveSheet.Range["A2"].Select();

but no way.. "2" value is lost.

I also tried getting value from text area in toolbar ("2" is shown there) but don't know hoe to do!

Upvotes: 1

Views: 1085

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

When Excel in in Edit Mode (Similar to pressing F2 in an Excel's cell), most of the buttons on the ribbon and the VBA Editor become inaccessible.

Having said that, Can VBA code run in Edit mode? Yes, it can as shown in THIS link

To answer your question, You will have to use API's to trap the key and then find out what the current value of the cell is. Trust me, it is not worth the pain.

Simple Alternative:

Use ExcelApp.Interactive to check for Excel's Edit mode. And if the Excel is in Edit mode then inform user to exit the cell and try again. Did a quick check on the web and found a similar example HERE See the section The correct solution for all Excel versions, 2000 – 2010

This code is in vb.net but can be easily converted to C#

Function IsEditing() As Boolean
    If ExcelApp.Interactive = False Then Return False
    Try
        ExcelApp.Interactive = False
        ExcelApp.Interactive = True
    Catch
        Return True
    End Try
    Return False
End Function

C# Code from Chat

bool IsEditing()
{
    if (Globals.ThisAddIn.Application.Interactive == false)
        return false;
        try
        {
            Globals.ThisAddIn.Application.Interactive = false;
            Globals.ThisAddIn.Application.Interactive = true;
            }
                catch (Exception e)
            {
            return true;
        }
    return false;
}

Upvotes: 1

Related Questions