• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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!
0
Shanan212
Asked:
Shanan212
  • 6
  • 4
1 Solution
 
Rob HensonFinance AnalystCommented:
You are expecting it to react to an Event when you have switched off Events in line 3.

Thanks
Rob H
0
 
Shanan212Author Commented:
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'
0
 
Rory ArchibaldCommented:
How are you changing the sheet contents? Manually, or via formulas? (the latter won't work). Also, have you had any errors while testing? If so, you may still have events disabled, so run this:
Sub ResetEvents()
Application.Enableevents = true
End Sub

Open in new window


then change your code to:
Private Sub Worksheet_Change(ByVal Target As Range)
   On Error Resume Next
        Application.EnableEvents = False
        Me.Unprotect
        
        Me.Columns.AutoFit
        me.EnableAutoFilter = True
        
        Me.Protect DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
        Application.EnableEvents = True
End Sub

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Shanan212Author Commented:
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!
0
 
Rory ArchibaldCommented:
Did you reset events first?
0
 
Shanan212Author Commented:
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 :/
0
 
Rory ArchibaldCommented:
No - you need to actually run the  reset sub yourself, otherwise the events can never trigger it.
0
 
Shanan212Author Commented:
cap
This is the setting I am working
0
 
Shanan212Author Commented:
woops wrong picture

 cap
0
 
Shanan212Author Commented:
Aah triggering reset sub myself do works!
0
 
Rory ArchibaldCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now