Workbooks

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hi Pete,

Thanks for that, the 3rd option would be the most appropriate in our case. I will present at the next meeting and see how sensitive the business find the information with the option at hand

Niten

[Former Member · Accepted solution]

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

[Former Member]

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