Formula

[Colin Haig]

To All

I ask this question a couple of weeks ago - the answer I received
was to use the ctrl - semi colon key combination but these keys
are not available on a palm

I am using Excel and OLE to generate a maintenance checklist
spreadsheet from a database, the spreadsheet is then downloaded to a palm
device for field technicians to complete - to make it easier for the
technician
I would like the following

A D
G H
7 Equipment Maintenance Task
Checked Complete
8 Chilled Water System
9 Chiller 1 Check Electrical
Connection 1 30/11/2003
10 Chiller 1 Check Unit Vibration


If possible I would like the current date to be entered cell (10,H) when the
technician enters number 1
into cell (10,G) .

This is how I would have to call the formula

oSheet:Cells(nRow,"I"):Formula := "function_name(parameters)"

Can someone assist me with an Excel formula to do this and how
I associate it with a template sheet.

Thanks for any assistance

Cheers

Colin Haig

 

[Arvi Laanemets]

Hi

You can't do anything like that with function, and consequently - with
formulas.
In Excel, functions/formulas can't change anything on sheet, i.e. change
cell value or move cursor, etc. When you enter some formula into cell, what
there is is a formula, not the value it is returning.

To change the value in cell otherwise as manually, you have to invoke a
procedure, or have an event written.

You surely find some thread in some of Ecxcel NG's about worksheet's Change
event, when searching - I remember at least one from past week. But there
always exists danger that your data get messed up somehow. And with tables
big enough, your workbook can be getting too slow.

I'm never worked with Palm, let alone Palm Excel, but surely you can use
macros/procedures there. So you can write a macro, and set some hot-key for
it, to emulate the behaviour of Ctrl+;
An example:

Sub InsertToday()
ActiveCell.Value = Date
End Sub

 

[Colin Haig]

Arvi

I generate the spreasheet from a PC using OLE to send data to the
sheet and then download the sheet to the Palm - so I need to embed the
function/procedure into a Template sheet.

oSheet:Cells(nRow,"H"):Formula := "some_function"

I have tried searching the internet looking for examples , can
you point me to any good sites

Cheers

Colin Haig

 

[Arvi Laanemets]

Hi

I have to reiterate - no way to do this through functions. No built-in
formula or UDF can do it - the only way is implement a code (a procedure or
event) into excel document (workbook). With procedure, user has to invoke it
manually, event occurs automatically.

 

[Phillips]

This is what I got to work. I am NOT SURE about in a "template" but...
Put this as a change event on your worksheetcode


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If ActiveCell.Column <> 1 Then Exit Sub
Call setDate
End Sub

Sub setDate()
Dim mDate As Date
Dim mRow As Integer
mRow = ActiveCell.Row
Application.EnableEvents = False
ActiveCell.Offset(0, 7).Value = Date
Application.EnableEvents = True
End Sub

HTH
Phil