Workbook sheet change event prompts save

**pprseller** · 10-28-2009, 04:39 PM

Hi, thanks to those that have helped so far! I have many worksheets in a workbook that need to be saved if a user changesanything on them.
These sheet names all end in "....SD" and the code needs to only run on those sheets. I have learned alot from the forum but not enough, just yet . . This is what I have so far:

HTML Code:

It doesn't like the 2 "Then's". (Don't laugh - I'm trying.)

Thank you!!

**shg** · 10-28-2009, 05:06 PM

Maybe just this?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If LCase(Right(Sh.Name, 2)) = "sd" Then Me.Save
End Sub

**pprseller** · 10-29-2009, 11:54 AM

That works but its prompting after every cell is typed in. Maybe I'm using the wrong worksheet/workbook event. Can you suggest what to use? The scenario is that I have worksheets that are used over and over again and not saved. The worksheets that end in "SD" must be saved after a change. So the workbook close event won't work either. Is there a way to prompt save when a sheet "loses focus"? Thanks . . . .

**shg** · 10-29-2009, 01:17 PM

The code I posted doesn't prompt at all, does it? It just saves worsheeets ending in "sd" after any manual change.

**pprseller** · 10-29-2009, 01:39 PM

I have code for "before save" prompting user if they are sure they want to save. So I took that out and your code works fine except on every cell change the workbook saves and it takes about 15 seconds to save each time (it's a large workbook!). How can I stop that? (This a worksheet where the user will be filling in 15 fields so that will be too long to wait it out each time a cell is changed.)