Regaining control of Excel spreadsheet from VB.net

I am creating an Excel spreadsheet from VB.net. The program populates some cells of the spreadsheet. The user can also enter data into cells. These two activities can happen consecutively (not concurrently).

If the user does not hit ENTER after entering their data, VB.net cannot regain control of Excel. Is there a way around this

Below is a short test stub that demonstrates this. The user enters a number into cell A1. After he does this, the user hits ok to the msgbox. The square of the number goes into A2. If the user does not hit enter after typing in their number, a HRESULT: 0x800A03EC error occurs.

Any help appreciated,

Fred

Sub Main()

Dim excel As Excel.Application

Dim wb As Excel.Workbook

Dim sheet As Excel.Worksheet

Dim data3 As Integer

Dim rng As Excel.Range

excel = New Excel.Application

sheet = CType(wb.Worksheets.Add, Microsoft.Office.Interop.Excel.Worksheet)

excel.Visible = True

rng = sheet.Range("A1")

MsgBox("enter number in cell A1")

data3 = CInt(rng.Value)

Do While data3 <> 100

rng = sheet.Range("A2")

rng.Value = data3 * data3

rng = sheet.Range("A1")

MsgBox("enter number in cell A1")

data3 = CInt(rng.Value)

Loop

End Sub

I'm gettig the same error as you and I'm pretty good at excel automation. The problemis that the cell A1 doesn't have a value until you hit enter.

Will something like this work for you:

Dim excel As Excel.Application

Dim wb As Excel.Workbook

Dim sheet As Excel.Worksheet

Dim data3 As Integer

Dim rng As Excel.Range

excel = New Excel.Application

sheet = CType(wb.Worksheets.Add, Excel.Worksheet)

excel.Visible = True

sheet.Range("A1").Value = InputBox("Enter your value")

data3 = CInt(sheet.Range("A1").Value)

Do While data3 <> 100

sheet.Range("A2").Value = data3 * data3

sheet.Range("A1").Select()

sheet.Range("A1").Value = InputBox("Enter your value")

data3 = CInt(sheet.Range("A1").Value)

Loop

Thanks. It seems that there is not a way to regain control of Excel when it is edit mode. I am going to restructure my application so that all user input to the Excel spreadsheet is controled through the vb.net application.

Thanks for your help,

Fred