Disable auto xlmsgbox, replace w/own
 

Hi, everyone,
Can someone tell me how to disable
the xl auto message box and replace
it with my own?
I want to have my own messages displayed when
my worksheet cells are locked.
Thanks!

Disable auto xlmsgbox, replace w/own
 

Myriam,

No, you cannot change the locked cell message.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Myriam" wrote in message
...
Hi, everyone,
Can someone tell me how to disable
the xl auto message box and replace
it with my own?
I want to have my own messages displayed when
my worksheet cells are locked.
Thanks!

Disable auto xlmsgbox, replace w/own
 

Thanks.

"Chip Pearson" wrote:

Myriam,

No, you cannot change the locked cell message.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Myriam" wrote in message
...
Hi, everyone,
Can someone tell me how to disable
the xl auto message box and replace
it with my own?
I want to have my own messages displayed when
my worksheet cells are locked.
Thanks!

Disable auto xlmsgbox, replace w/own
 

If you protect the worksheet in code, you can make it so the user can't even
select those cells to change them:

With Worksheets("sheet1")
.Protect Password:="hi"
.EnableSelection = xlUnlockedCells
End With

But this setting isn't remember if you close the workbook. It has to be run at
least once when you reopen the workbook (auto_open or workbook_open would be a
nice home.)

Myriam wrote:

Hi, everyone,
Can someone tell me how to disable
the xl auto message box and replace
it with my own?
I want to have my own messages displayed when
my worksheet cells are locked.
Thanks!

--

Dave Peterson

Disable auto xlmsgbox, replace w/own
 

Hi Myriam

In Excel 2002-2003 this is option when you protect your sheet.
You don't need the code then that Dave posted.

Or something like this in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Target.Cells(1).Select
If Target.Locked = True Then MsgBox "your text"
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dave Peterson" wrote in message ...
If you protect the worksheet in code, you can make it so the user can't even
select those cells to change them:

With Worksheets("sheet1")
.Protect Password:="hi"
.EnableSelection = xlUnlockedCells
End With

But this setting isn't remember if you close the workbook. It has to be run at
least once when you reopen the workbook (auto_open or workbook_open would be a
nice home.)

Myriam wrote:

Hi, everyone,
Can someone tell me how to disable
the xl auto message box and replace
it with my own?
I want to have my own messages displayed when
my worksheet cells are locked.
Thanks!

--

Dave Peterson

Disable auto xlmsgbox, replace w/own
 

Just a word of warning if the OP shares the workbook with users of xl2k (or
below). You'll need the .enableselection code.

But if you won't use the workbook on earlier versions, Ron's suggestion to use
Tools|protect|protect sheet and toggle that option is far easier.

Ron de Bruin wrote:

Hi Myriam

In Excel 2002-2003 this is option when you protect your sheet.
You don't need the code then that Dave posted.

Or something like this in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Target.Cells(1).Select
If Target.Locked = True Then MsgBox "your text"
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Dave Peterson" wrote in message ...
If you protect the worksheet in code, you can make it so the user can't even
select those cells to change them:

With Worksheets("sheet1")
.Protect Password:="hi"
.EnableSelection = xlUnlockedCells
End With

But this setting isn't remember if you close the workbook. It has to be run at
least once when you reopen the workbook (auto_open or workbook_open would be a
nice home.)

Myriam wrote:

Hi, everyone,
Can someone tell me how to disable
the xl auto message box and replace
it with my own?
I want to have my own messages displayed when
my worksheet cells are locked.
Thanks!

--

Dave Peterson


--

Dave Peterson

Disable auto xlmsgbox, replace w/own
 

Dave and Ron,
Thanks you so much. I'll try your solutions tomorrow.
Regards,


"Dave Peterson" wrote:

Just a word of warning if the OP shares the workbook with users of xl2k (or
below). You'll need the .enableselection code.

But if you won't use the workbook on earlier versions, Ron's suggestion to use
Tools|protect|protect sheet and toggle that option is far easier.

Ron de Bruin wrote:

Hi Myriam

In Excel 2002-2003 this is option when you protect your sheet.
You don't need the code then that Dave posted.

Or something like this in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Target.Cells(1).Select
If Target.Locked = True Then MsgBox "your text"
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Dave Peterson" wrote in message ...
If you protect the worksheet in code, you can make it so the user can't even
select those cells to change them:

With Worksheets("sheet1")
.Protect Password:="hi"
.EnableSelection = xlUnlockedCells
End With

But this setting isn't remember if you close the workbook. It has to be run at
least once when you reopen the workbook (auto_open or workbook_open would be a
nice home.)

Myriam wrote:

Hi, everyone,
Can someone tell me how to disable
the xl auto message box and replace
it with my own?
I want to have my own messages displayed when
my worksheet cells are locked.
Thanks!

--

Dave Peterson


--

Dave Peterson

Disable auto xlmsgbox, replace w/own
 

Dave,
Thanks for the warning. I am using xl2K. Some of my clients are still using
that version, so I'll (for now at least) stick to the .enableselection code.

I really like Ron's solution but I haven't had a chance to use it
Regards,

"Dave Peterson" wrote:

Just a word of warning if the OP shares the workbook with users of xl2k (or
below). You'll need the .enableselection code.

But if you won't use the workbook on earlier versions, Ron's suggestion to use
Tools|protect|protect sheet and toggle that option is far easier.

Ron de Bruin wrote:

Hi Myriam

In Excel 2002-2003 this is option when you protect your sheet.
You don't need the code then that Dave posted.

Or something like this in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Target.Cells(1).Select
If Target.Locked = True Then MsgBox "your text"
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Dave Peterson" wrote in message ...
If you protect the worksheet in code, you can make it so the user can't even
select those cells to change them:

With Worksheets("sheet1")
.Protect Password:="hi"
.EnableSelection = xlUnlockedCells
End With

But this setting isn't remember if you close the workbook. It has to be run at
least once when you reopen the workbook (auto_open or workbook_open would be a
nice home.)

Myriam wrote:

Hi, everyone,
Can someone tell me how to disable
the xl auto message box and replace
it with my own?
I want to have my own messages displayed when
my worksheet cells are locked.
Thanks!

--

Dave Peterson


--

Dave Peterson