The Elsmar Cove Wiki More Free Files The Elsmar Cove Forums Discussion Thread Index Post Attachments Listing Failure Modes Services and Solutions to Problems Elsmar cove Forums Main Page Elsmar Cove Home Page
Go Back   The Elsmar Cove Forum > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel .xls Spreadsheet Templates and Tools
Forum Username


Search the Elsmar Cove
Custom Search
Monitor the Elsmar Forum
Follow Marc & Elsmar
Elsmar Cove Forum RSS Feed  Marc Smith's Google+ Page  Marc Smith's Linked In Page   Marc Smith's Elsmar Cove YouTube Page  Marc Smith's Facebook Page  Elsmar Cove Twitter Feed
Elsmar Cove Groups
Elsmar Cove Google+ Group  Elsmar Cove LinkedIn Group  Elsmar Cove Facebook Group
Sponsor Links





Donate and $ Contributor Forum Access
Courtesy Quick Links

Links that Elsmar Cove visitors will find useful in your quest for knowledge:

Howard's
International Quality Services
Marcelo Antunes'
SQR Consulting
Bob Doering's
Correct SPC - Precision Machining

NIST's Engineering Statistics Handbook
IRCA - International Register of Certified Auditors
SAE - Society of Automotive Engineers
Quality Digest Portal
IEST - Institute of Environmental Sciences and Technology
ASQ - American Society for Quality

Related Topic Tags
microsoft excel .xls .xlsx spreadsheet
Reply
 
Thread Tools Search this Thread Rate Thread Content Display Modes
  #1  
Old 28th April 2012, 03:37 PM
124McNaughton 124McNaughton is offline
Getting Involved (6 to 9 Posts)

 
Registration Date: Mar 2010
Location: Canada
 
Posts: 7
Thanks Given to Others: 0
Thanked 0 Times in 0 Posts
Karma Power: 17
Karma: 10
124McNaughton has less than 100 Karma points so far.
Please Help! VBA Needed to Protect Individual Excel Cells

I am trying to create an approval button for my manager that will show the date of the approval and his name along with protecting the cells so they cannot be changed. He would also like the protected cells to be password protected. So far I have a short VBA program that will enter his name and the date he approved the work. I am stuck on the protection aspect of the program, the best that I can do is protect the entire work sheet not the individual cell with a password. I have included the VBA program that I have developed so far.
Any help would be greatly appreciated.

Private Sub ApprovelButton1_Click()
With ActiveSheet
.Unprotect
.Cells(7, 5) = Date
.Cells(7, 6) = "John Doe"
.Protect
End With
End Sub


Thanks in Advanced.

Sponsored Links
  #2  
Old 28th April 2012, 09:22 PM
michaelrm's Avatar
michaelrm michaelrm is offline
Involved in Discussions

 
Registration Date: Nov 2011
Location: Perth , Western Australia
 
Posts: 45
Thanks Given to Others: 6
Thanked 24 Times in 20 Posts
Karma Power: 14
Karma: 134
michaelrm is appreciated, and has over 100 Karma points.michaelrm is appreciated, and has over 100 Karma points.
Send a message via Skype™ to michaelrm
Re: VBA Needed to Protect Individual Excel Cells

try this

Sub ProtectTheSheet()
Cells.Locked = False
Range("A3:A12,D3:E12,J1:R13,W18").Locked = True
ActiveSheet.protect
End Sub
Sponsored Links

  #3  
Old 29th April 2012, 12:23 AM
Hodgepodge's Avatar
Hodgepodge Hodgepodge is offline
Appreciated Member

 
Registration Date: Jul 2008
Location: Phoenix, Arizona, U.S.A.
Age: 43
 
Posts: 281
Thanks Given to Others: 634
Thanked 243 Times in 148 Posts
Karma Power: 59
Karma: 3765
Hodgepodge is appreciated, and has over 1700 Karma points.
Hodgepodge is appreciated, and has over 1700 Karma points.Hodgepodge is appreciated, and has over 1700 Karma points.Hodgepodge is appreciated, and has over 1700 Karma points.Hodgepodge is appreciated, and has over 1700 Karma points.Hodgepodge is appreciated, and has over 1700 Karma points.Hodgepodge is appreciated, and has over 1700 Karma points.Hodgepodge is appreciated, and has over 1700 Karma points.
Re: VBA Needed to Protect Individual Excel Cells

Assign this macro to your button.

Code:
Sub Rectangle2_Click()
    With ActiveSheet
        .Unprotect Password:="Banana2000"
        .Cells(7, 5) = Date
        .Cells(7, 6) = "John Doe"
        .Cells.Locked = False
        .Range("E7:F7").Locked = True
        .Protect Password:="Banana2000"
    End With
End Sub
See the attachment for an example. I wouldn't normally use "Range" & "Cells" in the same code. I just put it in there the different methods in case others feel more comfortable with one method or the other.
Attached Files: 1. Scan for viruses before using, 2. Please report any 'bad' files by Reporting this post, 3. Use at your Own Risk.
File Type: xlsm Protect Cells Research.xlsm (18.4 KB, 44 views)

__________________

None of the secrets of success will work unless you do. - Fortune Cookie
Thanks to Hodgepodge for your informative Post and/or Attachment!
Reply

Lower Navigation Bar
Go Back   The Elsmar Cove Forum > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel .xls Spreadsheet Templates and Tools

Do you find this discussion thread helpful and informational?


Bookmarks


Visitors Currently Viewing this Thread: 1 (0 Registered Visitors (Members) and 1 Unregistered Guest Visitors)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Forum Search
Display Modes Rate Thread Content
Rate Thread Content:

Forum Posting Settings
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Discussion Threads
Discussion Thread Title Thread Starter Forum Replies Last Post or Poll Vote
Controlling Minitab by vba (visual basic) or vb Inspector-71 Using Minitab Software 6 25th April 2012 05:52 AM
Individual/Moving Range Chart (IMR Chart) in Excel amroczek Excel .xls Spreadsheet Templates and Tools 8 1st February 2012 05:45 AM
Excel Time Calculations and Formulas help needed tad_bit_lost Excel .xls Spreadsheet Templates and Tools 15 16th August 2010 05:25 PM
Free Introduction Course - VBA (Visual Basic for Application) in Excel to make forms jrubio Excel .xls Spreadsheet Templates and Tools 10 30th May 2008 04:48 PM
Excel formula for Ppk needed Aitch Excel .xls Spreadsheet Templates and Tools 44 1st July 2006 05:12 AM



The time now is 04:58 PM. All times are GMT -4.
Your time zone can be changed in your UserCP --> Options.


   


Marc Timothy Smith - Elsmar.com
8466 LeSourdsville-West Chester Road, Olde West Chester, Ohio 45069-1929
513 341-6272