Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Unprotecting a worksheet Results

Hey everyone,

I have a set of code that runs on the double-click event on a worksheet, to
display a user form to assist the user with entering data into the worksheet.
The user form does some data validation and then writes the values entered in
the form into the worksheet via VBA code.

The VBA code Unprotects the sheet, and re-protects before and after entering
the data. The ActiveSheet.Protect command is the very last line of code in
the subroutine before End Sub.

One user (and only one) of the sheet is getting the standard Microsoft Excel
error "The cell or chart you are trying to change is protected and therefore
read-only." after the code runs. This is despite the fact that the code has
already written all values to the sheet, and has re-protected as it's last
action before End Sub. The user is not triggering any other events which
would account for this error, and I don't know why Excel is giving the error,
nor can i work out what it is that is trying to change the data in the
worksheet.

I have tried it on their computer, stepping through my code in break mode,
and the error only comes up after the VBA has run and reached End Sub. There
are no events triggered by the user other than the initial double-click which
starts the code, and no other macros/code/events which are triggered in any
way by the user or my code.

Has anyone experienced this, and knows how to get around it?

Thanks,
Wayne

Hi all,
I have a workbook containing ~60 worksheets in which I would like to hide
~50 of them but have individual worksheets open on demand. The worksheets
contain hyperlinks to other worksheets within the same workbook and are
simply referenced as an Excel hyperlink. On clicking a hyperlink I would
like it to unhide the relevant worksheet, on hitting the Excel Web 'back'
button I would like it to rehide the relevant worksheet.
Is this feasible as currently constructed or am I looking at additional
macros to replace the hyperlinking?
Extract of the code doing the worksheet hiding:
---
Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim wst As Worksheet

'Ensure the Data sheet is hidden and can't be shown without password
ThisWorkbook.Worksheets("Data").Visible = xlVeryHidden

'Ensure the Exchange Rate sheet is hidden and can't be shown without
password
ThisWorkbook.Worksheets("ExchangeRates").Visible = xlVeryHidden

'protect the Worksheets from alteration
For Each wst In ThisWorkbook.Worksheets
wst.Protect (PWORD)
Next wst

'Unprotect the Exchange Rate Worksheet to obtain Reuters updates
ThisWorkbook.Worksheets("ExchangeRates").Unprotect PWORD

'activate the MainMenu worksheet
ThisWorkbook.Worksheets("MainMenu").Activate

'define the data range names
DefineDataColumns

'sort the specifed data ranges
SortDataRanges "Data_AssociatedDealerGroup", "Data_DealerNames",
"Data_DealerIDs"
SortDataRanges "Data_AssociatedUnitCode", "Data_RMCodes"
SortDataRanges "Data_RMUnitCodes", "Data_RMUnitNames"
SortDataRanges "Data_AssDealerGrp", "Data_AssMarginTempl"

Exit_Workbook_open:
Exit Sub
ErrorHandler:
DisplayError Err.Number, Err.Description
Resume Exit_Workbook_open
End Sub
---
Any help will be greatly appreciated - I have read through the other
discussion threads, but I don't see anything specific to my requirements.
Regards,

I'd appreciate any tips as to a better way to deal with the following:

I am dealing with unprotecting and protecting some data in order to do
some updates to a worksheet. I want to find a way that VBA knows my
current filename so that I can avoid using the following:

Workbooks("Data_Apr2005.xls"). _
Worksheets("Cash Flow").Protect DrawingObjects:=False

Is there a way to specify the path the user opened the file from and
the filename without hardcoding the information?

Thanks!

The following causes "Variable Not Defined" message
NOTE: Hide rows macro works great. I can't get the unprotect and protect to
work
if spite of numerous attempts at understanding user group info and the
microsoft help. Obviously a lot to learn yet.
Is part of the problem the positioning of the unprotect statement?
This macro will be used for a number of worksheets/workbooks so I can't have
the statement directly identify the worksheet name (since it is different in
each workbook).

Option Explicit
Sub Hide_Rows()
' Hide_Rows Macro
' January 11, 2005
' From Frank Kabel
' Keyboard Shortcut: NONE

Worksheet.Unprotect Password:="password"

Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "x" Then
Rows(RowNdx).Hidden = True
End If
Next RowNdx
Application.ScreenUpdating = True

Worksheet.Protect Password:="password"

End Sub
--
John F. Scholten

I have a protected worksheet. How could I copy and paste within the
same worksheet without unprotect it? Any help will be highly
appreciated!

Is there a way to add comments to an unprotected cell in a protected
worksheet? The option to insert a comment is not available in the insert
menu even when the cursor is on the unprotected cell.

Thanks
Dave

I am using Excel XP.

I have a workbook with 12 worksheets, 1 for each month of the year. Each worksheet has a timesheet used to report hours. Each of the twelve timesheets is exactly the same.

To protect each sheet, I go into each sheet and apply the Tools/Protection/Protect Sheet approach. I have to repeat this 12 times to protect them and then 12 times to unprotect them.

My question is "Is there a faster way to apply and remove worksheet protection?

Appreciate your help!!!!!!!!!

I have successfully used the code from a previous discussion post to activate
the spell check function in a protected worksheet.

However when I share the workbook and run the macro the error: "400"
appears. When I go to the Help option the help screen is blank.

Does anyone have any idea how I can use the spell check in a protected
worksheet within a shared workbook?

The code used from the previous discussion is shown below.

Many thanks
Dave

Cats

Requires VBA to unprotect the sheet, do the spellcheck then reprotect the
sheet.

Similar to.......

Sub Spell_Check()
ActiveSheet.Unprotect Password:="justme"
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

Gord Dibben Excel MVP

On Wed, 16 Feb 2005 11:05:06 -0800, "Cats" <Cats@discussions.microsoft.com>
wrote:

>Office 2003 - Excel
>I am trying to make it possible for personnel to spell check information
>they insert, in a unlocked cell in a password protected worksheet. I have
>tried everything I can find in all HELP areas, but cannot find anything

Excel 2003 worksheet
I have data in B1 being the header row through to Y366.
I have selected all cells and unlocked them. I have then selected B1:Y1 and
locked them as they are my header row.
I then went tools,protection,allow users to edit and input a range of
B2:Y366. did not want to give password permission, clicked apply and then
clicked protect sheet and checked unlocked cells and sort and then OK.
When I then go to sort data it tells me "The cell or chart you are trying to
change is protected and therefore read-only."
To modify a protected cell or chart, first remove protection using the
Unprotect the sheet command (Tools menu, Protection submenu)> You may be
prompted for a password.
What am I doing wrong. Should it not sort even though the protection is on
if I have a user range input?
Someone please help.
I did have columns hidden but they have all be displayed prior to the first
step as above.
I want it protected as I have formula's that will be tucked away on my
hidden columns that I don't want people to get to and wipe by mistake.
Thanks
Sue

I've got a workbook with one of the worksheets password protected. When
trying to save the workbook as an HTML file, Excel complains about it
and refuses to do so unless the worksheet is unprotected.

I've tried putting in the call to Unprotect at the Before Close event
but found that it doesn't work. Calling the code in a Command Button
does. However, I can't do it this way (for some reason).

So any idea how to work around this thing?

Thanks in advance.

If I have a file that's password protected is there a way around this? It's for a world cup spreadsheet that I've been trying to get going for a while. I want to be able to see what formulae they used but since it's protected I don't know how to get at it.

Any help would be appreciated.

Hello,
I have a spreadsheet in Excel 2003 and have the following problem.
Details. It is a protected worksheet, but the malfunction occurs whether
it's protected or unprotected.

Whenever I try to copy (works fine and is visible in the toolbar) an item
(formula, words, etc) I am able to use the edit copy command or the control
c command.
I'll go to my cell where I want to paste it and the paste function isn't
operational and neither is the control v.

Whenever I open another "new" worksheet the copy/paste functions work fine.

Since this a is pretty complex worksheet that I had some assistance on
building I don't know where to start.

Does anyone have any idea what the problem might be, or would someone be
will to take a look at it for me to see if it can be fixed.

Thanks
Bob Reynolds

I tried to sort a sheet via VBA and kept getting an error until I
figured it was the protection stopping things. How can I turn off
protection, sort, then turn on protection again?

I looked in help and found ActiveWorkbook.Unprotect ("password") but it
doesn't work. Any pointers in the right direction much appreciated.

TIA,
R.

I am working in Excel 2000, with a protected worksheet emailed to me from
another co-worker. After unprotecting the worksheet (and workbook), I am
still unable to revise a formula. When I make the revision and press enter
(or the green checkmark) the formula does not change, but instead appears in
the cell that the calculation formerly appeared in. How do I make the
revision to the formula?

I protect a worksheet (with password) and choose the option to let it be
sorted, then when I go to Data, sort, I get an error message telling me to
unprotect the sheet. I have read that Excel 2002 is supposed to let you do
this. (hence, the list of options) Am I missing part of the program or what
am I doing.??? I have formulas on the page so also a number of locked
cells... thanks for any guidance.

Hi,

I'm using Excel 2003. I need to know what the purpose of Protect Workbook
is under Tools | Protection. It gives 2 options, protect workbook for
Structure and Windows.

I understand the options and functionality under Protect Worksheet, and I
thought that the Protect Workbook option would do the same for the whole
workbook instead of individual worksheets.

When trying this out, I protected a workbook, saved it, but was still able
to edit it and resave without having to put in a password or even unprotect
it.

So, what does Protect Workbook do and what are the Structure and Windows
options for.

Thanks,

Dan

I have an Excel document that I have used in my old workplace to track Petty
Cash.
I am now using it within my new work environment, and want to change the
house name, dates, etc.. so they are on all sheets.

For some reason on the tabs, not all options are able to access (they are
grey, instead of black). I also want to bold the name of the house, but the
bold, underline and italicize options aren't able to be pushed..

Why and any quick ways to get around that?

It is asking me for a password when I look to unprotect the sheet, but I
have no idea of it, can i get around that?

james

Hello all - I am trying to get my macro to copy one worksheet to a new worksheet, clear a few cells, and rename the sheet to the next consecutive number. I have been able to get the macro to do it properly the first time but when I try to reference the newly created sheet, it references the 1st sheet. What I would like to accomplish is have new sheet #2 reference sheet #1, new sheet #3 reference sheet #2, new sheet #4 reference sheet 3 and so on. These worksheets are in the middle of a workbook with about 10 sheets in it. Any help would be appreciated. This is what I have so far.

Sub
ADDNEWBITRUN()
'
' ADDNEWBITRUN Macro
'

'
    ActiveSheet.Unprotect
    Sheets("BITRUN 1").Select
    Sheets("BITRUN 1").Copy Before:=Sheets(6)
    Range("I12").Select
    ActiveCell.FormulaR1C1 = "='BITRUN 1'!R[2]C"
    Range("I11").Select
    Selection.ClearContents
    Range("I13").Select
    Selection.ClearContents
    Range("I16").Select
    Selection.ClearContents
    Range("I18").Select
    ActiveCell.FormulaR1C1 = "='BITRUN 1'!R[1]C"
    Range("I19").Select
    Selection.ClearContents
    Range("I22").Select
    ActiveCell.FormulaR1C1 = "='BITRUN 1'!R[1]C"
    Range("I23").Select
    Selection.ClearContents
    Range("J8").Select
    ActiveCell.FormulaR1C1 = "='BITRUN 1'!RC+1"
    Range("J9").Select
    ActiveWindow.SmallScroll Down:=3
    Range("I38:I42").Select
    Selection.ClearContents
    Range("D39:D41").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=12
    Range("B45:J52").Select
    Selection.ClearContents
    Range("G20:H20").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Range("D21").Select
    ActiveSheet.Name = "BITRUN " & Format(Range("J8"))
    End Sub


Aim/ Object — I've made this Time Tracker workbook and it's working fine. I want to keep protected the cells of columns A, C, D and E, so I just added the codes in the VBA. Therefore one can run the time but can not delete the value.

Problem — I want to allow formatting in these protected cells through VBA codes.

If I put below code in the last steps of my macro:

    Worksheets(1).Activate
    Worksheets(1).Protect (123)
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True

...then the required thing (allowing formatting) is enable on the protected cells, but If I want to manually unlock the protection then it doesn't asks the password. The protection of the worksheet is just removed by clicking on the Unprotect Sheet option(in the Tool Menu).

In this case anyone can misuse the workbook, easily can delete the data since there is no requirment of the password.

OR

If I put the above code as follows (in the last steps of my macro):

    Worksheets(1).Activate
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    Worksheets(1).Protect (123)

...then no one can unlock/ break the protection without the password. It will ask the password if somebody wants to manually unlock the worksheet, but the other side this code doesn't allow formatting on the protected cells.

How can I enable formatting options on the protected cells, and if somebody wants to unprotect the worksheet then he must need the password too?

Note - I am testing with password: 123

Kindly help me out!

Thanks in Advance!

I need help with the wording in the macro.. specifically, the part where the macro has to paste the column from the material matrix 8-24-10 back into column C of each ticket page of the workbook.. I need to change the words “service worksheet – JDH version 4TEST” to a phrase that will let the macro paste back into the sheets no matter whatever the workbook’s name is. See below...

Sub Prepare_workbook_for_invoicing()

'

' Prepare_workbook_for_invoicing Macro

' prepare the workbook for invoicing

'

 

'

    Sheets("Daily Service Report").Select

    ActiveSheet.Unprotect Password:="JDH"

    Sheets("Tkt #1 Material").Select

    ActiveSheet.Unprotect Password:="JDH"

    Sheets("Tkt #2 Material").Select

    ActiveSheet.Unprotect Password:="JDH"

    Sheets("Tkt #3 Material").Select

    ActiveSheet.Unprotect Password:="JDH"

    Sheets("Tkt #4 Material").Select

    ActiveSheet.Unprotect Password:="JDH"

    Sheets("Tkt #5 Material").Select

    ActiveSheet.Unprotect Password:="JDH"

    Sheets("Daily Service Report").Select

    Range("D4:F4").Select

    Sheets("Tkt #1 Material").Select

    ActiveSheet.Outline.ShowLevels RowLevels:=4

    Range("A3:E346").Select

    Selection.AutoFilter

    Sheets("Tkt #2 Material").Select

    ActiveSheet.Outline.ShowLevels RowLevels:=4

    Range("A3:E346").Select

    Selection.AutoFilter

    Range("B4").Select

    Sheets("Tkt #3 Material").Select

    ActiveSheet.Outline.ShowLevels RowLevels:=4

    Range("A3:E346").Select

    Selection.AutoFilter

    Range("B4").Select

    Sheets("Tkt #4 Material").Select

    ActiveSheet.Outline.ShowLevels RowLevels:=4

    Range("A3:E346").Select

    Selection.AutoFilter

    Range("B4").Select

    Sheets("Tkt #5 Material").Select

    ActiveSheet.Outline.ShowLevels RowLevels:=4

    Range("A3:E346").Select

    Selection.AutoFilter

    Range("B4").Select

    Sheets("Daily Service Report").Select

    Range("D4:F4").Select

    ChDir _

        "C:Documents and SettingsxxxxxMy DocumentsJDH - CBRE Invoicing"

    Workbooks.Open Filename:= _

        "C:Documents and SettingsxxxxxMy DocumentsJDH - CBRE InvoicingJDH - KENTUCKY - MATERIAL PRICING MATRIX 
8-24-10.xls"

    Columns("F:F").Select

    Selection.Copy

    Windows("service worksheet - JDH version 4TEST.xls").Activate

    Sheets("Tkt #1 Material").Select

    Columns("C:C").Select

    Range("C3").Activate

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("Tkt #2 Material").Select

    Columns("C:C").Select

    Range("C3").Activate

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("Tkt #3 Material").Select

    Columns("C:C").Select

    Range("C3").Activate

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("Tkt #4 Material").Select

    Columns("C:C").Select

    Range("C3").Activate

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("Tkt #5 Material").Select

    Columns("C:C").Select

    Range("C3").Activate

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("Daily Service Report").Select

    Range("D4:F4").Select

End Sub



No luck finding an answer? You could always try Google.