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.
 
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
  • Solved
unprotect sheet
Replies
1
Views
95
Joe4
Joe4
H
  • Question Question
VBA to protect/unprotect sheet...
Replies
6
Views
753
jkpieterse
jkpieterse
Jeffrey Mahoney
  • Question Question
ActiveX Textbox on Sheet - Scroll Using VBA
Replies
6
Views
124
Jaafar Tribak
Jaafar Tribak
J
  • Question Question
Difficulties in Refreshing Pivot Tables in Excel 2024 When the Source Table and/or Pivot Tables have Protected Cells
Replies
0
Views
151
jfrsanders
J
R
  • Solved
Msbox VBA code when cell value is changed
2
Replies
18
Views
1K
Ramadan
R

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