Posts by r_hind

    try using data tables. With data tables you can see what result you would achieve by putting different values through the same formula.

    In the simplest manner data tables will handle 2 changing (input) variables.

    In a more complex manner you can handle any number of changes by using the variable as a pointer to a list of variables.

    Let me know if I can help further.

    I'd say that your chances of achieving this are highly unlikely.

    Network security on web sites is such that you would usually only want specific people to have permission to save/update files.

    If on the other hand all the users are from the same business and have access to the same network you may give them access to this via an intranet.

    ** This code tests for the correct value in the CustomDocumentProperties

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ActiveWorkbook.CustomDocumentProperties("SecurityRequired").Value = " "
    End Sub

    Private Sub Workbook_Open()
    ActiveWorkbook.CustomDocumentProperties("SecurityRequired").Value = " "
    ActiveWorkbook.Windows(1).Caption = "Code Example by Robert Hind"

    Dim myOLApp
    Dim myOLItem
    Dim str
    Dim tmpPath
    Dim oNet, sUser

    Set myOLApp = CreateObject("Outlook.Application")
    Set myOLItem = myOLApp.CreateItem(olMailItem)
    Set oNet = CreateObject("WScript.Network")
    SendTo = ActiveWorkbook.CustomDocumentProperties("Owner").Value
    FirstName = ActiveWorkbook.CustomDocumentProperties("Source").Value

    sUser = oNet.UserName

    With myOLItem
    .Subject = ActiveWorkbook.Name & " Opened"
    .Recipients.Add SendTo
    .Body = "Hi " & FirstName & "," & vbCrLf & "Just to let you know I've opened " & ActiveWorkbook.Name & "." & vbCrLf & vbCrLf & "Regards," & vbCrLf & sUser
    '.Attachments.Add tmpPath,,1,str
    End With

    'str = "filename.CSV"
    'tmpPath = "\\Ct_server_1\D3Transfer\Linfox\LINFOX.CSV"
    If sUser = ActiveWorkbook.CustomDocumentProperties("Editor").Value Then
    ActiveWorkbook.CustomDocumentProperties("SecurityRequired").Value = "<<Value&gt;&gt;"
    ActiveWorkbook.CustomDocumentProperties("SecurityRequired").Value = " "

    End If

    End Sub

    If your main report (into which the spreadsheet is being inserted) is produced in Word for example then why not paste link (as say a picture) the selected areas into your report and let Word worry about the page numbering.

    The other advantage of this is that if you're viewing updated data from the same spreadsheet each month you can use a link to a named range and set up a template that will make report production very easy.

    Is this the sort of solution you're looking for.

    It sounds to me that you can achieve what you're looking for by using named ranges in combination with a lookup function such as vlookup. By using Named ranges the formula can be exactly the same therefore avoiding conflicts.

    If you want to e-mail (or post here) your spreadsheet I'll have a look at it for you and perhaps make some suggestions.

    You can set up [woe]*[/woe] that looks for a specific value in say the CustomDocumentProperties and protects the spreadsheet (from data entry, copying and pasting etc) unless the value is correct.

    When called from the switchboard the required value can be entered into CustomDocumentProperty allowing use.

    I've set up several spreadsheets which will only open (for editing) when the network logon matches a pre-set value...and it works very well.

    Would this sort of solution help? If you need any further help..let me know.

    I'm having some troublesome difficulties continually getting "Unable to read file" errors.

    The spreadsheets concerned all have pivot tables included and when I click Ok (on receiving the error message) all the pivot tables are discarded ..with the following message "[pivot table ref] was discarded due to integrity problems".

    I'm using Excel 2002 and have installed service pack 2 and the hot fix referred to in Knowledge Base article 812384.

    Is anyone else experiencing this issue? Can you help?