Excel VBA 1

[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]

hi,

I did not notice that you started posting here.

Are you not calling your prpcedure from the Worksheet_Activate event?

Private Sub Worksheet_Activate()
getactivesheet
End Sub

That IS automatic!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[exRP12Nuke]

Right now when I open up my spreadsheet I have two tabs visible, the "Working" tab and "Aug-11" tab. The Worksheet_Activate must not run just when I open the spreadsheet up. Is there another handler that I could use that would run the Worksheet_xxx when I open the spreadsheet? Otherwise I have to go in to Module 6 (where the small bit of code is) and run it from there in order for it to work.

 

[SkipVought]

In the ThisWorkbook Object code window, use the Worksbook_Open event.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, welcome to Tek-Tips! T

his IS one of the best sites/forums for learning and developing your VBA skills along with other skills as well.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[exRP12Nuke]

Thanks Skip, it has been a steep learning curve. I started out in my company as a mechanic, then I decided to get into the business side of it. I enjoy learning Access and Excel with VBA, but it is a far cry from what I did in my younger years.

Placing that code into the "This Workbook" code window still provides the same results as before. I see where you were coming from with that and it makes sense, but for some reason it is still not running (or perhaps it is and I am missing something else).

 

[SkipVought]

Please post ALL you code and explain where each procedure resides.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Just a shot in the dark here, but is there any place in Excel where you can set: enable/disable macros on start?

Have fun.

---- Andy

 

[exRP12Nuke]

Thanks guys, I am just going to leave the sheet unprotected, it is easy enough to regenerate if any changes are made, I was just playing around with some new functions to expand my knowledge.

Thanks for the ideas!

-Brett

 

[SkipVought]

FYI, a different look at your code...

Function getactivesheet() As String
    With Activesheet
        .Protect Password:="tbcc", userinterfaceonly:=True
        .EnableSelection = xlNoRestrictions
        .EnableAutoFilter = True
    End With
End Function

But I would not use activesheet, as I rarely activate sheets or select cells in my code...

Function ProtectSheet(ws as worksheet) As String
    With ws
        .Protect Password:="tbcc", userinterfaceonly:=True
        .EnableSelection = xlNoRestrictions
        .EnableAutoFilter = True
    End With
End Function

Then call the function with the sheet argument...

ProtectSheet ActiveSheet

'or

ProtectSheet Sheets("SomeOtherSheet")

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nodrog77]

Andrzejek ,

in answer to your question "Just a shot in the dark here, but is there any place in Excel where you can set: enable/disable macros on start? "

The answer might be in setting your macro security to "medium" as follows: (correct me if it's changed slightly - I'm still on 2003 here)

Tools > Macro > Security > Medium

When the user opens the spreadsheet excel will then ask the user if he/she wants to run any macros.

In later versions of excel it might be more complex , see

http://office.microsoft.com/en-us/e...o-security-settings-in-excel-HP010096919.aspx

for 2007

Regards,

Leandra.