[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • 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 HensonIT & Database AssistantCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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