Strange inability to delete selection change event sub...

[LizF]

Hello folks,

We are having a weird problem here...

In one of the sheets of my workbook I ended up with a...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub
[\code]

...by mistake.  I don't need or want it so I tried to delete it.  Problem is, whenever I try to delete it it crashes excel, and sometimes the whole PC.

I got round it by doing
[code]
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Exit Sub
End Sub
[\code] but it's really slowing my code down as during one particular procedure a selection change happens 100s of times and it's going to this event each time

Does anyone know why I don't seem to be able to delete this, or even better how to fix it?

Thanks,
Liz
[ponder]

 

[RobBroekhuis]

Corrupted workbook, most likely. Try to copy the code and paste it into a safe place (text file or so), then try to delete it through the immediate window using
n=thisworkbook.VBProject.VBComponents("MySheet&quot.CodeModule.countoflines
thisworkbook.VBProject.VBComponents("MySheet&quot.CodeModule.deletelines 1,n

Then paste the code (less the offending lines) back in. If this still upsets Excel, then you'll need a different approach.
Rob

 

[SkipVought]

Hi,
The selection change event AND a bunch of other events are all part of Excel. These events are there regardless of whether you did something or not. Initially none of them contain code -- thats up to you. But they are there.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[LizF]

Hi guys,

Rob, I tried your nifty trick, but Excel still froze (Task Manager calls it "Not Responding"! (-

Any other tricks or tips? I guess at worst I could copy the entire workbook minus those few lines into a new one...

Skip, good point! This was definitely of my doing though - as in it was a blank VB Object (Sheet2) until I mistakenly wrote in a selection change sub. Didn't even realise I'd done it until my code kept dying and I found it had jumped from the module it was in to this selection change. Grr!

Cheers guys, any further advice gratefully received...
Liz
:->

 

[xlbo]

How are you trying to delete the sub - are you trying to delete the module or just selecting the text and pressing delete ?? Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[LizF]

Geoff,

I'd been selecting the text and trying to delete it...

It's not in a module, it's in the Sheet2 object so am I right in thinking I can't delete that?

Cheers,
Liz

 

[xlbo]

Yup - can't delete it
Some things to try

1: Comment out all the lines (by putting ' in front of them)
2: Add a new line above the current start of the sub - something like
Sub HopeThisWorks()
and then comment out the private sub line or try and delete the private sub line (if this works, the code'll still be there but it won't be the selection change event Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[LizF]

Hi Geoff,

Darn, I was so sure those ideas would work! But they didn't... :-(

Am I going to have to resort to copying worksheets and code manually?

There's a point...if I copy sheet2 into a new file will it take the troublesome code with it or will I get a nice blank sheet2 opject?

Thanks,
Liz

 

[bryanbayfield]

Can you not create a new workbook, copy over all the formats, values etc from your old workbook. Assuming all your code has been saved in a .txt, and any forms you might need have been exported, then delete the old workbook, save the new one using the name of the old.

Then import your forms and paste your code into the appropriate module/ sheet objects.

That's the way I normally get around Excel screwing up. Once I've indulged in some torture of my Bill Gates voodoo doll - careful placement of a clothes peg can do wonders.

 

[LizF]

Heh heh heh Good method - avoids violent punishment of the PC for Microsoft's sins...

Yeah, I'm getting around to thinking that creating a new workbook and copying it all over may be the only option - I had been hoping to avoid it though!

Ah well, thanks for the help guys
Liz