PDA

View Full Version : Solved: Change Link Source in a protected sheet



dmkitz
04-11-2007, 11:38 AM
Excel 2000. I have a worksheet that must be password protected. It contains many macros and I've used the UserInterFaceOnly argument (set to true) of the Protect method to allow the user to run the macros.

One hurdle I don't know how to get past is this: The workbook is linked to another Excel workbook and the user must be able to change the Link Source. (This is how many of the cells are populated.) I can create a macro to open the Links dialog box but the "Change Source" option is grayed out because the sheet is protected.

If I build Sheet Unprotect into the macro, then when the user finishes changing the Link Source, the sheet will be left unprotected. How can I accomplish this?

Thanks.

geekgirlau
04-16-2007, 03:10 AM
You should be able to create a macro to do this:

Use Application.GetOpenFilename to get the user to nominate the new link source - store this value in a variable
Remove the protection
Change the link source
Reprotect the sheet

dmkitz
04-16-2007, 09:59 AM
I'm sorry, but I can't figure out the code. Thanks.

geekgirlau
04-18-2007, 12:31 AM
Sub ChangeLinkSource()
Dim strSource As String


strSource = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If strSource <> False Then
Sheets("mysheet").Unprotect
ActiveWorkbook.ChangeLink Name:="OldLink.xls", NewName:=strSource, _
Type:=xlExcelLinks
Sheets("mysheet").Protect
End If
End Sub

dmkitz
05-02-2007, 02:10 PM
Thanks, geekgirlau, I'm just getting back to this project. When I ran the sub, I got a Run-time error 13 -- type mismatch at this line:
If strSource <> False Then
Please advise. Thanks.

geekgirlau
05-03-2007, 04:57 PM
Sorry - should be a variant data type

dmkitz
05-04-2007, 07:40 AM
No problem. We got past the data type, but now when I run the code, I get a run time error 1004: Method 'ChangeLink' of object '_Workbook' failed, at this line:
ActiveWorkbook.ChangeLink Name:="OldLink.xls", NewName:=strSource, Type:=xlLinkTypeExcelLinks

Could that solution be as simple? I fear not.

geekgirlau
05-06-2007, 10:19 PM
"OldLink.xls" needs to be replaced by the name of the linked workbook prior to the change.

A really simple method of checking your syntax for this is to start recording a macro and change the link manually. This will give you the correct value for the existing linked workbook, and you use "strSource" for the new name as in the example above.

dmkitz
05-07-2007, 06:37 AM
I don't think this will work then because I can't anticipate what the users will have named their workbooks. Can the macro return the value of the current link and use that?

geekgirlau
05-07-2007, 06:21 PM
Sub ChangeLinkSource()
Dim varSource As Variant
Dim varLinks() As Variant
Dim i As Integer


varLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(varLinks) Then
Sheets("mysheet").Unprotect

For i = 1 To UBound(varLinks)
varSource = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

If varSource <> False Then
ActiveWorkbook.ChangeLink _
Name:=varLinks(i), NewName:=varSource, _
Type:=xlExcelLinks
End If
Next i

Sheets("mysheet").Protect
End If
End Sub

dmkitz
05-10-2007, 12:01 PM
When I get to the "ActiveWorkbook.ChangeLink" line, I get an error saying the cell or chart I'm trying to change is protected and therefore read-only.

I've tried each of the following three lines of code to unprotect (placed after the "If Not" line) but they haven't worked:
ActiveSheet.Unprotect password:="dci"
Sheets("mes").Unprotect password:="dci"
ActiveWorkbook.Unprotect password:="dci"

Thanks.

geekgirlau
05-10-2007, 05:55 PM
Have you tried just the unprotect instruction on it's own? Did it work? To state the obvious, is the password correct (remember it's case-sensitive).

dmkitz
05-11-2007, 07:48 AM
The unprotect instruction on its own brought up a prompt asking for the password. The password in my code is correct.

geekgirlau
05-13-2007, 06:21 PM
Sorry, I didn't make this clear. If you run only the following line of code, what happens?


Sheets("mes").Unprotect password:="dci"

dmkitz
05-16-2007, 07:23 AM
Sorry, I didn't understand that at first. Yes, that code, by itself, does run properly and does unprotect the sheet. Thanks.

geekgirlau
05-17-2007, 05:44 PM
Can you set a breakpoint on the red line below:


Sub ChangeLinkSource()
Dim varSource As Variant
Dim varLinks() As Variant
Dim i As Integer


varLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(varLinks) Then
Sheets("mes").Unprotect Password:="dci"

For i = 1 To UBound(varLinks)
varSource = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

If varSource <> False Then
ActiveWorkbook.ChangeLink _
Name:=varLinks(i), NewName:=varSource, _
Type:=xlExcelLinks
End If
Next i

Sheets("mes").Protect Password:="dci"
End If
End Sub





... and then step through the code one line at a time using [F8]. What happens? Is the sheet unprotected after the unprotect command? Have you tested with the new link source both open and closed?

dmkitz
05-22-2007, 08:21 AM
Thanks for all your help! I don't know why there was a problem, but the line of code in red above is now unlocking the sheet and the macro is working as intended! :clap2:

geekgirlau
05-24-2007, 01:05 AM
That's great - don't forget to mark this thread as "solved" using the Thread Tools at the top of the screen.