VBA Help Please! : User forms & Very hidden to create access rights in a workbook

r_DEAd

New Member
Joined
Feb 12, 2023
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
Thanks for reading I'm in desperate need of help!

I have a Workbook with multiple worksheets which is to be used by multiple users each user is only meant to see relevant information, I've created a user management table and login form and its all working as it should. My issue is when I save and exit then reopens the workbook the sheet i was using is visible behind the password form. So for example, if the workbook had 3 sheets, Sheet1, Sheet2, Sheet3, and 3 Users User, User 2, and User 3, User 1 is admin with access to everything. User2 has access to sheet 1 and User3 has access to sheet 2. If User 1 saves the workbook on sheet 3 and exits excel when user 2 opens it the contents of sheet 3 are visible behind the log in form, I have all the sheets hidden and workbook tabs hidden but the last sheet the workbook was saved on keeps loading behind the login form every time Below is the VBA I'm using If anyone can help it will be greatly appreciated as 3 hours of googling and I'm no closer to a solution.

Thanks in Advance!


Private Sub Workbook_Open1()


ActiveWindow.WindowState = xlMinimized
ThisWorkbook.Application.Visible = False
Application.Windows(1).Visible = False

Login_Frm.Show

End Sub

Private Sub Workbook_Open()

Dim sh As Worksheet
Dim wsh As Worksheet

ThisWorkbook.Unprotect 1234

Set sh = ThisWorkbook.Sheets("User Managment")
sh.Visible = xlSheetVisible

For Each wsh In ThisWorkbook.Sheets


Next


sh.Unprotect 1234
sh.Cells.EntireColumn.Hidden = True
sh.Cells.EntireRow.Hidden = True
sh.Protect 1234

ActiveWindow.DisplayWorkbookTabs = False


ThisWorkbook.Protect 1234







End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
The following two macro will either HIDE or UNHIDE all sheets except those specifically listed in the macro. You can change which sheets
you want exempted by listing them as shown (or don't list any sheets). Play around with the code.


Code:
Sub UnhideAllSheets()

'Unhide all sheets in workbook.

Dim ws As Worksheet

Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws

Application.ScreenUpdating = True

End Sub


Sub HideAllSheets()

'Hide all calendar sheets in workbook.
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
    If ws.Visible <> xlSheetHidden And ws.Visible <> xlSheetVeryHidden And ws.Name <> "Sheet1" Then
        ws.Visible = xlSheetHidden
    End If
Next ws

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top