Hi Simon,

Not exactly what you're asking for but it might do.

I have a list of network login names on a hidden worksheet with the
named range "References_Permissions_IDs" of people who have
permissions on a database.
If the user name of the person logged in is in this list when the
workbook opens the entire workbook refreshes (queries the DB), if not
then no values are changed.

In the This Workbook module:

Private Sub Workbook_Open()
Dim WS As Worksheet
Sheets("Instructions").Activate
'Set access level for workbook based on login id - if network id
appears in the range 'References_Permissions' then _
the query in the 'Query' worksheet will refresh on file open.
On Error GoTo User_Not_In_Permissions_List
If WorksheetFunction.VLookup(Netwrk_Login, Sheets("References").Range
("References_Permissions_IDs"), 1, 0) = Netwrk_Login Then
ActiveWorkbook.RefreshAll
With Sheets("Instructions")
.Visible = True
.Activate
End With
End If

User_Not_In_Permissions_List:
For Each WS In Worksheets
If WS.Visible = xlSheetVisible Then WS.Protect
Next WS
End Sub
-------------------------------------------------------------------------------
In a standard module:

Public Function Netwrk_Login() As String
Netwrk_Login = Environ("username")
End Function
---------------------------------------------------------------------------------

Dependant on the version of Excel used the following may be needed to
idenify the user logged in as Environ has only been available (I
think) since version 2002 SP3

Private Declare Function apiGetUserName Lib "advapi32.dll" ()
Function Netwrk_Login() As String
'Returns the network login name - called in Workbook_Open event
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX < 0 Then
Netwrk_Login = Left$(strUserName, lngLen - 1)
Else
Netwrk_Login = ""
End If
End Function
-------------------------------------------------------------------------------------------

HtH,
JF

On 10 Dec, 09:51, Simon wrote:
Hi, I have an excel spreadsheet with a number of cells that contain a formula
which uses a user defined macro that I have created to retrieve data from an
access database and populate the cells. *The problem I have is that the
database is sitting in a confidential directory at work which I have access
to so when the formulas are updated the data is retrieved correctly. *When I
send the excel file to staff who don't have access to the confidential
directory, the cell values become blank I think because they user defined
macro cannot retrieve the data from the database which is in the restricted
directory. *

Is there any code that I can place in my macro to say that if you can't find
the database value then don't alter the contents of the cell and leave the
cell as it is? *I'm using ADODB to connect to the excel db in my user defined
macro

Thanks.
Simon