Is there any way to protect formulas AND import external data?

[Skysurfer]

Hi,


Is there any work-around for importing external data into a protected worksheet?


I have a worksheet that the user can use either to copy-paste a database or to use Excel's 'import external data' on the data menu. That database worksheet then feeds a dashboard. Problem is that there are formulas and ActiveX controls on that sheet that need to be protected, but once protection is engaged, the 'import external data' menu is disabled.


Is there any kind of trick or vba (oooh, that would have been fun during Halloween!) that will allow me to have both protection and import external data on the same sheet?


Thanks,


Lawrence

 

[kchiba]

Hi Lawrence,


You can wrap the Import External Data in a Macro, so that to import the data the user can click on a button.


At the start of the Macro you Unprotect the sheet,


Then run the Data Import


Then protect the sheet again


I would suggest that it would e better to separate the data from the Dashboard by placing them on different sheets.


cheers


kanti

 

[Skysurfer]

Thanks kanti.


But how do I create the macro so that the user can have access to Data > Import external data and NOT be able to say delete the button that triggers the macro? Or delete hidden formulas?


Right now, I have three parts to the dashboard: the database sheet; a pivot table sheet that reads the database; and the charts sheet that reads the pivot tables. I just need to figure out a way to let the user import data to that database sheet beginning in cell C16 and not expose anything above or to the left to destruction.


Is that even possible?


Lawrence

 

[kchiba]

Hi Lawrence,


You can protect/unprotect specific cell on a spreadsheet.


When you format a cell click on the Protection Tab and de-select the Locked box, this would mean that in that cell input is allowed and the rest of the sheet is protected.


So Select all the Cell below row 16 and right click to format cells and than on the protection tab de-select the Locked.


kanti

 

[Skysurfer]

Every cell to the right of and below C16 has protection switched off. So when sheet protection is turned on it is to protect those other cells, I can't import data.


How do I import data and still protect the content of those other cells?


Lawrence

 

[kchiba]

Hi Skysurfer,


Here is a an example of the code you could use around your import code. Place a button on the Sheet and link the code to the button


Sub UnprotectProtect()

Dim myPassword As String

myPassword = "qwerty"


'Unprotect the Sheet

ActiveSheet.Unprotect myPassword


' PLACE YOU IMPORT HERE for expample


With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:UsersDesktoproles.txt", Destination:=Range("$C$16") _

)

End With


'Protect the Sheet

ActiveSheet.Protect myPassword

End Sub