clear cells on open

[mike]

Super easy question..I want cell B1 cleared on workbook open.

What I am trying to do: Macros need to be enabled for my sheet to proper,
our business excel macros are off with notification and we want to keep it
that way for most users. Most people here are excel illiterate and don't even
know what a macro is, so in cell B1 I have in bold red instructions for
enabling macros.

If the macro is enabled, I want those contents cleared, or if macros are
already enabled, for those contents to clear on open.

If what I'm asking won't achieve my desired results, please enlighten me.

Thanks,

Mike

 

[Mike H]

Hi,

Private Sub Workbook_Open()
With Sheets("Sheet1").Range("B1")
.Font.ColorIndex = 0
.ClearContents
.Font.Bold = False
End With
End Sub

You will of course have to put them back using the before_Close event

Mike

 

[Lars-Åke Aspelin]

Super easy question..I want cell B1 cleared on workbook open.

What I am trying to do: Macros need to be enabled for my sheet to proper,
our business excel macros are off with notification and we want to keep it
that way for most users. Most people here are excel illiterate and don't even
know what a macro is, so in cell B1 I have in bold red instructions for
enabling macros.

If the macro is enabled, I want those contents cleared, or if macros are
already enabled, for those contents to clear on open.

If what I'm asking won't achieve my desired results, please enlighten me.

Thanks,

Mike

Try this macro:

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("B1").Clear
End Sub

Hope this helps / Lars-Åke

 

[CraigKer]

The following will clear the the contents of B1 when you open the workbook
and say "yes" to enable macros:

Sub auto_open()
Worksheets("Sheet1").Range("B1").ClearContents
End Sub

However, they will not see what is in B1 prior to saying yes to macros since
the sheet will not yet be displayed on the screen. Also is they save the
sheet the contents of B1 are gone forever.

 

[mike]

Thanks for all you input, Mike and Lars code works but not if my sheet is
protected (even if the cell B1 is unprotected). How do I get around this?

I couldn't get Craigs suggestion to work.

 

[Mike H]

Try this

Private Sub Workbook_Open()
Sheets("Sheet1").Unprotect Password:="Mypass"
With Sheets("Sheet1").Range("B1")
.Font.ColorIndex = 0
.ClearContents
.Font.Bold = False
End With
Sheets("Sheet1").Protect Password:="Mypass"
End Sub

Mike

 

[mike]

Worked like a charm...Thanks again.

Try this

Private Sub Workbook_Open()
Sheets("Sheet1").Unprotect Password:="Mypass"
With Sheets("Sheet1").Range("B1")
.Font.ColorIndex = 0
.ClearContents
.Font.Bold = False
End With
Sheets("Sheet1").Protect Password:="Mypass"
End Sub

Mike

 

[Mike H]

your welcome

Worked like a charm...Thanks again.

 

[Lars-Åke Aspelin]

That was very strange. If cell B1 is not locked, the macro should work
even if the worksheet is protected.

Lars-Åke