Locking A Worksheet But Allowing Hyperlinks?

Mar 15, 2007

I have a problem where a client is asking to have an Excel Spreadsheet completely secured. In itself this is a relatively painless exercise, but the problem lies in the fact that the workbooks have a lot of cross-sheet navigation by way of internal Hyperlinks.

My first thought was to unlock the cells that contain the the Hyperlinks and then allow selecting unlocked cells in the sheet protection properties, but this has an adverse affect in that it means clicking anywhere on the page will execute the hyperlink as if you had clicked on it directly. This isn't going to be sufficient in this case.

The Hyperlinks are simply added to the cell itself, but there are a lot of them and this template may be processed and updated up to 1000 times a month, so I need something that can handle being automated. My only other thought is to hand the document over to Acrobat and PDF it to allow security, but automating PDF creation is testy at the best of times.

View 2 Replies


ADVERTISEMENT

Adding Worksheet Protection But Allowing Form Controls - Macros

Jun 5, 2014

I have a worksheet with lots of things going on... the end user needs access to the Name field, DOB field, all the scroll and form control option buttons in the 9-question sheet, and the macro-enabled reset button at the bottom. Formulas are sprinkled throughout the worksheet, in columns T through AC... option button links go to AA and AB.

I would like to protect the entire sheet so none of the fields, except where indicated above, could be selected or edited... but when I protect the sheet, I get errors when trying to use you form controls, and the formulas do not respond. And finally, when protected, I get a debug error on the reset macro.

View 2 Replies View Related

Locking Different Cell Ranges Then Locking Whole Sheet With Macros

Jan 4, 2010

I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.

The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.

What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.

I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.

I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....

If anyone has any suggestions I would be eternaly greatful.

Passwords on the sheet for the different buttons are abc or cobra.

You can download my Spreadsheet here.

[url]

View 9 Replies View Related

Anchoring Pivot Chart And Slicer Without Locking Entire Worksheet?

Jul 19, 2012

I am trying to create a "dashboard" style report using a pivot table and pivot chart with slicers. All of these objects (table, chart and slicers) are in the same worksheet. However, the users of the report (read executive management) are not very Excel savvy and I want to protect the format of the report. So, I want to protect the sheet and only allow access to the Pivot table options. However, in order to make the slicers usable, it appears that I have to allow them to edit objects. This unfortunately means that they can then move those objects (the Slicers and Chart).

I am looking for a way to lock the slicers and pivot chart in position. I have already accounted for column width changes created by modification of the pivot table parameters by selecting the "Don't move or size with cells" option under PivotChart Tools > Size > Format Chart Area > Properties > Object positioning. This is the default/locked-out setting for the slicers.

View 7 Replies View Related

Macro Code To Remove All The Hyperlinks From A Worksheet.

Apr 2, 2009

What I’m after is the macro code to remove all the Hyperlinks from the data in the cells in a worksheet.

View 2 Replies View Related

Copy Worksheet And Maintain Relative Hyperlinks

Nov 4, 2008

I'd like to be able to create a copy of a worksheet and maintain the relative hyperlinks within each worksheet. Currently, when I copy a worksheet, the hyperlink takes me back to the original worksheet.

I'm using Excel 2003.

View 9 Replies View Related

Macro To Create Index With Hyperlinks To Worksheet Headers

Apr 9, 2014

Can't seem to attach sample Widgets.xls. I need a macro to take the heading (note heading not sheet name) from each worksheet on a workbook (or from a range of nominated worksheets in case I don't want to index the first or last few sheets) and use it as the Description for a Index entry on a nominated sheet e.g. Sheet named 'Index'. In addition that each Description is also a hyperlink back to the sheet with the heading. In this way users can click between each entry on the index to go to the sheet and then click on the heading on the sheet to go back to the index.

Index Sheet

M21, M22 and M22-A BICYCLES1
Q21, Q22 and Q22-A BICYCLES2
R21, R22 and R22-A BICYCLES3

Taking this one step further I ideally want to save this workbook as a pdf and the hyperlinks to remain.

View 4 Replies View Related

Get The Index Worksheet To Extract Data From The Sheets That It Has Indexed: Hyperlinks

Oct 30, 2006

I am now trying to get the Index worksheet to extract data from the sheets that it has indexed, for example, it returns the sheet name, but i want it also to return the value that is contained in C2 and C3. Is this possible? I have attached the code which i am using below for ease of reference.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index...................................

View 2 Replies View Related

Locking One Cell If Another Had A Value In It (conditional Locking)

Oct 10, 2008

I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.

View 9 Replies View Related

Explicit Hyperlinks, Relative Hyperlinks

Feb 21, 2007

I have a spreadsheet and within the first sheet there are lots of hyperlinks to other cells within sheet 1 (my template).

I have made a small macro which very basically makes a copy of sheet 1 (my template)

The hyperlinks in the template are explicit and as they are copied to sheet two, they still reference back to cells in sheet 1. I need the hyperlinks to be relative, so that when I make a copy of the template the hyperlinks are copied and make reference to cells in the new sheet. I cannot work out how this should be done

View 6 Replies View Related

Calculate End Dates Allowing For Weekends

Feb 9, 2009

If I have the start date (say February 01, 2009) and the job will take 100 working days, when will it finish if there is no work done over weekends? I assume you can use networkdays somehow but the final logic escapes me

View 2 Replies View Related

Allowing VBA To Edit Protected Cells

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

Ambiguous Name In VBA Is Not Allowing To Open File?

Mar 19, 2014

During a copy and paste I forgot to change the name of a macro in a module which had many macros. Now when I want to open that file , it hangs and does not open. how to open the file to change the name of the macro and re-save it ?

View 5 Replies View Related

Allowing User Defined Range

Feb 13, 2009

I would like to alter this code so that the user chooses the column where the numbers are. They aren't always in column D....

View 9 Replies View Related

Protect Some Cells While Allowing Entry In Others

Aug 9, 2008

Iam making some test report tamplet`s, in that when I select the material from drop down (ref enclosed xls file) all spec will be displayed. ·I want the sheet to be protected at the same time few cells to be allowed for data entry (cells marked with blue)· If sheet can`t be protected I want to hide the formulas and spec details used in it.

View 2 Replies View Related

Allowing VBA Forms To Accept Alpha Numeric?

Dec 18, 2012

My VBA form for excel currently only accepts numeric. I want two of its fields to accept alphanumerics. Those two textboxe fields are cboIns and TextCmt in the code below.

Code for the form is given below.

VB:
Dim dic As ObjectDim Ctrls
Dim myTBoxes() As clsTxtBoxes

[Code].....

View 6 Replies View Related

Leaving The Cell Blank But Allowing The Macro To Run

Mar 15, 2007

How can I have my macro run in its cell and still allow the cell to be blank? Say I have a number, 0.99, in the cell and I want to delete it and leave it blank. I can either push "Backspace" or the delete key and it still leaves zeros.

If I push the space bar it stops the macro (which I do not want to happen) and leaves the cell blank because I added a space.

View 12 Replies View Related

Div/o Error Message Not Allowing To Calculate The Other Sheet

Jan 18, 2009

When you have 0 divided by 0 if there a way to show 0 instead of the div/o error message. The reason I want to know if that I have a spreadsheet for peoples efficiency during the week which feeds into another sheet to provide a monthly/yearly totals and who is the best and worst, but if there on holiday for a week or two the feed does not work as the error message does not allow the other sheet to calculate.

View 7 Replies View Related

Lock In A Title On Column Allowing Sorting

Jan 29, 2009

I am trying to create titles for columns. I can do that by just creating them in the cells above the data. The problem is that the titles sort into the data using the sorting feature. When I lock any cells and protect them, the sorting is locked out. Can I use some sort of header over the columns that remains in place where I can still sort the data below, by clicking on the column and sorting?

View 3 Replies View Related

Set 2 Cells Equal, While Allowing Either One To Be Manually Changed

Jun 9, 2009

Is there a way to set 2 cells equal, while allowing either one to be manually changed? So if I put in value of 10 for Cell1, Cell1 and and Cell2 will both be equal to 10. Then if I change Cell2 to a value of 20, both cells will be equal to 20.

View 5 Replies View Related

Visual Basic - Not Allowing Cell Entry

Aug 20, 2008

I have a cell that I do no want anyone to enter any information into unless a previous cell has "U" inside of it.

Okay so so I have a column of cells named "Fringe Benefit Type" you can choose different letters to corropand to different types. Now I have set the letter "U" to be the one I want and that works. Now the next cell is called "Hire Season". Now (stay with me here) IF "Fringe Benefit Type" is equal to "U", I don't want the user to be able to enter anything into "Hire Season".

I am using the visual basic thing, which already has some code, but I am not understanding it too well.

View 14 Replies View Related

Allowing Users To Select The Range For A Macro To Be Run On

Jan 12, 2009

i have a spreadsheet that data is imported to from a different program. however the amount of rows of data will change from day to day. I need to be able to select this range of data before i run a macro on it.

View 11 Replies View Related

Displaying A Form While Allowing Sheet To Be Editable

May 4, 2009

i want to do the following: initialize a sheet by showing form1, which accepts user input for rows and cols name a range on the sheet of size rows x cols display form2 which is just a "DONE" button that they click when they're finished pause/stop the macros and allow the user to fill in values for that range start up some other macros when they press the "DONE" button.

i tried it and found that when you display a form it locks the sheet (can't select the sheet to input values) and you can't even alt tab away from the form. is there a way around this?

View 2 Replies View Related

Allowing User To Select A Cell But Not Edit It?

Aug 25, 2009

I have an event handler that runs a procedure when a user double clicks a cell. This procedure modifies the contents of this cell and other cells. I want the user to be able to select the cell so they can double click it, but I don't want them to be able to single click it and modify it by hand (accidentally or not). Is there a way to lock/unlock the contents of the cell without locking the cell itself?

View 6 Replies View Related

Share With Multiple Users Allowing Macros

Oct 9, 2008

I have a workbook that I would like to share and allow multiple users to add and make changes at the same time. However, the workbook is full of macros that really need to run. Is this possible? The workbook is written in excel 2003.

If this is not possible then is it possible to share the workbook in a similar fashion but not allow any other users to make changes, but update their workbook with the changes that I make?

View 2 Replies View Related

Excel 2010 :: Allowing Edit On Specified Range

Jan 6, 2013

Run on Excel 2010

Sub UseChangePassword() Dim wksOne As Worksheet Set wksOne = Application.ActiveSheet
' Protect the worksheet. wksOne.Protect ' Establish a range that can allow edits ' on the protected worksheet.
wksOne.Protection.AllowEditRanges.Add _ Title:="Classified", _ Range:=Range("A1:A4"),
_ Password:="secret" MsgBox "Cells A1 to A4 can be edited on the protected worksheet."
' Change the password. wksOne.Protection.AllowEditRanges(1).ChangePassword
_ Password:="moresecret" MsgBox "The password for these cells has been changed." End Sub

The red color code is giving error message: Application defined or Object defined error.

View 1 Replies View Related

Data Validation Allowing Both Text And Numbers

Aug 28, 2007

to validate a cell for the following customer format, but cant get it to work:

XX00000

Where XX is any combination of letters (upper or lowercase) and
00000 is a combination of any 5 numbers.

It must always be in this format, with this number of digits in total (2 letters followed by 5 numbers)

View 9 Replies View Related

Only Allowing 1 Checkbox A Time To Be Selected On A Page

May 30, 2007

I have up to 6 checkboxes on a work sheet but I only want one of them to be allowed to be checked at any given time. Is there a way to make sure this happens? As an example if the first check box is marked and then the user checks Checkbox4 checkbox1 would automatically be unmarked.

View 6 Replies View Related

Outline Macro Not Allowing Filtering On A Protected Sheet?

May 5, 2014

I have the following macro so that we can use the outline feature without having to unprotect a sheet.

Private Sub Workbook_Open()
With Worksheets("Schedule")
.Protect Password:="APQP", Userinterfaceonly:=True[code]....

However once the macro is run, filtering will not work without unprotecting the sheet first. If you choose not to run the macro, filtering will work without unprotecting the sheet however the outline feature will not. what I need to add and where to make both the outline and filter features to work without having to unptotect the sheet?

View 1 Replies View Related

Allowing Users To View Multiple Sheets Once Signed In?

Feb 24, 2014

What i am trying to do: Create a login option to filter what worksheets each user can see. I need the users to be able to view 2 sheets not just 1. Also i need an admin login that can view all sheets. I want to keep the workbook protected so even though they can see and edit those two sheets they can only edit certain unlocked cells. Admin should be able to view/edit all cells on all sheets.

Below is my code which is made for a sample workbook. I was just trying to get the concept down and then i would convert it to the real workbook.

Form Code:

[Code].....

View 2 Replies View Related







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