asked on

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!

Rob Henson

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

Thanks
Rob H

Shanan212

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shanan212

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

Did you reset events first?

Shanan212

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

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

Shanan212

ASKER

This is the setting I am working

Shanan212

ASKER

woops wrong picture

Shanan212

ASKER

Aah triggering reset sub myself do works!

Rory Archibald

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.