A
Angus Comber
Hello
I have written a database program which works fine. Users wanted the
ability to be able to view the whole database in a grid and edit as if in
Excel. So I thought as if in Excel sounds good so using VBA made Excel a
front end to the data.
Now when my spreadsheet is launched a CommandBar appears allowing the user
to get the data. If the user clicks on the button then a SELECT * FROM xyz
DAO recordset is retrieved. The data is in two tables so there is a join.
The database is Microsoft Access. (in Access 97 format). I am testing on
Excel 97 (as need to be compatible with as many versions of excel as
possible).
The data populates into Excel in about 10 - 20 seconds if say there are 1500
rows. But that's OK.
Then the users edit the database simply by selecting a cell and editing the
data. I use:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
to capture when a user has changed a selection and then edit the database
using .Edit and .Update the recordset. That's fine so far.
But if for example the user selects the entire spreadsheet (eg by clicking
on the top left of the spreadsheet) then clicks the delete key to delete all
contents then I don't actually delete the entire database because that would
be dangerous. But I found that after selecting the entire spreadsheet if
you move the mouse to another cell for example the CPU utilisation goes up
to 100% and the whole thing stays there until I End Task Excel.
I notice that if I do an: Application.EnableEvents = False then the problem
goes away. However, I do need the Worksheet_SelectionChange functionality.
It seems that enabling the event handling AND selecting a large range of
cells brings Excel to its knees.
Can anyone suggest a way of getting round this problem. It is obviously not
very good if it happens to my users.
Could I perhaps disable users selecting multiple cells? (This functionality
is not required for my application).
Is there some other way of preventing Excel crashing/going barmy like this?
Angus Comber
(e-mail address removed)
I have written a database program which works fine. Users wanted the
ability to be able to view the whole database in a grid and edit as if in
Excel. So I thought as if in Excel sounds good so using VBA made Excel a
front end to the data.
Now when my spreadsheet is launched a CommandBar appears allowing the user
to get the data. If the user clicks on the button then a SELECT * FROM xyz
DAO recordset is retrieved. The data is in two tables so there is a join.
The database is Microsoft Access. (in Access 97 format). I am testing on
Excel 97 (as need to be compatible with as many versions of excel as
possible).
The data populates into Excel in about 10 - 20 seconds if say there are 1500
rows. But that's OK.
Then the users edit the database simply by selecting a cell and editing the
data. I use:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
to capture when a user has changed a selection and then edit the database
using .Edit and .Update the recordset. That's fine so far.
But if for example the user selects the entire spreadsheet (eg by clicking
on the top left of the spreadsheet) then clicks the delete key to delete all
contents then I don't actually delete the entire database because that would
be dangerous. But I found that after selecting the entire spreadsheet if
you move the mouse to another cell for example the CPU utilisation goes up
to 100% and the whole thing stays there until I End Task Excel.
I notice that if I do an: Application.EnableEvents = False then the problem
goes away. However, I do need the Worksheet_SelectionChange functionality.
It seems that enabling the event handling AND selecting a large range of
cells brings Excel to its knees.
Can anyone suggest a way of getting round this problem. It is obviously not
very good if it happens to my users.
Could I perhaps disable users selecting multiple cells? (This functionality
is not required for my application).
Is there some other way of preventing Excel crashing/going barmy like this?
Angus Comber
(e-mail address removed)