I am creating a spreadsheet containing sensitive financial reports that I want to selectively present only to those users who are authorised to view specific reports. I created a "Welcome" page which has a drop down list of internal users to select from. Once the name has been selected (cell H17) that user then supplies their unique password (cell H19).
My challenge (I am not a VB programmer) is twofold:
1) verifying that the password supplied is correct - that part is easy enough using the following conditional statement:
=IFERROR(IF(H19=VLOOKUP(H17,formula!I3:J56,2,0),"True","False"),"")
But what I am struggling with is how to then, if the value returned is "True" (i.e. password matches), display the macro button that is assigned to a macro which displays a report tab that is currently hidden from view.
After I recorded the macro, I got the following basic code to help display the report tab:
Sub display()
'
' display Macro
'
'
Sheets("formula").Select
Sheets("Report Generator").Visible = True
End Sub
The thing is I don't want the user to see this macro button to be able to unhide that report tab, unless and until the password verification has taken place. So my questions is: how can I make the macro button appear only once password verification takes place?
2) There are different types of reports (different report tabs) that I want different users to view, depending on their level of authorisation. How can I achieve this, in the above scenario? Let's say there are a total of 3 different reporting levels; the basic level grants the user access to the report tab from 1) above only. Levels 2 and 3 will get access to this report tab plus several other tabs as well. How can I make the unhiding of tabs conditional/dependent upon which user has verified their password?
Thank you so much in advance for your help, I really appreciate it.
Andre
My challenge (I am not a VB programmer) is twofold:
1) verifying that the password supplied is correct - that part is easy enough using the following conditional statement:
=IFERROR(IF(H19=VLOOKUP(H17,formula!I3:J56,2,0),"True","False"),"")
But what I am struggling with is how to then, if the value returned is "True" (i.e. password matches), display the macro button that is assigned to a macro which displays a report tab that is currently hidden from view.
After I recorded the macro, I got the following basic code to help display the report tab:
Sub display()
'
' display Macro
'
'
Sheets("formula").Select
Sheets("Report Generator").Visible = True
End Sub
The thing is I don't want the user to see this macro button to be able to unhide that report tab, unless and until the password verification has taken place. So my questions is: how can I make the macro button appear only once password verification takes place?
2) There are different types of reports (different report tabs) that I want different users to view, depending on their level of authorisation. How can I achieve this, in the above scenario? Let's say there are a total of 3 different reporting levels; the basic level grants the user access to the report tab from 1) above only. Levels 2 and 3 will get access to this report tab plus several other tabs as well. How can I make the unhiding of tabs conditional/dependent upon which user has verified their password?
Thank you so much in advance for your help, I really appreciate it.
Andre
Last edited:
