PDA

View Full Version : Sleeper: Show userform, save > ...Illegal operation ?



johnske
02-26-2005, 01:21 AM
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
02-26-2005, 01:37 AM
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
02-26-2005, 01:45 AM
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
02-26-2005, 02:48 AM
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
02-26-2005, 02:56 AM
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 :dunno :banghead: :banghead:

Jacob Hilderbrand
02-26-2005, 05:46 AM
Try to unistall Office then also delete the Office folder in Program Files. Run Defrag. Reboot. Re-install Office.

johnske
02-26-2005, 07:58 AM
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 :devil: ).

Regards,
John

Anne Troy
02-26-2005, 08:15 AM
John: When's the last time you cleaned out your Windows temp folder? In particular, the VBE folder that gets created...

johnske
02-26-2005, 08:21 AM
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... :devil: ...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
02-26-2005, 08:34 AM
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
02-26-2005, 08:46 AM
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
02-26-2005, 09:01 AM
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
02-26-2005, 09:36 PM
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. :dunno

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 :banghead: :banghead: :banghead: :banghead:


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
02-26-2005, 09:59 PM
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!! :whistle:

Ivan F Moala
02-27-2005, 05:59 AM
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
02-27-2005, 06:44 AM
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