UN protect your worksheet manually or by code. TESTED fine on file presented.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Abdulgani Shaikh
Sent: Sunday, April 15, 2012 4:43 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock
My sheet contains some tax formula and I want to keep these formula protected,
if inadvertently they deleted or corrected, it will not give correct answer.
If I am protecting the worksheet, it gives error at
Range("A1:J58").SpecialCells(xlCellTypeConstants, 1).ClearContents
Please guide
Thanks a lot for everything, that's what I want really.
Regards
On Sat, Apr 14, 2012 at 8:34 PM, dguillett1 <dguille...@gmail.com> wrote:
On your protection, why not just leave unprotected...
On your macros, see attached (Sent direct to OP)
‘-----------
Option Explicit
Sub AddSheetSAS()
ActiveWorkbook.Save
Dim i As Long
Dim s, k As String
s = InputBox("Please Enter INITIALs of Employee as Sheet Name to be added")
For i = 1 To Worksheets.Count
k = Worksheets(i).Name
If UCase(k) = UCase(s) Then
MsgBox "Sheet Already Exists"
Exit Sub
End If
Next i
ActiveWorkbook.Unprotect
Sheets("Master").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = s
Range("A1:J58").SpecialCells(xlCellTypeConstants, 1).ClearContents
Range("h13") = 12
Range("a2").Select
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub
Sub DeleteSheetSAS()
Dim sht As String
ActiveWorkbook.Unprotect
On Error GoTo nosuchsheet
sht = InputBox("Please Enter Sheet Name to be deleted")
Application.DisplayAlerts = False
Sheets(sht).Delete
Application.DisplayAlerts = True
ActiveWorkbook.Protect Structure:=True, Windows:=False
Exit Sub
nosuchsheet:
MsgBox "The sheet does not not exist"
Application.DisplayAlerts = True
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub
Sub NextSheetSAS()
On Error Resume Next
Sheets(ActiveSheet.Index + 1).Activate
If Err.Number <> 0 Then Sheets(1).Activate
End Sub
Sub PreviousSheetSAS()
On Error Resume Next
Sheets(ActiveSheet.Index - 1).Activate
If Err.Number <> 0 Then Sheets(1).Activate
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: ITP Abdulgani Shaikh
Sent: Saturday, April 14, 2012 7:14 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock
Dear Freinds,
I am tax practitioner and needs to make Tax working of lot of salaried
employees. I have made one template for the said purpose and needs your help
on some issues.
My worksheet is protected, out of all cells, some cells are unprotected for
entering data. If I am entering data in Cell E13 then Cell H13 should be auto
unlock for entering data. and if there is no data in Cell C3 or zero in C3
then Cell E3 should be auto locked.
I want to give following option in my Tax Working Sheet
01. ADD new sheet = This option is working correctly
BUT, following buttons are not
02. DELETE sheet with option to choose sheet by entering sheet name, i have
tried, but its not working. Current selected sheet goes deleted.
03. PREV = Option to go to previous sheet. (Please also considering that
after first sheet, it must be stopped)
04. NEXT = Option to go to next sheet. (Please also considering that after
last sheet, it must be stopped)
I am attaching herewith file, please guide.
Regards
--
Shaikh AbdulGani A R
ITP, STP, TRP, STRP
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
