none
PW need to enter first (based from the choosen report) in the Form before going to specific Sheets RRS feed

  • Question

  • Hi Guys,

    I need your help, I am creating a Report where can access by many with different level (Executives, Member from Team1, Member from Team2, etc).

    What I want to achieve is when a member like an Executive want to access the file, then he/she will choose "Executive" from the Combo box then must enter the Password on below textbox to access which Sheets to show declared from the macro.

    Ex: once the Executive successfully enter the correct PW, then he can access the 'Executive Summary' sheet, and so the other Team's Report. If Team1 was choosen in the dropdown by a member, then only 'Team1 Summary' and 'Team1 Data' sheets will show.

    This is my another need, from the 'Reference' tab, there's this list of Access (Column B) - this list is the combo box list, then the corresponding PW in Column C which the required PW for each Access - it is the one should they use in the PW textbox in the userform.

    I have up uploaded the workbook in OneDrive: https://1drv.ms/x/s!Au9kZOZjRkvkkl6RpOuI9JmBWIBu

    Thank you so much Guys and more power to you!

    All the best,

    Jaeson

    Friday, November 18, 2016 7:39 AM

All replies

  • Hi Jaeson,

    I’ve read through your query and I am afraid the build-in function in Excel cannot be used to achieve the goal. However, I’ve consulted an engineer who supports VBA related questions in another forum and confirmed that your requirement might be achieved via coding. I’ll move your question to the following dedicated MSDN forum for Excel. Hope you will find a solution there:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best Regards,
    Yuki Sun

    Monday, November 21, 2016 5:07 AM
  • Hi,

    You could use Worksheet.Visible Property (Excel) to hide the sheets you want.

    To avoid users showing the sheets, you could customize the context menu, hide the options "Hide" and "Unhide"

    Their idMso are


    So, the XML should be:

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
       <contextMenus>
          <contextMenu idMso="ContextMenuWorkbookPly">
             <button idMso="SheetHide" visible="false" />
               <button idMso="SheetUnhide" visible="false" />
          </contextMenu>
       </contextMenus>
    </customUI>

    For more information, please visit Customizing Context Menus in Office 2010

    You could download the sample file: https://1drv.ms/x/s!AkcxDWH1nFmJpHmsB-24hq9aNI02

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 22, 2016 5:31 AM
    Moderator