HELP! - Hiding colums Macro

[mobilesuit818]

Hello,
I need to hide several columns (those columns are F, Q, R S, T, U, W, X, and Y) when im finished working a worksheet. There are several worksheets in my workbook.

Can someone help with a macro for this, please?

 

[mobilesuit818]

Based on this formula,
Sub foo()
ActiveSheet.Range("F:F, Q:Y").EntireColumn.Hidden = True
End Sub

I would like to see how I can revise this macro to add password protect. The end result is that in order to run this macro, I would like to password protect it, basically sending a spreadsheet out to my company, but need to protect a couple of columns, so no one can see it, at the same time, I need to be the only one who can hide/unhide those columns once i enter the correct password in.

Can anyone help me with this one?

 

[Smitty]

Just Unprotect/Reprotect it:

Sub foo() 
   With ActiveSheet
      .Unprotect "PasswordHere"
        .Range("F:F, Q:Y").EntireColumn.Hidden = True 
      .Protect "PasswordHere"
   End With
End Sub

Smitty

 

[mobilesuit818]

Smiity,
Thank.

Also need to see if you can change the macro, to run right after the user saves as. Is this possible.?

Also, I have the first macro that you gave me entered with this one, and it wont allow me to run both of them,

Can you please advise.?

 

[Smitty]

Also need to see if you can change the macro, to run right after the user saves as. Is this possible.?

Check out the BeforeClose/Save events in the VB helpfile. You can test for the old WB name in your code for validation.

Also, I have the first macro that you gave me entered with this one, and it wont allow me to run both of them

If you got an "Ambiguous name..." error then that's because both subs are named the same & VBA doesn't like that (ambiguity), so you've got you change one or the other.

Smitty

 

[mobilesuit818]

Check out the BeforeClose/Save events in the VB helpfile. You can test for the old WB name in your code for validation.

I did a search on this, and found out this code, but I did know where to enter the formula in.

The formula that I saw was:
Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)

Can you post a structure format for future reference and how where do I enter the formula in? Do I put in under the SUB?

If you got an "Ambiguous name..." error then that's because both subs are named the same & VBA doesn't like that (ambiguity), so you've got you change one or the other.

I actually a message that read "400", and I am assuming it is the same thing that you mention. Im going to go ahead and just use a set not both macros.

 

[mobilesuit818]

Can anyone help me?

 

[mobilesuit818]

If someone can tell me where to input this code?

 

[Smitty]

Do you want to force the user to save or let them choose when?

It might be a better idea to force the save and incorporate your code there.

Smitty

 

[mobilesuit818]

Smitty,
I dont know if it will be difficult, but for my type of application, the user will need to chose when they need to save, and right when they do that, it would hide the columns as defined, and password protect that so no one else can see that, except if you had a password.

Thanks again.

 

[Smitty]

OK,

You can try something like this:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeSave(<SPAN style="color:#00007F">ByVal</SPAN> SaveAsUI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, Cancel As <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")
      .Unprotect "PasswordHere"
        .Range("F:F, Q:Y").EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN>
      .Protect "PasswordHere"
   <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note I used a specific sheet name reference instead of ActiveSheet, so you'll need to adjust that to the actual sheet name that you're trying to use this on.

The code goes in the ThisWorkbook module.

Smitty