Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-23-2012, 12:49 PM
AHB AHB is offline Cell formating on Protected worksheet Windows 7 32bit Cell formating on Protected worksheet Office 2010 32bit
Novice
Cell formating on Protected worksheet
 
Join Date: Dec 2011
Posts: 5
AHB is on a distinguished road
Default Cell formating on Protected worksheet

Hi, I'm a novice at VBA.

This is VBA code I'm using to change the formating in 2 cells based on the value in another cell. This code works fine when worksheet is not protected but the text formating and locking don't work when wkst is protected. Not sure what I've done wrong. Any help appreciated.

Private Sub Steam_Option_Change()
'Trigger for locking second press and temp input cells
Application.ScreenUpdating = False
If Range("BC409").Value = True Then
Range("AP33").Select
ActiveCell.FormulaR1C1 = "'-- n/a --"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 16
Selection.Locked = True
Selection.FormulaHidden = False
Range("AP34").Select
ActiveCell.FormulaR1C1 = "'-- n/a --"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 16
Selection.Locked = True
Selection.FormulaHidden = False
ElseIf Range("BC409").Value = False Then
Range("AP33").Select
Selection.ClearContents
Selection.Font.Bold = False


Selection.Font.ColorIndex = 0
Selection.Locked = False
Selection.FormulaHidden = False
Range("AP34").Select
Selection.ClearContents
Selection.Font.Bold = False
Selection.Font.ColorIndex = 0
Selection.Locked = False
Selection.FormulaHidden = False
End If
End Sub
Reply With Quote
  #2  
Old 02-25-2012, 10:45 PM
Catalin.B Catalin.B is offline Cell formating on Protected worksheet Windows Vista Cell formating on Protected worksheet Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

INsert this line at the begining of your code, to unprotect worksheet, a macro cannot make those changes on a protected sheet:
activesheet.Unprotect Password:="Secret"
At the end of the code, you may want to protect it :
Activesheet.Protect Password:="Secret"
Reply With Quote
  #3  
Old 02-27-2012, 10:25 AM
AHB AHB is offline Cell formating on Protected worksheet Windows 7 32bit Cell formating on Protected worksheet Office 2010 32bit
Novice
Cell formating on Protected worksheet
 
Join Date: Dec 2011
Posts: 5
AHB is on a distinguished road
Default

Thanks, Catlin.B. Although I don't like the idea of unprotecting/protecting the wkst within a macro, it looks like that may be only way I can get it to work.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell formating on Protected worksheet Insert/Paste Picture into Protected Worksheet udea Excel 1 02-06-2012 07:43 PM
Cell formating on Protected worksheet "Auto-populating" data-worksheet to worksheet. meggenm Excel 4 02-04-2012 02:04 AM
Cell formating on Protected worksheet Word mail merge from a protected Excel worksheet Platform Mail Merge 10 09-28-2011 05:12 AM
Cell Comments in a Protected spreadsheet dmgerber Excel 1 06-06-2010 05:10 AM
click a cell to go to a worksheet victor Excel 0 10-27-2006 02:57 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:47 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft