[RESOLVED] Limiting User's Interaction with Excel

[Eugbug]

I am writing a simple application which reads data from a GPS unit and stores the coordinates in a file. I am a novice when it comes to Excel automation and VB and am learning bit by bit how to do this! I have succeeded so far in writing a list of values into a column and after much "exception throwing" when an attempt is made to edit a cell, I set the interactive property of the Excel object to false to prevent any changes being made. This works fine but unfortunately this disables all interaction, so is it possible to prevent interaction with the cells of a worksheet but allow a user to scroll down through data (when this extends beyond the screen) ? I have tried to use the protect method and lock the cells in code, but this just causes Excel to complain about the sheet being protected and VB throws an exception.

[westconn1]

lock the cells and protect the sheet? unprotect to change data and reprotect

[Eugbug]

lock the cells and protect the sheet? unprotect to change data and reprotect

Yes that worked fine. Thanks! However Excel complains if a user tries to edit a cell on the sheet, and VB throws a "call was rejected by callee" exception. So this exception needs to be caught, or alternatively a displayalerts = false method can be used on the application object.

Here is the code snippet:


Dim oXL As Object
Dim oWB As Object
Dim oSheet As Object


' Opens an Excel file
Private Sub ExcelOpen()

'Start Excel and get Application object.
oXL = CreateObject("Excel.Application")

oXL.Visible = True

' Add a new workbook....
oWB = oXL.Workbooks.add
oSheet = oWB.ActiveSheet

oXL.UserControl = True

' Don't display warnings in Excel
oXL.DisplayAlerts = False

End Sub

' Write to excel file
Private Sub ExcelWrite(ByVal value_to_write As String, ByVal row As Integer, ByVal column As Integer)
Static previous_row As Integer = 1


Try
oSheet.unprotect()
oSheet.Cells(row, column).Value = value_to_write
oSheet.columns.autofit()
oSheet.protect()
Catch ex As Exception
MsgBox(ex.Message)
End Try


End Sub

[westconn1]

avoid the use of activesheet, as it is a new workbook, specify as

osheet = owb.sheets(1)