Selecting a record on an access form by clicking on an excel cell containing the search value.

[AndyKeen]

I have a small Access (Office 2007) application which uses Excel to display a "room chart" from data held in the Access tables. The Excel workbook and sheet are automatically opened and populated/updated as changes are made within the Access forms. When the Access form is closed the Excel sheet is automatically saved and closed by the Access module. This all seems to be working perfectly.

What I would now like to be able to do is to click on a cell in the already open Excel chart and automatically position the user on the equivalent record on the Access form (preferably by using the cell contents to "find" the correct record rather than changing the existing filter on the form?)

My main problem is that I cannot get my head around how I trap the click in the worksheet, pick up the current value of the clicked cell and bring it back into Access to use for the search. Can I trap a click in an open Excel sheet(and therefore determine the current cell value) from within the existing Access module that opens and updates the sheet? Or do I have to trap the click in Excel and somehow drive the form or pass the value from there?

I have tried loads of reference resources and help sites but just can't seem to find the info I need in a form that I can understand! Any pointers in the right direction would be much appreciated. Apologies if I am just being thick!

Many thanks
Andy

 

[SkipVought]

hi,

how I trap the click in the worksheet

There is no CLICK event on Excel Worksheets.

There is, however, a Workbook_SheetSelection Changeevent in the ThisWorkbook object. There is also a WorkSheet_SelectionChange event for the specific worksheet in question.

The Target object identifies the cell(s) selected.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AndyKeen]

Thanks again Skip. Sounds just the thing. Will see how I get on tomorrow. Hopefully I won't have to bother you again.

 

[TheAceMan1]

How are ya AndyKeen . . .

Just a hint ... I sure hope the [blue]primary key[/blue] of the record is embedded in the chart when the chart is updated!

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

click on a cell in the already open Excel chart

I am puzzled: There are no CELLS in a CHART. Do you mean, click on a cell in the already open Excel SHEET. Conversely, there are events associated with Excel charts for the chart area, plot area, axes, series/points. But depending on the chart being an object on a worksheet or a chart sheet, the method to expose these events is different.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AndyKeen]

Sorry Skip. My use of language is at fault. The "chart" is a user term for the entity which in Excel terms is simply a worksheet on which the data is laid out in a form which the user requires. All I need to do is trap the cell being selected by the user, pick up the value of the record key from the comment and move the Access form to show the user the detail they need for the corresponding record. Simple really. Just my limitations in not knowing which events and functions to use - and I suspect you gave me the answer to that in your first reply!

 

[TheAceMan1]

SkipVought . . .

Meant to say [blue]embedded in the spreadsheet[/blue] ... Just relating to carrying over the pk for a direct find.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]