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

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

I do not know of a 100% reliable method of accessing the current value of
the cell containing the function from within the function using VBA.

The safest way would to do a copy & paste-special values on all occurrences
of the function before you send it out.

A potentially unreliable alternative is to get the current value using
Application.caller.text

This returns the formatted current value as text, so you probably need to
convert it back to a number
The other problem with using this method is that the formatted value is
probably not exactly the same as the actual value (number of decimal places
etc), and could even be ##### if the user has altered zoom or column widths
etc, so its not 100% reliable.

Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

"Simon" wrote in message
...
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

This Should Keep the values as they are on you sheet after distribution.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationManual
End Sub

"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