Skip to Content
author's profile photo Former Member
0
Former Member

Workbooks

Posted on Oct 18, 2006 at 12:43 PM 14 Views

Can someone advise on how (if possible) I can build a workbook which consists of a number of worksheets but I want some worksheets visible to a set of users and some of the others available to another set of users. Some of the worksheets will also be common to both sets

Thanks

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2006 at 02:00 PM
    2

    Hi Niten,

    I need to know a little bit more about exactly what you want to achieve to be able to give a complete answer. But, here are some general thoughts ...

    First, about visible and hidden worksheets. There are actually 3 options for the "visible" property of an Excel worksheet:

    1. Visible

    2. Hidden

    3. Very Hidden

    Most Excel users know about options 1 & 2; and, most users know how to make a Hidden (#2) sheet visible. If you desire to make a worksheet hidden for user convenience, but allow them to make the sheet visible if they want, then use Hidden (#2). If you desire to make a worksheet hidden to protect proprietary data, then make the worksheet very hidden (#3) and lock the VBProject with a password.

    Second, how will you know which users should see which worksheets? You have several options:

    1. set up your own authorization table in the workbook, based on the user's LAN ID, and use Win32 API function to read the user's LAN ID

    2. same as #1, but base the table on user's BW ID and read the user's BW ID from the system registry

    3. use authorization variable from BW system; to use this method, there would need to be 1 query in the workbook that all users can see, and all other sheets start out hideen for all users. After this 1 query is refreshed, you read the user's authorization by reading the return variable from the BW query. Based on this variable return, you unhide certain other sheets.

    Tell me which of the above options best fits your needs and I will give you code to accomplish the tasks.

    - Pete

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member
      Oct 18, 2006 at 07:47 PM

      Hi Niten,

      To give you a feel for how this would work, below is VBA code that would go in the Excel workbook to read the return from the Authorization Variable after the first query is refreshed. Instead of a message box, you would insert your code to unhide some of the sheets and refresh the queries on those sheets.

      Sub testReadVariableValue()

      Dim nm As Name

      Dim qID As String

      Dim authVariable As String

      'local query ID of first query embedded in workbook

      qID = "SAPBEXq0001"

      'technical name of Authorization Variable

      authVariable = "YCVCABU1"

      'read each Name in the workbook

      For Each nm In ThisWorkbook.Names

      'is this Name related to the first query?

      If nm.Name Like "" & qID & "" Then

      'does this name relate to our authorization variable?

      If nm.Name Like "" & authVariable & "" Then

      'just for demonstration, show me the

      'address of this Named Range, and

      'the contents of the returned value(s)

      msg = nm.RefersToRange.Address & vbCrLf

      msg = msg & nm.RefersToRange.Cells(2).Value

      MsgBox msg, vbInformation, nm.Name

      End If

      End If

      Next nm

      End Sub

      - Pete

    Comment on This Answer
  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2006 at 12:46 PM
    0

    I don't think you can do that; you can put diffrent query in the sheet and, using authorization object, the user will/wil not see data for that query.

    Excel sheet can't be used with authorization check.

    Hope it helps.

    Regards

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.