locked
Userform being active, using other Excel files. RRS feed

  • Question

  • Hello guys!

    I'm trying to make a userform work that is in a worksheet, but I want to use and manipulate other excel worksheets. I know that by including a zero in front of the code it lets you manipulate other spreadsheets, as below:

    Private Sub CommandButton1_Click()
    UserForm1.Show 0
    End Sub

    Private Sub Worksheet_Activate()
        With ActiveWindow
            .DisplayHeadings = False
            .DisplayWorkbookTabs = False
            .DisplayHorizontalScrollBar = False
            .DisplayVerticalScrollBar = False
        End With
        Application.DisplayFormulaBar = False
    End Sub

    But the idea is to hide the commands from the folder that contains this form and keep them available in the other files that you open from excel.

    Does anyone know if it is possible?

    thanks,

    Monday, January 22, 2018 9:53 PM

Answers

  • I don't understand why you want to hide the application. As far as I can determine, there is no need to hide the application.

    The example that I gave you does need some additional enhancements to make it safe from other users being able to unhide and unprotect the worksheets. You will need a user login and password on the userform. Depending on the users level of clearance, whether a button is displayed to unhide and unlock the worksheets and this way the unhide and unprotect code can be shifted into the Userfrom module so that it cannot be seen from selecting Macros on the Developer ribbon.


    Regards, OssieMac

    • Marked as answer by Cdilenes Saturday, January 27, 2018 8:20 PM
    Friday, January 26, 2018 12:12 AM
  • OssieMac Thank you by the tips,

    See how the file I got, using an excel file and keeping the userform from another open. But I need it to stay open even by closing the other file.

    https://1drv.ms/f/s!AgtAj9TaY-s9afCSXVujM0VyS8w

    Regards, Cleber

    • Marked as answer by Cdilenes Tuesday, November 13, 2018 11:28 PM
    Thursday, January 25, 2018 10:30 PM

All replies

  • What you are attempting to do can be achieved as follows.

    Use a blank worksheet on which to show the Userform.

    All other worksheets can be very hidden with VBA code as well as password protected. The user cannot even see that other worksheets exist.

    The VBA project can be password protected against viewing.

    The user will still have access to the Userform and other workbooks that I assume get opened via the Userform.


    Regards, OssieMac

    Tuesday, January 23, 2018 7:22 AM
  • Hello OssieMac,

    Thanks for replying,

    I do not understand what you mean, but what I want is for the userform to be available to the user but not necessarily the file that it is and allow other files to be opened and changed from excel.

    I have a timer on the form, but I want to open other excel files. But the folder on the timer have to inhibited.

    Regards, Cleber

    Tuesday, January 23, 2018 10:27 PM
  • I have uploaded a zipped copy of a workbook with VBA code to apply the protection to the workbook and worksheets. However. you need to manually protect the VBA Project.

    When you create a button on your projects to show the Userform, right click the button and select Format control -> Protection tab and uncheck the box against "Locked". This is basically the same for a forms control button or ActiveX button so that the user can click the button to show the Userform when the worksheet is protected.

    To lock the VBA Project (VBA code) against viewing.

    • Open the VBA Editor.
    • Right click the project name in the Project Explorer (left column)Select VBA Project properties
    • Select Protection tab (at top of dialog)
    • Check box against "Lock project for viewing"
    • Enter a password of choice and then confirm password. (Don't forget the password)
    • Click OK


    The project is not locked until after you Save and Close the workbook and re-open and then the VBA cannot be viewed.

    To unlock the VBA Project

    • Double click the Project name in the Project Explorer (or click plus sign to expand tree)
    • Enter the password.
    • Expand the Project tree by clicking each of the plus signs in the Project Explorer column.
    • After unlocking the project, the project remains unlocked until after save and close the workbook.

    To remove the VBA password

    • Same as for locking but uncheck the box against "Lock project for viewing"
    • Delete the password from the fields.

    Info re the Uploaded workbook

    • The uploaded workbook is currently all unprotected.
    • The password used for all of the protection is displayed on the worksheet for your convenience while testing.
    • Open the VBA editor and run 'Sub ProtectAndHideSheets"
    • Lock the VBA project (as per guidelines above)
    • Save and close the workbook.
    • Re-open the workbook and you will see that there is virtually nothing you can do in the workbook or the VBA Project in the VBA editor. However, you can click the button to Show the Userform.
    • You can open and work on any other workbooks.
    • You can remove the Project protection as per the guidelines above and run the "Sub UnProtectUnHideAllSheets". You will required similar code in any project that you create so that you can edit and update as required.

    Link to zipped example workbook. Download, Unzip and test as per above guidelines.

    Example workbook

    In any project that you create, my suggestion is to only allow a blank worksheet to be visible and that is where you show the userform.

    With the code using "xlSheetVeryHidden", the user will not even be aware of other hidden worksheets in the workbook and with the structure protected, the user cannot add worksheets or unhide worksheets in the Immediate window.

    Feel free to get back to me with any questions.


    Regards, OssieMac

    Wednesday, January 24, 2018 1:07 AM
  • But if I use the "xlSheetVeryHidden" command, the active userform code will try to write data to the hidden woorksheets,  but will display error.

    I also need to leave the userform active, but not preventing the user from opening and manipulating other files in excel.

    Regards, Cleber

    Wednesday, January 24, 2018 11:28 PM
  • But if I use the "xlSheetVeryHidden" command, the active userform code will try to write data to the hidden woorksheets,  but will display error.

    There is no problem writing to a hidden worksheet with VBA code. However, it must first be unprotected with the VBA code and then re-protected after writing is finished. It does not need to be unhidden.

    I am suspecting that you think that the worksheet needs to be selected for the VBA code to write to it but there is almost never a requirement to select a worksheet to write to it with VBA code. Simply use the full worksheet reference and range reference and data can be written to a worksheet without selecting it.

    I have edited the example workbook and added a button to the Userform so you can enter some dummy data in the Userform TextBoxes and then use the button to save data to a hidden worksheet. (It saves to Sheet2)

    Following link the edited workbook.

    Edited example workbook for saving data from Userform to Hidden Sheet

    1. The uploaded workbook is currently unprotected.
    2. Open the VBA editor and run the code to protect and hide the worksheets.
    3. There is no need to protect the VBA project code at this stage while you are testing.
    4. Change back to the worksheets and Click the button to show the Userform.
    5. Enter some dummy data in the 4 TextBoxes on the Userform.
    6. Click the button to save the data.
    7. Repeat the last 2 steps a few times and it will save the data on the next blank row each time.
    8. Open the VBA editor and run the code to unprotect and unhide all worksheets.
    9. Check Sheet2 to see what the code has written to it.

    Hope this clears some things up for you but feel free to get back to me again if any problems.

    Note also that I have used a Public constant for the password at the top of the Standard Module1 so that if you want to change the password then you only need to do so in one place in the code. If you do change the password then ensure that you run the code to unprotect all first and that you do not forget the password.


    Regards, OssieMac


    • Edited by OssieMac Thursday, January 25, 2018 2:46 AM
    Thursday, January 25, 2018 2:08 AM
  • OssieMac Thank you by the tips,

    See how the file I got, using an excel file and keeping the userform from another open. But I need it to stay open even by closing the other file.

    https://1drv.ms/f/s!AgtAj9TaY-s9afCSXVujM0VyS8w

    Regards, Cleber

    • Marked as answer by Cdilenes Tuesday, November 13, 2018 11:28 PM
    Thursday, January 25, 2018 10:30 PM
  • I don't understand why you want to hide the application. As far as I can determine, there is no need to hide the application.

    The example that I gave you does need some additional enhancements to make it safe from other users being able to unhide and unprotect the worksheets. You will need a user login and password on the userform. Depending on the users level of clearance, whether a button is displayed to unhide and unlock the worksheets and this way the unhide and unprotect code can be shifted into the Userfrom module so that it cannot be seen from selecting Macros on the Developer ribbon.


    Regards, OssieMac

    • Marked as answer by Cdilenes Saturday, January 27, 2018 8:20 PM
    Friday, January 26, 2018 12:12 AM
  • Actually the idea is that the userform is visible to the user and when you want to check the registry you can access them freely. But being this active userform other excel spreadsheets can be opened and crafted. What I need is to keep a userform active, but the spreadsheet that is inserted is hidden, but does not imply the manipulation of excel.
    Thank you for your help!
    I will try to hide only the application from this file.

    Regards, Cleber

    Saturday, January 27, 2018 8:20 PM