I am working on a multiple tab report that uses VBA code to control which users see each tabs and to update the data every hour. The problem I am facing is when the data is refreshing, all of the tabs become unhidden and are cycled through which could result in data being seen by an unauthorized person. Is there any way to prevent the tabs from cycling or a better solution for handling user security?
Date
Votes
7 comments
-
Jet Reports Historic Posts How are the workbooks getting updated? Jet Reports uses veryhidden sheets that refresh when the reports are refreshed and I wonder if that is what is causing problems.
Instead of having sheets that show or hide based on the time would it make more sense to have one sheet that is updated based on the time its opened/refreshed? -
Jet Reports Historic Posts The sheets are automatically updated hourly, using the jet reports refresh in the vba code.
I do not understand your recommendation. Could you please explain? -
Jet Reports Historic Posts I have a worksheet that uses the refresh VBA:
Sub AutoRefresh()
Application.Run "JetReports.xlam!JetMenu", "Refresh"
If ThisWorkbook.Worksheets("Sheet Name").Range("CELL") <> "GO10" Then
Exit Sub
End If
Application.OnTime Now() + TimeValue("00:00:10"), "AutoRefresh"
End Sub
When the VBA runs all sheets with the Jet logic of +HIDESHEET in A1 stay hidden.
How are you hiding the sheets? -
Jet Reports Historic Posts The tabs are hidden based on the windows user logon using the sheet visible= veryhidden if they do not have authority to view the page.
-
Jet Reports Historic Posts If you are conditionally hiding sheets with VBA then I don't know of a solution for the refresh problem. I would recommend using the +HIDESHEET Jet Report keyword, but that only sets the sheet to hidden and not veryhidden so it can still be shown if the users know how.
I think the best solution is to have different reports with the needed info distributed to different people. This would eliminate the need and eliminate the VB code. Not sure if this is an option though.
Best of luck. If you find a solution using VBA then please post back.
Jason -
Jet Reports Historic Posts Thanks for your help Jason. If I figure something else out, I will update the post.
-
Jet Reports Historic Posts It is not clear to me if
1) the sheets were visible during the refresh period and then go back to hidden after the VBA and Refresh execute, or
2) if the code unhides the sheets and leaves them unhidden.
If the first is true I would test by turning off screen updating in VBA at the beginning of the code, then turn it back on at the end of the code. Turning off the screen updating means the screen stops refreshing (your view doens't change) while the code is running.
Turn off:
Application.ScreenUpdating = False
Turn on:
Application.ScreenUpdating = True
Please sign in to leave a comment.