Notify if value entered in Template

[kishorkhanal]

I have a worksheet named "Template". The template worksheet has to be copied as a new worksheet with a different name to enter any values. I do not want my users to accidently enter data in the Template and save. To avoid this, if the user tries to enter data in the "Template" worksheet, I want an warning to appear "You are trying to enter data in the Template. Please copy this template to enter data." Once a copy is made, the worksheet should allow entering data without any error message. Please help.

 

[taigovinda]

In my view, it would be much better to save your Template as read-only (from the windows file explorer, right-click the file, properties, check the Read Only box). That way, anytime someone tries to save it will force them to change the file name.

If you wanted to do it in code (keep in mind they have to enable macros for this to have any effect), maybe this code at the workbook level (right-click the green Excel logo at the top left of the screen, View Code, and paste this in):

Option Explicit
Option Compare Text
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Left(ThisWorkbook.Name, 10) = "Template.x" Then
    MsgBox "You are trying to enter data in the Template. Please copy this template to enter data."
    'undo what they did
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
End If
End Sub

Hope that helps.

Tai

 

[kishorkhanal]

I cannot make the workbook read only because I want the Template worksheet copied as a new worksheet in the same workbook. My workbook name is "Calculator.xls" and template worksheet name is "Template". I used above code but does not work.

 

[kishorkhanal]

Anybody please?

 

[jc.021286]

Hey there,

I've done something similar. With templates I normally have a page with instructions on how to use the file. In there I would place a button, that button would create the new sheet.

VBA wise, I'd say just set to copy a sheet.
Thus,

Sub Macro1()
Sheets("Template").Visible = True
    Sheets("Template").Copy After:=Sheets(Application.Sheets.Count)
Sheets("Template").Visible = xlVeryHidden
End Sub

This will keep the template safely hidden away from prying eyes by making it very hidden.

to unhide it for editing and the other, just create a 1 line code
Sheets("Template").Visible = True

and run that.

With VeryHidden, be sure to remember what the name of the sheet is, if you ever forget, it will remain hidden for all eternity!!!

Hope this solves your problem,
jc

 

[taigovinda]

I cannot make the workbook read only because I want the Template worksheet copied as a new worksheet in the same workbook. My workbook name is "Calculator.xls" and template worksheet name is "Template". I used above code but does not work.

I had misunderstood what you are doing... You can change my code to this, should serve your purpose:

Option Explicit
Option Compare Text
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Template" Then
    MsgBox "You are trying to enter data in the Template. Please copy this template to enter data."
    'undo what they did
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
End If
End Sub

 

[kishorkhanal]

I can't make it work. No warning messages. I copied the code in ThisWorkbook. Have you checked the code? Let me know if I need to do something to make it work.
Thanks.

 

[taigovinda]

I did test and it gave a pop-up warning when I tried to edit cells in a tab called Template...

You could also put the code directly onto the Template tab, see if this works (right-click template tab name, View Code, paste in):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "You are trying to enter data in the Template. Please copy this template to enter data."
    'undo what they did
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
End Sub

If still not working, try running this first from a module:

sub TurnOnEvents
application.enableevents=true
end sub

 

[jc.021286]

Kish,

Give this code a quick try in a new workbook.
1. make sure the tab Template is not locked / password protected
2. make sure the tab is called "Template"

Go to View > macros > Run : Macro1 (or change the name to something more appropriate)
you could also create a button (insert > object > draw a square, right click, edit text> Type: "Push Me", unselect the button from editing and right click it and go to assign macro. Link it to macro1 (or new amazing name) and you will ensure that nobody destroys your template and you can make as many copies as you wish.

Give it a try and let me know,
jc

Sub Macro1()
Sheets("Template").Visible = True
    Sheets("Template").Copy After:=Sheets(Application.Sheets.Count)
Sheets("Template").Visible = xlVeryHidden
End Sub