How to open Excel sheet with full access in c#?

Excel - unusual double click behaviour

• Excel Version: 2003 Am quite happy to use VBA to help fix this problem if possible,  :-) I have a spreadsheet with about 100 sheets, about half of these I don't ever want the user to see, but I won't actually be hiding the sheets I don't want them to see, I will just be hiding the Sheet Tabs via Tools - Options, and then the user can navigate around the workbook with Hyperlinks to other sheets that I want them to access only. The sheets I don't want to have visibility of are where I will be dumping the RAW data so I can't hide them, because I still need daily access to these sheets. The other half are the sheets will collate this data and the user will interface with these sheets only. The problem I have is, I have produced a macro that protects ALL the sheets together and enables the auto filter too, this is to lock down each cell to the user. But when on one of the sheets that the user will see, if they double click on a cell (that is pointing to one of the data sheets via a formula (not one of the hyperlinks that I mentioned before)), it obviously comes up with the usual 'this cell is locked for editing.....' dialog box, that's fine, but after clicking OK on the dialog box, the cursor takes you directly to the exact cell it is referencing to, ON THE SHEET I DON'T WANT THEM TO SEE!!! I really would like to be able to have the cells visible to the users and for them to be able to double click where ever they want and for it NOT to take them to the other sheets that I don't want them to see. Is this possible?? I hope this makes sense, and I hope you can help me. Here is a copy of the macro I have used to protect/unprotect ALL sheets at once (just in case you need it or think it might be causing me problems): Sub Protect() Dim pw As String Dim Wks As Worksheet For Each Wks In ActiveWorkbook.Worksheets Wks.EnableSelection = xlNoRestrictions Wks.Protect Password:="abc", contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True Wks.EnableAutoFilter = True Wks.Protect contents:=True Next Wks Exit Sub End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim N As Single For N = 1 To Sheets.Count Sheets(N).Unprotect Password:="abc" Next N Application.ScreenUpdating = True End Sub Sorry, I spose I didn't make it clear, the sheet in question won't actually be 'hidden' as such, but I will be hiding sheet tabs via Tools - options. And I have made hyperlink buttons to enable the user to move around the workbook without actually having to click on the sheet tabs. And I don't want to actually hide the data sheets, because I will need them visible to me to enter the raw data. So, either way, from your reply, it sounds like to me that I can't stop it taking you to the referenced cell on another sheet after the double click unless I hide the sheet in question, is this correct, or do you know another way I can go around the problem??? Cheers, Peter. I have a spreadsheet with about 100 sheets, about half of these I don't ever want the user to see (it's where I will be dumping the RAW data), and the other half are the sheets that will collate this data and the user will interface with it. The problem I have is, I have produced a macro that protects ALL the sheets together and enables the autofilter too, this is to lock down each cell to the user. But when on one of the sheets that the user will see, if they double click on a cell (that is pointing to one of the data sheets), it obviously comes up with the usual 'this cell is locked for editing.....' dialog box, that's fine, but after clicking OK on the dialog box, the cursor takes you directly to the exact cell it is referencing to, ON THE SHEET I DON'T WANT THEM TO SEE!!! I hope this makes sense, and I hope you can help me. Here is a copy of the macro I have used to protect/unprotect ALL sheets at once: Sub Protect() Dim pw As String Dim Wks As Worksheet For Each Wks In ActiveWorkbook.Worksheets Wks.EnableSelection = xlNoRestrictions Wks.Protect Password:="abc", contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True Wks.EnableAutoFilter = True Wks.Protect contents:=True Next Wks Exit Sub End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim N As Single For N = 1 To Sheets.Count Sheets(N).Unprotect Password:="abc" Next N Application.ScreenUpdating = True End Sub

• Answer:

  Peter, I see two possible solutions. One would be to not allow the user to select locked cells. in Excel 2003, when you protect a sheet, the first two choices in the dialog are to allow the user to select unlocked and locked cells. don't allow them to select locked cells. You can record a macro when you do this to see the code setting - then adjust your macro. another approach is basically to kill the double click action. this can be done with workbook level code and will work on all sheets: Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Cancel = True End Sub this would go in the ThisWorkbook module the cancel = true cancels the normal behavior associated with the double click. Another think you can look at (unfortunately I don't have2003 handy so I can't test it) is the ability to edit formulas in cells. As I recall, if you allow this, thendoesn't move to the linked cell location when you double click on the cell - however, this might only be on unprotected sheets - so I don't know if that offers any help to you. (this setting should be in the edit tab of Tools=>Options). Beyond that, you could write code to that would be more controlling like using the Changeselection to try to control what the use does. Perhaps the simplest might be to have a macro that hides and unhides the sheet. You could hide all the sheets that need hiding with the macro (and save the workbook). You could have another macro that unhides them when you want to work on them. Then protect the structure of the workbook so the user can't unhide the sheets. that might be the most secure and easiest to manage. Also, when you hide sheet with code you can set them to xlVeryHidden which generally requires code to unhide them as long as the user doesn't go to the Visual basic editor. Those are the things I can think of. Hopefully one or more will work for you. True, unless you hide the sheet, it is going to take them there. But there is a bunch of ways you could use VBA code to hide the sheets only for certain people, but not for yourself. a userform with textboxes and command buttons (for password entry), code to recognize the username of the person whose computer is being used, etc. You are talking about 2 different things. Protection of sheets just prevents user from editing it. This is what you've done with the macro, you've protected the sheet. You haven't done anything to actually hide the sheet or hide the information, they can still go and look at it. And yes...when you double-click on a cell that hyperlinks (or even references, via formula), another cell, and the cell you're double-clicking is locked, then the normalbehavior is to go to the reference cell. you'll notice it even will open up another linkedfile, to complete that behavior, if necessary. I also think it's retarded, since nobody generally wants this to happen. do you really want to completely HIDE the sheets in question? In that case, then you need VBA code to actually hide the sheets in question. some example code you can put in the Workbook's Open event: dim mysheet as worksheet for each mysheet in thisworkbook.worksheets if mysheet.name<>"MainPage" Then 'change to suit mysheet.visible=xlveryhidden else mysheet.visible=true end if next mysheet