Unprotect Single Cell Only

Jul 10, 2012

I have a spread sheet that is protected. I would like to have cell G5 unprotected when a checkbox is checked. I am able to unprotect the entire sheet by checking the box but I only want G5 unprotected. My current macro that unprotects the entire sheet is

Sub Checkbox_Clear2()
Sheets("Input_Personnel").Unprotect ("1")
If Range("J12") = False Then Range("G5").ClearContents
End Sub

Is there a way to change the highlighted line where it only unprotects cell "G5".

View 1 Replies


ADVERTISEMENT

Protect / UnProtect All Workbooks With A Single Button Through VBA

May 5, 2008

I am having some 60+ workbooks in which the end-users are entering data. Each workbook is protected with different passwords.

We have the systems networked. The main compilation file is in the server D:MAIN folder. The workbooks for users are in D:Mainusers sub-folder. The files in users folder are only allowed to have access by the users.

Now, in the compilation sheet I have linked the data from all the individual files (which is in its subfolder). I am facing a trouble that the excel asks password for each and every workbook to update the links in the compilation workbook.

Any possibility to overcome this trouble? A VBA code is the solution? or any other alternative measure is there to solve?

View 14 Replies View Related

Customised Prompt When Trying To Unprotect A Protected Cell

Jun 12, 2009

when the user tries to enter data into Ranges E25:E26, G25:G26 OR I25:I26 I want to have my own customised prompt that says: "You are using Imperial Units. Enter drafts in the Imperial range by clicking the Imperial button on the left".

To make it more interesting, the cursor should go to the range that I have named 'Imperial', when the user clicks on the 'OK' button of the above prompt.

View 14 Replies View Related

Code To Protect And Unprotect Cell Ranges?

Jan 28, 2014

following code so as to have certain cell ranges protected and unprotected USING COMMANDBUTTON/PASSWORD

Code:
Private Sub CommandButton1_Click()
Dim Password As String
JamesPassword = "MyPassword" '

[Code]......

View 8 Replies View Related

Unprotect Cell Based On Username Match

Dec 14, 2006

Sheets("sheet1").Range("a1").Value = Environ("username")

If Range("a1").Value = Range("H5").Value Then format.cell("c24")Protection=unlocked

I will have someone type in their supervisor's name in H5. When they send the form to their supervisor and A1 matches the entry in H5, I want cell c24 to be unlocked so the supervisor can make an entry.

View 9 Replies View Related

Protect Or Unprotect Cells Based On Cell Input

Nov 21, 2006

I am working on my spreadsheet and was wondering if anyone knows if cells can be Protected or Unprotected based on the value of another cell?

Here is what I am looking for:

If cell "A16" on the "Input Sheet" equals AR then Protect Cells J16, K16, L16, M16, N16, and O16

If the value of A16 on the Input Sheet DOES NOT equal AR then leave those cells unprotected.

Has anyone ever done anything like this? On my spreadsheet it would allow the user to skip the unnecessary cells as opposed to having to Tab through them. Not a big deal but definitely would be a time saver and a lot more user friendly.

If you have any questions or concerns just let me know. I will wait to see what the Excel Guru's come up with.

View 9 Replies View Related

Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)

Jan 7, 2009

If I have, in one cell (call it D1):

EH,DR,HU

and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...

eg...

F,G,H

View 9 Replies View Related

Multiple Row, Single Column Cell Blocks Into Single Row, Multiple Column Format

Mar 21, 2008

I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...

View 9 Replies View Related

Unprotect The Sheet..

Feb 24, 2009

Spreadsheet was created by me and worksheets locked by me with password. Data is maintained by another on the network. Since acquiring a new PC with xl 2003 replacing 2000, none of the worksheets will allow me to unprotect. The "unprotect" menu option is not available (shaded out), and it says "shared" at the top.

I have numerous spreadsheets that work this way, but this is the only one that has this problem. How can I unprotect it to make modifications?

View 2 Replies View Related

Spinbutton Unprotect...

Dec 25, 2008

When I protect the worksheet with the spinbutton, the spinbutton is no longer accessible. How can I modify the code below (provided by Leith Ross) to make the spinbutton accessible in a protected sheet?

View 3 Replies View Related

Unprotect All Sheets

Nov 23, 2006

I need a macro code to unprotect all sheets in a book. The sheets may range from 3 to 20 sheets, with a common password. I want to keep this as contol. Only one person will be using this.

View 9 Replies View Related

Unprotect The Cells

Oct 16, 2009

does anyone know if I can do something like this:

Sub warehouse()

Dim Flag As Boolean

If .user = "martin.lucas" then _
Range("H:H").unprotect
Range("K:K").unprotect
Range("N:N").unprotect
Set Flag = True
End if

If Flag = False then _
Msgbox("you are not permittedd to do this. See Martin Lucas")
Set Flag = False

End sub.....

View 9 Replies View Related

Unprotect Failing

Oct 11, 2006

I have a workbook that has several macros. When the workbook is saved, it cycles through and kicks off several of them.

This works fine, no errors. I have another macro that saves and then emails the workbook. When this macro runs, the same set of macros is launched due to the save method however it errors during one of the macros.

Basically what's happening is that the one macro (derivecountry) unprotects a sheet and inputs a data value. This macro works fine when run independantly but when it runs as a result of the email macr, the sheet does not get unprotected and the error results.

Here is the code line that runs to unprotect but doesn't always work

ThisWorkbook.Sheets(strShtName).Activate
ActiveSheet.Unprotect (strPassword)

I've also tried

ActiveSheet.Unprotect password:=strPassword

View 4 Replies View Related

Unprotect Sheet

May 3, 2007

I'm working with this workbook and suddenly the worksheets protected! Although I didn't make any password. I checked it but, I coulden't find any password. see code maybe there is a mistake.

View 7 Replies View Related

Protect / Unprotect Sheet

Jan 23, 2013

I am using the following code to protect/unprotect a sheet in a tool that i am working on, which seems to work great. It unprotects the sheet runs the code inbetween the two liens of code and then protects the sheet again.

VB:
ActiveSheet.Unprotect Password = "mypassword"
ActiveSheet.Protect Password = "mypassword"

The issue I am having is if i want to go in and edit the sheet without running the code it will not let me. When I go in and type the password to unprotect it says it is invalid. I have typed it exactly how the code reads. I have even tried including the quotes but no luck.

View 4 Replies View Related

Macro To Unprotect A Spreadsheet?

Jun 16, 2014

We have a macro that opens a spreadsheet and pastes data from another sheet into it.

Is there a macro statement that will unprotect the spreadsheet if it is saved with protection (it is not password protected)?

View 1 Replies View Related

Unprotect Macro Not Working

Feb 2, 2014

It gives me a error and says my password is wrong. I have user names set to return the password so that it is unlocked for certain people. It works for unprotecting the sheets. I modified it and added it back in to try to do it for the workbook.

[Code] .........

I realize that they " and () around the password are differant, but that's how I last left them. I have tried several combinations and it just gives me more errors saying expected end of statement, etc.

View 8 Replies View Related

Unprotect Sheets In New Workbook

May 5, 2014

I have a code that is supposed to go through a folder, open the xls file, replace all of the formulas with values only, then save as a xlsx file to get rid of the macros, however when we get into the next workbook it will not unprotect the sheet. instead it unprotects the origional sheet I sweat this worked last week when i was writing the sections and testing but maybe it didnt. The problem is without unprotecting the sheet i can not paste to it

Workbook 1 opens up, find that the date is expired, then goes through the folder and opens a workbook, is then supposed to unprotect the sheets ( this is where the problem comes in) save all the formulas as values and then as XLS in another folder.

I turned the text red in the offending area, everythign up to this point seems to work fine.

Private Sub Workbook_Open()
Dim printquotes As String
Dim OrigDate As Date
letmesave = True
OrigDate = ActiveSheet.Range("n2").Value

[Code] ......

View 3 Replies View Related

Blocking Unprotect Popup

Mar 20, 2007

I have a protected worksheet showing results of users maths tests. When users try to add data to this worksheet, a Windows Office window appears, advising it is a protected sheet, then gives instructions on how to Unprotect it.

I do not want users unprotecting this particular worksheet.

View 9 Replies View Related

Protect - Unprotect The Sheet

Jan 2, 2009

I have a worksheet where the users need to enter time. I have a macro that captures the time when the user hits Ctrl + t

What I want now is a code which protects the cell after entering the time with Ctrl + t.

I tried to do it, but my macro only works when I don't set any passwords to protect the sheet. How can I pass the password to the code and protect the sheet after the code to capture the time is being executed.

View 14 Replies View Related

Automatic Unprotect Using SaveAs

Sep 3, 2009

way for other staff to do the following...

Open an exiting protected spreadsheet,
SaveAs [newfilename]
Unprotect new sheet
Edit new sheet
Protect new sheet
Save it.

Hence we how have two protected sheets with different filenames and different contents.

Then, next time, the teacher will start again with the new spreadsheet file resulting in yet another version of the same data. etc etc.

View 9 Replies View Related

Asking To Unprotect An Unprotected Worksheet

Oct 9, 2009

I am working on a worksheet that has been protected off and on. I unprotected this AM, but XL will not allow changes and instructs to unprotect the WS, but it is already unprotected. The WB is not protected and never was. I am at a loss as to how to work around this problem.

View 9 Replies View Related

Use Network Id To Unprotect Sheet

Jun 1, 2006

We have created a spreadsheet that needs to be protected as it is viewed by several users. We would like, if possible, to update this daily with as little human intervention as possible.

Can we use VBE and the on worksheet open event to unlock the password protection, update the sheet and then lock it again? I know that you can read who opens the sheet using code similar to the following but don't know whether the password protection can be "unlocked" utilising this method.

View 6 Replies View Related

Unprotect VBA Project When Shared

Nov 9, 2006

I have an educational workbook which I am not familiar with. I cannot open vb project in vbe. When I try to open it says "project is unviewable". As far as I am concerned it should ask a password. But it doesn't. Can somebody tell me why. I am not asking somebody to crack it for me. I just want to learn why vbe doesn't ask a password. I think the only possibility is the creator of the program want nobody to open the code forever.

View 4 Replies View Related

Unprotect Sheets On Saving

Dec 20, 2007

I need to be able to protect the sheets in this workbook but then I need them to be unprotected when I click the Save File As button and then have them saved again whenever the file is opened back up.

View 9 Replies View Related

Excel 2010 :: Return Range Of Cell Concatenated Based On Single Cell Value

Jul 2, 2012

I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:

Number of variable entered: 5

q9001
q9002
q9003
q9004
q9005
q9006
etc.

Should give me: "q9001 q9002 q9003 q9004 q9005"

I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.

(Using Excel 2010)

View 7 Replies View Related

Find Multiple Instances Of Single Criterion In Row & Return To A Single Col

May 8, 2006

Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.

I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.

I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.

NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.

Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0

Scenario:
Looking for Numeric Criterion 1 (one).

Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1

In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.

View 14 Replies View Related

Paste Single Cell From External Source To Merged Cell In Excel?

Dec 2, 2013

I Want users to be able to copy a reference number from a an external program into excel, this works well for single cells but the cell I want them to be able to paste the information into is a merged cell and keep getting the "Data on the clipboard is not the same size and shape blah blah error". What I can do to get this information to paste? Is there some VBA that can kinda trick the cell to believe its single or when pasting it unmerges and then remerges again?

View 1 Replies View Related

VBA Loop - Remove Single Character From Cell String Then Calculate New Value In Cell

Mar 1, 2012

I have a string of text in cell A2. In cell B2 of my spreadsheet is a formula that calculates a number based on the text string in cell A2.

I want to write a VBA loop that removes a single character from the cell A2 string, then calculate the new value in cell B2. I want this loop to continue until the value in B2 falls below a set value (in this case 60).

My code so far
Sub trim_text()
Dim mytext As String
Dim myanswer As Integer
mytext = Range("A2")
myanswer = Range("B2")
Do While myanswer > 60
mytext = (Right(mytext, Len(mytext) - 1))
Loop
End Sub

This obviously does not work. In my excel table I have a formula in cell B2 to calculate "myanswer" will this work, or does that code have to be placed into the VBA code?

View 3 Replies View Related

Finding Single Name In Cell In Cell With Multiple Names (words)

May 8, 2014

Looking for a formula (not macro) that can do the following:

I have a spreadsheet and in one column, it has last names of clients. In the next column, it has full names of clients that are the same account, but different system with different descriptions for the name. I need to lookup the column that just has last name and see if that last name exist anywhere in the "Full name" column. If it does, then I need column C to say "Yes" or something along those lines.

The "full name" column isn't always in the same format and sometimes doesn't even contain the "last name." Sometimes the last name will be first and sometimes there's a comma and sometimes there isn't a comma. The spreadsheet attached shows a good short example of what I'm working with...

Name Spreadsheet Ex.xlsx

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved