Possible to change default Protected Sheet Msg?

B

BAlGaInTl

Well-known Member
Joined
May 7, 2003
Messages
1,082
Hey all...

I have a workbook that I have designed. It is my first attempt at using VBA, and it is going fairly well.

I have a data entry worksheet where all of the manually entered data is stored. I want people to view it, and select cells, but not to change the data. I protected the sheet no problem.

I created a VBA form that when the "OK" button is clicked, the sheet is unprotected, the data is entered, and then the sheet is protected again.

My question is this...

When a user tries to manually change a cell in the data entry sheet, they get the standard Excel message... "The cell or chart you are trying to change is protected... "

This message gives information on how to unprotect the sheet.

Is there a way to change that default message and have it tell the user to open the Data Entry form? Can I even add an option to the message to open the form?

Any help is greatly appreciated.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Ok...

Is there a way to cause a custom message to pop up when a person tries to edit a protected worksheet?
 
Upvote 0
Technically you could build a Worksheet_Change macro that blasted the user anytime then updated the cell. If you wanted the user to be able to type in some cells you could even put a case statement with the cell ranges. I think its possible but not through the typical protection that Excel has.

Hope this helps!
 
Upvote 0
using cell formatting, make sure that all cells that require entry through the data entry form are locked, and that all other cells are unlocked. however, do not turn sheet protection on. instead, use the following macro:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Locked = True Then
    Application.Undo
    MsgBox "You must use the Data Entry Form to enter data in this cell"
End If
Application.EnableEvents = True
End Sub

then in your userform, make sure that when your userform is opened, you include a line of code in the userform_initialize procedure that sets Application.EnableEvents to False, and in the code that hides your userform, set EnableEvents back to True.

this is not a secure and can be defeated, but it will get rid of the standard excel message you get when protect is on, enabling you to customize you message

hth
kevin
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Using VBA code in a protected excel sheet
Replies
2
Views
293
MagicCarpet
M
C
  • Question
Hiding Sheets during the Before_Close Event
2
Replies
10
Views
781
Jaafar Tribak
Jaafar Tribak
M
  • Question
Not running code
Replies
0
Views
150
MagicCarpet
M
GMC The Macro Man
  • Question
Sorting a table in a protected sheet when some cells are locked -MACRO needed
Replies
3
Views
197
RoryA
RoryA
G
  • Question
Bug with passing variables between user forms--VBA
Replies
4
Views
417
Domenic
D

Forum statistics

Threads
1,221,310
Messages
6,159,173
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top