Protected Cells Jump To Hyperlink
Aug 23, 2006
I have a protected worksheet with about a dozen unprotected cells (so the user can just answer question from a validated list). Also unprotected is a hyperlink back to the main menu (Sheet1).
My problem is when a user clicks outside of where they should (i.e. on a protected cell) - it executes the hyperlink back to Sheet1.
I have tried unprotecting cells around the hyperlink. I even tried putting "dummy" hyperlinks around the main one referring it back to the same sheet.
View 7 Replies
ADVERTISEMENT
Jan 21, 2014
I have a large spreadsheet with a long date range from A:3 to A:900
what would be the best way to jump to today's date? I have seen formulas, Macro's, Active X controls, Hyperlinks?
View 9 Replies
View Related
Jun 1, 2009
I have a excel worksheet with hyperlink. If the sheet is protected I am unable to use the hyperlink even though I have unblocked the protected cells.
View 9 Replies
View Related
Jan 21, 2008
Does anyone know if/ how i might unlock hyperlinks on my protected spreadsheet without having to unlock the cells they are placed in?
View 3 Replies
View Related
Oct 24, 2012
Excel 07
I inserted a hyperlink into a cell that goes to a web page. It works when the sheet is NOT protected, as soon as I protect the sheet it stops working. How do can I lock the workbook and that cell so nobody can change it but the hyperlink still work?
View 2 Replies
View Related
May 17, 2007
I have designed a form in excel so that it can be filled up and printed out. Have attached a sample sheet with a few fields for more detail.
Is there a possibility that when I enter a character in a cell, it automatically jumps to the next pre-defined cell (because not all cells have to be jumped immediately to the next one). Right now I am using protected/unprotected cells so that I can either use Enter or Tab to jump. If there is a macro that can do the trick, entering data would be a lot more faster and easier.
For ex, I can just type out MIRACLEZ, and the macro helps me jump cell to cell as I type.
View 3 Replies
View Related
May 23, 2014
I have a sheet with various pivot tables and charts, I want to have a row of buttons at the top to jump to the corresponding cell range.
View 1 Replies
View Related
Mar 29, 2014
Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies
View Related
Oct 20, 2008
I've created a spreadsheet that I want to offer for sale.
I've protected all cells with formula and those not needed to use the program. The only cells that can be selected/changed are those that need the values for the calculations.
How do I protect the sheet from being saved/stolen when trialed by possible customers?
View 5 Replies
View Related
Oct 31, 2009
I am trying to hyperlink all Worksheet2 A cells to worksheet1 A cells according to their row number. I have tried to create a Hyperlink in A1 and drag it down but what happens is that hyperlink links to same cell adn doesnt change cell number whilst dragging.
View 4 Replies
View Related
Feb 6, 2014
I made a sheet to control some reports, and it works like this: you click a button ("Insert"), it opens a userform, you type everything there (date, serial number, client, city) and it place that info on the first row available. Then there's a "Clear Last Entry" button, and that's it.
I would like to protect the whole sheet and only allow users to insert new entries via the "Insert" button. How can I do this? If I protect normally Excel won't allow the VBA code to write on cells.
View 4 Replies
View Related
Jan 28, 2008
The Special GoTo function doesn't have a listing for highlighting (going to) only protected or (preferribly) unprotected cells. Is there an easy way to do this?
View 9 Replies
View Related
Sep 7, 2013
I have a macro that turns off filtering option when it runs. What I want is for to first warn the user that rows have been filtered and the Macro will not run, then once the filters are switch off, the macro will run. It does the last bit at the moment but switches off the filtering of protected cells and once the macro has run the sheet can no longer be filtered .
Sub NewRowSQT()
Dim ans As String
ans = MsgBox("Ensure you have selected a cell in column B immediately below the section you wish to add to or the Macro will FAIL!", vbYesNo)
[Code]....
View 1 Replies
View Related
Feb 24, 2009
Excel 2007 or 2003 Is there code to be able choose the formating of a cell when right clicked if the cell is Unprotected. But, the sheet is Protect and the Workbook is Protected. Without Unprotecting anything 1st.
The reason being is sometimes the user may need to change the type of font from Arial, or Gdt or Y14.5M.
View 9 Replies
View Related
Mar 5, 2010
I am trying to change the color in Range("A1:bb5000") for each worksheet unless it is protected.
Here is my code
Sub Color_Sheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1:bb5000").Select
If Item.Locked = False Then
With Selection.Interior
View 9 Replies
View Related
Oct 7, 2007
How can i set up my spreadsheet so that all protected cells are a different colout to the main sheeT.
I would like this colouring to appear in the sheet when in protected and unprotected mode
View 4 Replies
View Related
Oct 23, 2007
Well I am back to the colour coding of protected cells
I am using excell 2003
Many cells in the worksheet are locked
My problem is that when I am debugging I forget to turn sheet protection on
when I am finished And then users can overtype the cell formulae
How can I set excel up so that when the sheet is protected all the Locked cells have a green background , but when the protection on the worksheet is turned off
then the locked cells must be a red
View 4 Replies
View Related
Mar 28, 2014
I have a code which deletes all contents of cell in range except formulas, from sheet 2 to 13. Code works, but I have included toggle button on each sheet to lock/unlock sheets.
If I lock sheets, code doesn't erase cells as needed.
I have figured out code that works for me, but looks like It's not performing quite correct. Although code executes quite quick...
This is my current code :
[Code] .....
How could I rearrange this code to work in proper order ?
View 4 Replies
View Related
Nov 9, 2008
Is there any way to allow autofiltering and sorting even with locked cells within the range to be sorted? I've conditionally locked some cells based on user input, and at the end of my vb code I have allowsorting and allowfiltering. But I want to sort cells that are locked even after that. Does anyone know if that's even possible with VB?
View 4 Replies
View Related
Dec 6, 2007
I have a workbook with a series of sheets that have tables for entering data. The table row and column labels and formulas in certain cells of each sheet are locked and some sheets are hidden.
When opening the file I want to clear all cell contents (interior color, comments, data, etc) in all the unlocked cells on each sheet that is not hidden.
I tried protecting the sheets first so only the unlocked cells would be accessible, but when I run the code below I get an error saying that the clear contents etc. cannot be performed because the sheet is protected. If it is not protected, everything gets wiped out.
How can I keep the locked stuff but clear the unlocked cells?
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
ws.Protect Password:="aaa" 'Protect each sheet
ws.Cells.ClearContents 'clear content of any unprotected cells
ws.Cells.ClearComments 'clear any cell comments
ws.Cells.Interior.ColorIndex = 0 'set background colour to no fill
Active.Cells.Range ("a1") 'make the active cell the top left
End If
Next ws
View 9 Replies
View Related
Sep 25, 2009
I'm using Microsoft Excel 2008 for Mac version 12.2.0 (090605) (Mac OS 10.5).
I created a sheet, selected some cells to be locked and others to be unlocked (using Format > Cells), then I used Tools > Protection > Protect Sheet (choosing Contents only, not Objects or Scenarios). This has the desired effect of protecting the locked cells from users accidentally typing changes and overwriting their current contents.
I can still enter new data in the unlocked cells as expected, but unexpectedly I cannot change the formatting of unlocked cells (e.g., shading, font - bold). These are *unlocked* cells so even if the sheet is protected, why can't I change the formatting for them? It is too tedious to Unprotect the sheet every time I need to change formatting of an unlocked cell.
I have zero experience with Visual Basic and can't even find options that others have described (e.g., right clicking the sheet name does not yield "Show code" and Tools > Macros does not yield anything comprehensible about VBA). So if your suggestions involve something complex, please do include detailed instructions!
View 4 Replies
View Related
Apr 25, 2006
I'm trying to create a worksheet with only one unprotected cell (simple enough!) and I want to base calculations in other protected cells on a running total of how many times a value has been entered in my unprotected cell. Re-entering the same value would need to be counted.
First enhancement would then be a means of resetting the counter.
Second enhancement would be to check for a valid entry and only then increment the counter.
View 4 Replies
View Related
Aug 15, 2007
I know that there is a lot on locking cells but it is confusing to me. I am able to lock a worksheet but that is not what I need. I have an Excel document that has thirty to thiry-six names on it. I do not want anyone able to edit the names. But I do want them to be able to add a name.
Example:
There are 30 students in the classroom. A new student comes in. I want the teacher to be able to add that student to the roster but not able to edit the names above. What do I need to do?
View 2 Replies
View Related
Jun 19, 2006
So i have sheet 1 with some cells locked and protected (but open to copying) as a template and sheet two will be where people paste one or more of the template formula on an ongoing basis. How do i mantain the locked and protected cells after they have been pasted? I have unlocked sheet two pasted the first template and had to choose protect cells after pasting, and further pasting of locked cells are not protected.
View 1 Replies
View Related
Mar 14, 2014
The text disappears when I want to correct it (protected sheet). see the discription of the problem in the attachment with figures.
View 3 Replies
View Related
Feb 17, 2009
Below is the code that I copied from another thread. But what I need is to enable this command when the sheet is protected.
View 9 Replies
View Related
Jan 20, 2003
I understand that the wrap text feature doesn't work if you are merging cells.
I have a document that users will be able to input certain cells but not others...the document needs to be protected so they can't change formulas etc.
View 9 Replies
View Related
Mar 29, 2012
Here is an excerpt of my code
Code:
Else
Sheet8.Unprotect Password:="rm123abc"
Sheet8.Range("V" & rw) = "Item is not found in the Data Base. Please Contact DME for all documentation requirements for this item"
Sheet8.Protect Password:="rm123abc", AllowFiltering:=True, AllowInsertingRows:=True, AllowDeletingRows:=True
End If
Unfortunately, once the code has run and has finished re-protecting my sheet, it doesn't allow the user to select locked cells. These locked cells contain hyperlinks, and they need to click on these hyperlinks to navigate through my workbook.
So I have two questions:
1. How can I enable allow user to select locked cells
2. Do I have to allow the user to select locked cells to access my hyperlinks, or is there another option?
View 2 Replies
View Related
Dec 31, 2013
I have this excel sheet I'm trying to create (to make my life better at work). It's to track credit card refunds. It'll be saved on sharepoint. So my department will fill in the data on the "Ops" side, and then the accounts department will fill in their side. It's very rudimentary, but anything is better than the old, tattered BOOK we are currently using.
So far, I have the user/date/time stamps autofill, when the user fills in "Entered by" or "Recieved by". I have those cells locked so no one can edit it. I have separte passwords for both sides (Ops and Accounts).
What I want now is to be able to:
1. Lock each line once it's been entered and saved. Say my department fills in one entry. We save it. After that, it CANNOT be edited. If there are any changes, I'll put in a remarks column so people can enter stuff.
2. If someone enters the first two columns (Guest name, booking number), then the "Entered by" HAS to be filled, or the workbook cannot be saved.
3. ONLY letters can be used for Guest Name and Entered by. I've tried different things for this. While I've managed to make it work if the user enters a digit first, I can still enter John56, and it'll pass. I need it to only allow letters (and spaces).
4. The "Booking Number" should only allow six digits. No less no more than six digits. It can be 000001 even. But it has to have six. And, obviously, no letters, no spaces.
In the "ThisWorkbook" module, I've got this code:
Code:
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="hurlEy!", _
UserInterFaceOnly:=True
Next wSheet
End Sub
In the sheet1 module, I've got this code:
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 3 Then
With Target
If .Value "" Then
.Offset(, 1).Value = Environ("username")
[code]....
View 1 Replies
View Related
Apr 11, 2005
I need to protect my worksheet and only allow users to edit certain ranges. I am allowing the use of autofilter which I can select when I apply protection. However, I am now aware I cannot sort protected cells..which is essential. I basically need to be able to sort a column titled 'surname'. The worksheet is a record of pupils attainment in my class.
Now, I have a macro which seems to do the job: ....
View 9 Replies
View Related
