Solved

linking data in Excel from another Excel workbook

Posted on 2011-03-07
11
158 Views
Last Modified: 2012-05-11
Hi,
Is there a way of linking cell properties in excel.
I want to link PROTECTION from an excel document.
i.e.
on PARAMETERS.xls, if i have a cell 'Total Cost' which has been formatted and 'LOCKED'.
Then on BUDGET.xls, i have linked a cell to the above 'Total Cost'. I know it would copy the text, but i am wondering if it could also copy the PROTECTION too.
That way if i need to UNPROTECT that cell, i would only need to unprotect the PARAMETERS.xls which would unprotect the BUDGET.xls automatically.
The reason for this is because the BUDGET.xls will be used by 115 customers and contains 52 worksheets (1 for each week), which is almost 6000 cells in total .So instead of UNPROTECTING 6000 cells, i would only need to unprotect 1 cell.
Normally i would just create a macro and run the macro for all customers, but this still takes time to do.
Does anyone know if this is possible?
0
Comment
Question by:Chrisneilhopkins
11 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 35055335
Hello,

if you link to a cell in another workbook, you will only be able to retrieve the cell value. Any other properties like formatting, including the "locked" setting will not be carried over with a linked cell.

So, if you can do it with a macro, that's the way to go.

cheers, teylyn
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 35057896
When Parameters is updated   (unlocked, updated, and locked), the Budget values will be updated.
0
 

Author Comment

by:Chrisneilhopkins
ID: 35058586
Thanks, but its not the values being updated that i'm querying. Although i want the values to be updates, i would also want the option to unprotect and protect on mass. rather than do each of the customers spreadsheets individually.
I create new spreadsheets every financial year, and for the last 4 years i have had to go through each of the spreadsheets after the year had been started to make corrections to mistakes that i have made when creating them.
This year i have decided to make things a bit easier by creating a PARAMETERS sheet (which i can use for changing field names and values), but if i wanted to UNPROTECT or PROTECT a field on mass then that would save me a huge amount of time.
Hopefully i woill not have to use them, but i'm sure i will make a mistake somewhere along the line!
:) thanks
0
 
LVL 6

Expert Comment

by:royhsiao
ID: 35062241
here is a way to protect and unprotect worksheet in vba


Sub Protect_all_Worksheet()
Dim wksht As Worksheet
Dim w_i As Long
Dim wkshtnames()
w_i = 0

''count the worksheet
For Each wksht In ActiveWorkbook.Worksheets
    w_i = w_i + 1
    ReDim Preserve wkshtnames(1 To w_i)
    wkshtnames(w_i) = wksht.Name
Next wksht

''showt the sheet's name
    For w_i = LBound(wkshtnames) To UBound(wkshtnames)
          ''update the password in ""
        Sheets(wkshtnames(w_i)).Protect Password:=""
    Next w_i

End Sub

Sub unProtect_all_Worksheet()
Dim wksht As Worksheet
Dim w_i As Long
Dim wkshtnames()
w_i = 0

''count the worksheet
For Each wksht In ActiveWorkbook.Worksheets
    w_i = w_i + 1
    ReDim Preserve wkshtnames(1 To w_i)
    wkshtnames(w_i) = wksht.Name
Next wksht

''showt the sheet's name
    For w_i = LBound(wkshtnames) To UBound(wkshtnames)
        ''update the password in ""
        Sheets(wkshtnames(w_i)).Unprotect Password:=""
    Next w_i

End Sub

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 6

Expert Comment

by:royhsiao
ID: 35062651
Here is the way to protect or unprotect a range in all worksheet in vba
''//Created by RoyHsiao 3/7/2011
Sub unProtect_one_cell_in_all_Worksheet()
Dim wksht As Worksheet
Dim w_i As Long
Dim wkshtnames()
w_i = 0

''count the worksheet
For Each wksht In ActiveWorkbook.Worksheets
    w_i = w_i + 1
    ReDim Preserve wkshtnames(1 To w_i)
    wkshtnames(w_i) = wksht.Name
Next wksht

''showt the sheet's name
    For w_i = LBound(wkshtnames) To UBound(wkshtnames)
        ''update the password in ""
        Sheets(wkshtnames(w_i)).Unprotect Password:=""
        Sheets(wkshtnames(w_i)).Select
        Range("A1").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
        Sheets(wkshtnames(w_i)).Protect Password:=""
    Next w_i
End Sub
''//Created by RoyHsiao 3/7/2011
Sub Protect_one_cell_in_all_Worksheet()
Dim wksht As Worksheet
Dim w_i As Long
Dim wkshtnames()
w_i = 0

''count the worksheet
For Each wksht In ActiveWorkbook.Worksheets
    w_i = w_i + 1
    ReDim Preserve wkshtnames(1 To w_i)
    wkshtnames(w_i) = wksht.Name
Next wksht

''showt the sheet's name
    For w_i = LBound(wkshtnames) To UBound(wkshtnames)
        ''update the password in ""
        Sheets(wkshtnames(w_i)).Unprotect Password:=""
        Sheets(wkshtnames(w_i)).Select
        Range("A1").Select
        Selection.Locked = True
        Selection.FormulaHidden = False
        Sheets(wkshtnames(w_i)).Protect Password:=""
    Next w_i
End Sub

Open in new window

0
 
LVL 6

Expert Comment

by:royhsiao
ID: 35062665
You could update the range and password in unProtect_one_cell_in_all_Worksheet and Protect_one_cell_in_all_Worksheet.
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 35062686
You could use a macro to unprotect the worksheet and another macro to protect the worksheet.

This method makes the current lock value meaningless.

Also, note that Excel 2007 and 2010 can use cell styles that can set or reset protection on unlocked worksheets.
0
 
LVL 6

Accepted Solution

by:
royhsiao earned 500 total points
ID: 35343745
Hi Chrisneilhopkins,

I provided the following code in the post id: 35062651. I hope it will help.
''//Created by RoyHsiao 3/7/2011
Sub unProtect_one_cell_in_all_Worksheet()
Dim wksht As Worksheet
Dim w_i As Long
Dim wkshtnames()
w_i = 0

''count the worksheet
For Each wksht In ActiveWorkbook.Worksheets
    w_i = w_i + 1
    ReDim Preserve wkshtnames(1 To w_i)
    wkshtnames(w_i) = wksht.Name
Next wksht

''showt the sheet's name
    For w_i = LBound(wkshtnames) To UBound(wkshtnames)
        ''update the password in ""
        Sheets(wkshtnames(w_i)).Unprotect Password:=""
        Sheets(wkshtnames(w_i)).Select
        Range("A1").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
        Sheets(wkshtnames(w_i)).Protect Password:=""
    Next w_i
End Sub
''//Created by RoyHsiao 3/7/2011
Sub Protect_one_cell_in_all_Worksheet()
Dim wksht As Worksheet
Dim w_i As Long
Dim wkshtnames()
w_i = 0

''count the worksheet
For Each wksht In ActiveWorkbook.Worksheets
    w_i = w_i + 1
    ReDim Preserve wkshtnames(1 To w_i)
    wkshtnames(w_i) = wksht.Name
Next wksht

''showt the sheet's name
    For w_i = LBound(wkshtnames) To UBound(wkshtnames)
        ''update the password in ""
        Sheets(wkshtnames(w_i)).Unprotect Password:=""
        Sheets(wkshtnames(w_i)).Select
        Range("A1").Select
        Selection.Locked = True
        Selection.FormulaHidden = False
        Sheets(wkshtnames(w_i)).Protect Password:=""
    Next w_i
End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:broomee9
ID: 35783891
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now