asked on 10/4/2011

Autofit using excel events

Private Sub Worksheet_Change(ByVal Target As Range)

        Application.EnableEvents = False
        ActiveSheet.Unprotect
        
        ActiveSheet.Columns.AutoFit
        ActiveSheet.EnableAutoFilter = True
        
        ActiveSheet.Protect , DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
        Application.EnableEvents = True
End Sub

Open in new window

Hi,

I have the above function, however, it just not working :/

There is no errors but the function should autofit the columns but its not doing it (I even tried outputting a message and its not popping either)

The full function is to include 'autofitting' certain columns only and I have idea like this

activesheet.range("A3","H3").select
selection.columns.autofit

^ Would the above work as well? (if the function starts working?

Thanks!

Microsoft Excel

Last Comment

Rory Archibald

10/4/2011

Rob Henson

10/4/2011

You are expecting it to react to an Event when you have switched off Events in line 3.

Thanks
Rob H

Shanan212

10/4/2011

ASKER

Ok, I removed it (both lines) and still not working. The Sub is in a sheet which is accessed by right clicking on the worksheet name and then clicking on 'view code'

ASKER CERTIFIED SOLUTION

Rory Archibald

10/4/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Shanan212

10/4/2011

ASKER

Doing it manually.

Above is also not working.

So I created a new worksheet.

Put in some columns, and tried the above (with the protection stuff disabled) - still no use
Tried with enableEvents stuff removed - no use :/

Thanks for the continues help!

Rory Archibald

10/4/2011

Did you reset events first?

Shanan212

10/4/2011

ASKER

I put the reset function above the bigger Sub! Also put it in a module and called it from this function - no use!

Funny thing is, when I was developing the function at the beginning with just 'autofit' line, it worked :/

Rory Archibald

10/4/2011

No - you need to actually run the reset sub yourself, otherwise the events can never trigger it.

Shanan212

10/4/2011

ASKER

This is the setting I am working

Shanan212

10/4/2011

ASKER

woops wrong picture

Shanan212

10/4/2011

ASKER

Aah triggering reset sub myself do works!

Rory Archibald

10/4/2011

No - you don't need to call ResetEvents from the event (that would serve no purpose). You need to actually run ResetEvents yourself once in order to turn events back on.