Excel VBA

[exRP12Nuke]

I have been placed in a job that requires a strong working knowlege of VBA, mainly for Access, but also at times for Excel...the only problem is that my working knowledge is not as strong as I need it to be at times.

I am working on putting together an Excel spreadsheet that automatically formats a data table that I place into Excel. I have all of the code correct for the formatting of the spreasheet, but I am getting hung up on some of the clean up code that I am trying to put into my spreasheet.

As my spreadsheet completes the formatting process, I have the code set to protect the sheet that was just populated:

Sheets(TabDate).Select
Activesheet.Protect DrawingObjects:=True, Contents:=True,_ Scenarios:=True, AllowFiltering:=True
With Worksheets(TabDate)
.Protect Password:="tbcc", userinterfaceonly:=True
.EnableSelection = xlNoRestrictions
.EnableAutoFilter = True
End With
ActiveWorkbook.Save

This code sets the sheet protection for my most recent sheet (TabDate in this portion of the code). Once my code is finished running all of my cells are locked, but I am still able to use the auto filter function that I have put into the spreadsheet. This works great until I close out of the spreadsheet and reopen it, at that point everything is locked out and I cannot use anything without unprotecting the sheet.

I have written another module:

Private Sub Worksheet_Activate()
getactivesheet
End Sub
-----------------------------------------------------
Function getactivesheet() As String
getactivesheet = Activesheet.Name
x = getactivesheet
With Worksheets(x)
.Protect Password:="tbcc", userinterfaceonly:=True
.EnableSelection = xlNoRestrictions
.EnableAutoFilter = True
End With
End Function

THis module does what I need it to do, but I have to go in and manually run this code inorder for it to work properly. Is there any way to make this code (Module 6) run automatically when I select a particular tab, or even when I open up the spreasheet?

Thanks!

-Brett

 

[SkipVought]

Please post Excel VBA questions in forum707 for better results.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[exRP12Nuke]

Thanks Skip, I will do that now!

 

[SkipVought]

As a general comment, if you unprotect sheets automatically, then why protect at all, unless you intend that only you can do that automatically, and not anyone else.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[exRP12Nuke]

They do not unprotect automatically, when I close and reopen the spreadsheet it removes the autofilter permission that I want to have available to use while the sheet is protected. Once the sheet is closed and reopened all premissions are removed until I run the second portion of code.

 

[SkipVought]

are you not calling the code here, in the Worksheet_Activate event? That IS automatic!

Private Sub Worksheet_Activate()
getactivesheet
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

okay, let's keep it in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}