Problem updating cells after deleting

[dls725]

Hi,

I apologize if this post is a repost, but I tried searching.

I have created a spreadsheet that allow users to reserve courts for certain days and time. I wrote the code to allow users to delete an entire reservation by selecting a single cell within the reservation and hit delete entire booking. All the data that displays on the spreadsheet is stored in a access database.

My problem is that I don't know how to update the cells back to normal. I only know how to update the cell that was initially selected when you hit delete. Each reservation is associated with a single booking number. This is how I delete an entire reservation. Each reservation can only be for one court, so no cells in the same reservation would be side by side. The user is also able to delete any single cell of a reservation.

For example, all the cells labeled "DS" is from a single reservation. If I select any one cell labeled "DS" and hit delete entire booking, I would like all the cells labeled "DS" to turn back to default (no text or color fill).

Any help is greatly appreciated, and I apologize again if this post is a repeat. Thanks again.

[OBP]

Why use Excel to do an Access job?
If you are just trying to "Clear" the booking then that is what you need to use not "delete".
If the Data is "Linked" to Access does it delete the Access Record?

[dls725]

The data is linked, but the spreadsheet saves after every action. Once I hit the delete button, I want all the cells to that is associated with that reservation to clear on the spot, just in case I wanted to make another reservation. I can always open the spreadsheet seperately and manually delete everything so when I open the database again, everything will load on a blank spreadsheet, but that defeats the purpose.

I figure out how to do it for the single cell that the user select initially to delete the entire reservation.

Selection.Interior.ColorIndex = xlNone
Selection.Value = ""

I just need to know how I would do it for the entire booking. Somehow I excel need to know which cell is associated with which reservation and clear it.

[OBP]

Does that mean that for say BB that you want to clear all the Cells with BB in them?

[dls725]

Not necesarily, BB is just the initial of the person who made the reservation. A single person can have multiple reservations. Each reservation (can have mulitple cells and can be on different columns) has a specific booking number. Each reservation can only be for one court (either 1 or 2) so no two columns side by side will be the same reservation. But in this case, the "BB" in column F, H, and J are the same reservation, where as the "BB" in column G is not.

I want it so that when I select any of the "BB" cell in either columns F, H, and J and hit delete entire booking, that it will clear all the "BB" in columns F, H, and J (they are all the withing the same reservation). I apologize if this is confusing, but I appreciate all the help I can get.

[mdmackillop]

Hi DLS
Welcome to VBAX
You need a little adjustment to the FindNext example from the help files

Sub DelBooks()
If Selection = "" Then Exit Sub
With Cells
Set c = .Find(Selection, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.ClearContents
c.Interior.ColorIndex = xlNone
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
End Sub

[lucas]

Very nice Malcolm. I still have trouble with
If Not c Is Nothing Then
shouldn't that end it if you start with a blank cell selected....keeps running.

[mdmackillop]

Thanks Steve.
I never tested for error handling
Regards
MD

[dls725]

Hey mdmackillop,

I am not a VBA wizard, so I apologize if this sounds stupid. I think I kinda understand what you are doing, but my question is how would excel know which cell to clear? The cells are tied in the backend with a unique booking number which I used to delete the data in access, but I have no idea how to associate that with the cells. I thought about making a recordset which carrys date, time, and court from a unique booking number (the reservation you just deleted) and do an If loop to match the cells. I also thought about inserting date, time, court in an multi-dimensional array and do a if loop. Would any of those work? If so, how would I go about it? I apologize again if this in any way sound confusing, but thanks for the effort guys.

[OBP]

dls725, I have to ask again, why are you making it so hard for yourself by using Excel for this part, when you can do it in Access?

[dls725]

Hey OBP,

In Access, I deleted the data, but how would you update the cells on the spot without closing the app (in case someone wants to make another reservation)? How would you do it in Access?

[OBP]

You can have as many people in the database at the same time as you like, within reason, I think it may slow down a little when it gets to over 32.
They can be in the same table at the same time and Access will stop 2 people accessing the same record at the same time.
Is the database on just one computer or shared?

[mdmackillop]

The code reads the value of the selected cell and clears all cells with the same value, removing the colour at the same time. If you used conditional formatting, then colour would appear/disappear automatically. If you need to run this from access, then I'm sure the macro can be called from there and the value passed to the code for execution. Someone else will have to help you with that.

[dls725]

Hey OBP,

This application would only be used by one person, one computer. I still don't understand how you would update the cells without closing the application by using access.

Hey mdmackillop,

Besides the booking number (which is not associated directly with the cells) what conditions should I use? Date, Time, and court number can be used as conditions, but how would I go about doing it? Is it possible to make a multidimensional array that contain the date, time, and court for each cell?

[OBP]

dls725, you do not update the cells in the worksheet, you have an Access form that looks like and does the same job as your Excel worksheet.
As the Access form is in direct contact with the table and therefore the kind of operation that you are trying to do is much easier to program.

[dls725]

Hey OBP,

So Access can also display a spreadsheet? Can you please explain how you would go about doing so? I apologize, this is my first time using VB, Excel, and Access this much, I am totally lost.