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

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

"mike" wrote:

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

On Fri, 23 Jan 2009 12:18:07 -0800, mike
wrote:

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

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" wrote:

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

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" wrote:

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

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" wrote:

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" wrote:

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

Worked like a charm...Thanks again.

"Mike H" wrote:

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" wrote:

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" wrote:

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

your welcome


"mike" wrote:

Worked like a charm...Thanks again.

"Mike H" wrote:

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" wrote:

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" wrote:

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

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

Lars-Åke


On Fri, 23 Jan 2009 13:36:01 -0800, mike
wrote:

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" wrote:

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