Sleeper: Show userform, save > ...Illegal operation ?

[johnske]

Can anyone

HELP

please?

This does everything it should - except - if you click 'Save' after showing the userform it gives the dreaded "This Program Has Performed an Illegal...." and is closed down

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyFilePath$
MyFilePath = "C:/My Documents/Backups for " & ActiveWorkbook.Name
On Error Resume Next '< already got a folder
MkDir MyFilePath
'save backup copy
ActiveWorkbook.SaveCopyAs Filename:=MyFilePath & "/" & _
(Format(Now(), "dd mmm yy h mm ss") & _
" " & ActiveWorkbook.Name)
End Sub[/vba]
[vba]Option Explicit
'<< CODE FOR USERFORM >>
Private Sub UserForm_Activate()
Dim N%, MyList(20, 0)
With Application.FileSearch
.LookIn = "C:/My Documents/Backups for " & ActiveWorkbook.Name
.Filename = "*.*"
If .Execute > 0 Then
For N = 1 To .FoundFiles.Count
MyList(N - 1, 0) = .FoundFiles(N)
Next N
End If
End With
ListBox1.List = MyList
End Sub

'delete all the backups and the folder

Private Sub CommandButton1_Click()
Dim N%
With Application.FileSearch
.LookIn = "C:/My Documents/Backups for " & ActiveWorkbook.Name
.Filename = "*.*"
If .Execute > 0 Then
For N = 1 To .FoundFiles.Count
Kill .FoundFiles(N)
Next N
End If
End With
'now delete the folder (folder must be empty)
RmDir "C:/My Documents/Backups for " & ActiveWorkbook.Name
're-set the list & view it
UserForm_Activate
End Sub

'purge all the backups except the last one

Private Sub CommandButton3_Click()
Dim N%
With Application.FileSearch
.LookIn = "C:/My Documents/Backups for " & ActiveWorkbook.Name
.Filename = "*.*"
If .Execute > 0 Then
For N = 1 To .FoundFiles.Count - 1
Kill .FoundFiles(N)
Next N
End If
End With
're-set the list & view it
UserForm_Activate
End Sub
 
Private Sub CommandButton2_Click()
Unload Me
End Sub

[Anne Troy]

You have the userform as ShowModal = True.
So, if that's the case, how is it that you're hitting the SAVE button?
Using 2003, if TRUE, I have to close the userform to hit save, and I don't get an error.
If FALSE, and I CAN hit SAVE, I still get no error.

What version, John?

[johnske]

You have the userform as ShowModal = True.
So, if that's the case, how is it that you're hitting the SAVE button?
Using 2003, if TRUE, I have to close the userform to hit save, and I don't get an error.
If FALSE, and I CAN hit SAVE, I still get no error.

What version, John?

Hi Anne,

2000, but I was hoping to leave this so it could also be used in '97 (as an add-in).

I only get it after I've shown the userform and unloaded it (I should've been clearer there). Everything should be back to normal then, but if I then click save I get the message. If I don't show the userform and save & save everythin'g fine ???

John

[Jacob Hilderbrand]

Works fine for me with no errors as well. You could always try something to take Excel's "mind" off of whatever it is that is causing the error.

Option Explicit
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws         As Worksheet
Dim i          As Long
Application.ScreenUpdating = False
    Application.EnableEvents = False
Set ws = ActiveSheet
    For i = 1 To Sheets.Count
        Sheets(i).Activate
        DoEvents
    Next i
    ws.Activate
Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

[johnske]

Thanx Jake,

It's worth a try, but that's two now that've had no problems with it. I'm starting to think that I may need to re-install Office (I got rid of a trojan and half a dozen diallers yesterday, they may've done something to corrupt it - the 1st clue I got was OE had been re-set to "disconnect after sending and receiving") then I got another VBA problem that eventually resolved itself...

John

Well, I just did a 'detect & repair', system file check, then a complete uninstall & reinstall - it's no better. Tried a 'distraction' as Jake suggested, still the same problem

[Jacob Hilderbrand]

Try to unistall Office then also delete the Office folder in Program Files. Run Defrag. Reboot. Re-install Office.

[johnske]

Try to unistall Office then also delete the Office folder in Program Files. Run Defrag. Reboot. Re-install Office.

Thanx Jake,

Tried that, but no better.

Played around with it some more and it only does it with the Before_Save event on my machine, seems to be some sort of conflict there. If I make it either a Workbook_Open or Close event there's no problem...that may be the way to go (be a lot less backups then ).

Regards,
John

[Anne Troy]

John: When's the last time you cleaned out your Windows temp folder? In particular, the VBE folder that gets created...

[johnske]

John: When's the last time you cleaned out your Windows temp folder? In particular, the VBE folder that gets created...

Hi Anne,

About three times in the last few hours... ...so don't think it's that. Also done: Ad-aware, Spybot, Bazooka, registry...re-installed almost everything except windows itself...

John
EDIT: It's actually pretty strange - even tho the book you're working from give an "illegal operation" message, the backup copy is still made, saved, and works OK.

I'm starting to think that to avoid this I may have to create a custom "Save" tool-bar button that saves and makes a backup to replace the standard 'Save' button and also make it a "Close" event in case the custom button is forgotten about...

[Anne Troy]

Is it your toolbar file, maybe?
Or maybe you need to reset Excel?
I would try wiping the xlb and dumping Excel's registry key.

[johnske]

Is it your toolbar file, maybe?
Or maybe you need to reset Excel?
I would try wiping the xlb and dumping Excel's registry key.

If you mean it's the toolbar 'save' button - had a problem once B4 with a corrupted button, so I tried replacing it. Not sure what you mean about re-setting XL & the 3rd bit....I've already uninstalled and deleted the 'office' folder B4 re-installing - wouldn't all that be done?

[Anne Troy]

No.

1. Find *.xlb files and rename (if your toolbars are customized and you can't bear to lose them) or delete

2. Close all proggies. Start-Run and type regedit. Browse to hkey_current_user/software/microsoft/office/9.0/excel folder; right-click it and rename it to OldExcel. Close and relaunch Excel. It's like a "new" installation. Uninstall and reinstall does NOT do this.

[johnske]

Well, thanx very much for the help from everyone, but... ... nothing works.

I think I'm going to just have to give up on the "BeforeSave" and go for a "BeforeClose" event instead.

I thought it mighta had something to do with the "On Error" approach (trying to save with an unresolved error) and used the modified code below, but...no better

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyFilePath$, FileSysObj
MyFilePath = "C:/My Documents/Backups for " & ActiveWorkbook.Name
Set FileSysObj = CreateObject("Scripting.FileSystemObject")
If Not FileSysObj.FolderExists(MyFilePath) Then
FileSysObj.CreateFolder (MyFilePath)
ActiveWorkbook.SaveCopyAs Filename:=MyFilePath & "/" & _
(Format(Now(), "dd mmm yy h mm ss") & _
" " & ActiveWorkbook.Name)
Else
ActiveWorkbook.SaveCopyAs Filename:=MyFilePath & "/" & _
(Format(Now(), "dd mmm yy h mm ss") & _
" " & ActiveWorkbook.Name)
End If
End Sub

Regards,
John

[Scottie P]

I tried this out and ended up with two errors, but no illegal ops.

Obviously, I did something dumb the first time but what I found was that I was getting two "Backup For..." directories and a debug on the Kill statement.

Still fiddling around here; will report back later - there's food to be eaten!!

[Ivan F Moala]

John did you try Dreamboats sugestion Re; xlb ??

If so and still no go ... then do you have multiple versions of excel on your system ??

[johnske]

John did you try Dreamboats sugestion Re; xlb ??

If so and still no go ... then do you have multiple versions of excel on your system ??

Hi Ivan,

Yes, I most certainly tried Dreamboats (and every suggestion everyone else made). And no, I only have 2000.

It's got me beat, doesn't look like I'll take this idea any further as it depended on an auto-backup on 'Save' - It may simply be that my Office installation disc is corrupted (?a surface scratch or something?).

It may work fine on other machines, and I could still carry on and complete it, but if I cant get it to work properly on my own machine I really can't feel at all confident about it.

regards,
John