2 Problems: #1 Expand/Collapse groups in protected Worksheets & #2 Add username in worksheets.

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
Problem#1
Expand/Collapse groups in protected Worksheets:

When i put the following vba codes, it works only for one protected worksheet. But i need it for several protected worksheets.
Pls help someone to solve this issue. Your help will be highly appreciated.
Thanks in advance.


In a standard Module:
VBA Code:
Sub protect_sheet()
ActiveSheet.Unprotect "639"
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("CBC_Report")
Sh.Protect "639", userinterfaceonly:=True
Sh.EnableOutlining = True
ActiveSheet.Protect "639"
End Sub

In ThisWorkbook:
VBA Code:
Private Sub Workbook_Open()
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("CBC_Report")
Sh.Protect "639", userinterfaceonly:=True
Sh.EnableOutlining = True
End Sub


Problem#2
I need to Add Username in a fixed cell of a worksheet when a user login by Login Interface.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
Problem#1
Expand/Collapse groups in protected Worksheets:

When i put the following vba codes, it works only for one protected worksheet. But i need it for several protected worksheets.
Pls help someone to solve this issue. Your help will be highly appreciated.
Thanks in advance.


In a standard Module:
Sub protect_sheet()
ActiveSheet.Unprotect "639"
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("CBC_Report")
Sh.Protect "639", userinterfaceonly:=True
Sh.EnableOutlining = True
ActiveSheet.Protect "639"
End Sub

In ThisWorkbook:
Private Sub Workbook_Open()
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("CBC_Report")
Sh.Protect "639", userinterfaceonly:=True
Sh.EnableOutlining = True
End Sub


Problem#2
I need to Add Username in a fixed cell of a worksheet when a user login by Login Interface.
It has been solved..........
 

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
Glad to hear you got the solution.

Do you mind posting about your solution? Then it is perfectly fine to mark your post as the solution to help future readers.

Thanks.
Good Afternoon Sir,

Find below the codes for Problem#1 and Problem#2 still unsolved:

In ThisWorkbook:

Private Sub Workbook_Open()

Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("SheetName")
Sh.Protect "Password", userinterfaceonly:=True
Sh.EnableOutlining = True

End Sub
-----------------------------

In a Standard Module:

Option Explicit

Sub protect_sheet()
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("SheetName")
Sh.Protect "Password", userinterfaceonly:=True
Sh.EnableOutlining = True
End Sub
--------------------------

For Deactivate Hidden Protected Worksheets:

In Each Worksheet:

Private Sub Worksheet_Deactivate()
ActiveWorkbook.Unprotect "Password"
Me.Visible = xlSheetHidden
ActiveWorkbook.Protect "Password"

End Sub
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,343

ADVERTISEMENT

Thanks for the feedback.

Did you solve problem #1 completely? What was the actual scenario in the project? I am interested, so in case you need further help, I'd try to contribute to the solution if there is something not working entirely.
If you need to get "SheetName" worksheet protected when the workbook is opened, then why wouldn't you just protect the worksheet and save the workbook instead of doing that during the workbook opening?
In Worksheet_Deactivate, as soon as you select another worksheet, it simply makes that worksheet unusable/hidden. I am trying to understand the scenario.
And protect_sheet() subroutine: It is still protecting "single" sheet. Perhaps you need to pass a sheet name parameter, and use this subroutine for multiple worksheets? I can't see where you actually call this function, so I am trying to guess its purpose.

For example, if you call the following code with the sheet name, protect_sheet("MySheet"), then it will do its job on this worksheet. In fact, you can even pass different passwords for each worksheet.
VBA Code:
Sub protect_sheet(sheetName as string)
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets(sheetName)
Sh.Protect "Password", userinterfaceonly:=True
Sh.EnableOutlining = True
End Sub

As I said, I am just trying to understand.

Problem#2
I need to Add Username in a fixed cell of a worksheet when a user login by Login Interface.
Which Login interface are you referring to?
 

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
Thanks for the feedback.

Did you solve problem #1 completely? What was the actual scenario in the project? I am interested, so in case you need further help, I'd try to contribute to the solution if there is something not working entirely.
If you need to get "SheetName" worksheet protected when the workbook is opened, then why wouldn't you just protect the worksheet and save the workbook instead of doing that during the workbook opening?
In Worksheet_Deactivate, as soon as you select another worksheet, it simply makes that worksheet unusable/hidden. I am trying to understand the scenario.
And protect_sheet() subroutine: It is still protecting "single" sheet. Perhaps you need to pass a sheet name parameter, and use this subroutine for multiple worksheets? I can't see where you actually call this function, so I am trying to guess its purpose.

For example, if you call the following code with the sheet name, protect_sheet("MySheet"), then it will do its job on this worksheet. In fact, you can even pass different passwords for each worksheet.
VBA Code:
Sub protect_sheet(sheetName as string)
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets(sheetName)
Sh.Protect "Password", userinterfaceonly:=True
Sh.EnableOutlining = True
End Sub

As I said, I am just trying to understand.


Which Login interface are you referring to?
Dear Sir thank you very much for your reply.

Yes, my Problem#1 has been solved. Attached herewith pic of my worksheet for your reference.

Problem#2 still unsolved. Sending herewith the pic of my login interface for your reference.
 

Attachments

  • Capture User ID.JPG
    Capture User ID.JPG
    96.2 KB · Views: 2
  • MyWorkbook.JPG
    MyWorkbook.JPG
    140.1 KB · Views: 2
  • Pic#1_Login Interface.JPG
    Pic#1_Login Interface.JPG
    79.3 KB · Views: 2

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,343

ADVERTISEMENT

Regarding problem #2:
Double click on the Login button, it should open the procedure for the button click. I would normally need to see that code to say exactly where to put the following code line, but without seeing it, copy and paste the following code right after verifying the user credentials in this procedure:

VBA Code:
Worksheets("Sheet1").Range("A1").Value = TextBox1.Text

I assumed Sheet1 as the worksheet name, A1 as the cell that you want to store the user ID, and TextBox1 as the text box control name of the User ID on your form. You need to change these to match your actual references.
 

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
Regarding problem #2:
Double click on the Login button, it should open the procedure for the button click. I would normally need to see that code to say exactly where to put the following code line, but without seeing it, copy and paste the following code right after verifying the user credentials in this procedure:

VBA Code:
Worksheets("Sheet1").Range("A1").Value = TextBox1.Text

I assumed Sheet1 as the worksheet name, A1 as the cell that you want to store the user ID, and TextBox1 as the text box control name of the User ID on your form. You need to change these to match your actual references.
Dear Sir thank you very much for your support.
Find below the complete code of login interface:

Private Sub cmdLogin_Click()
Dim user As String
Dim password As String

user = Me.txtUserID.Value
password = Me.txtPassword.Value

If (user = "admin" And password = "admin") Or (user = "user" And password = "user") Then

Unload Me
Application.Visible = True
Worksheets("Biochemical_Report").Activate

Else
If LoginInstance < 2 Then

MsgBox "Invalid login credentials. Please try again.", vbOKOnly + vbCritical, "Invalid Login Details."
LoginInstance = LoginInstance + 1

Me.txtUserID.Value = ""
Me.txtPassword.Value = ""

Me.txtUserID.SetFocus

Else

MsgBox "Yoy have exceeded the maximum number of login attempt.", vbOKOnly + vbCritical, "Invalid Credentials."
Unload Me
ThisWorkbook.Close savechanges:=False
Application.Visible = True
LoginInstance = 0

End If


End If


End Sub
 

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
Regarding problem #2:
Double click on the Login button, it should open the procedure for the button click. I would normally need to see that code to say exactly where to put the following code line, but without seeing it, copy and paste the following code right after verifying the user credentials in this procedure:

VBA Code:
Worksheets("Sheet1").Range("A1").Value = TextBox1.Text

I assumed Sheet1 as the worksheet name, A1 as the cell that you want to store the user ID, and TextBox1 as the text box control name of the User ID on your form. You need to change these to match your actual references.
I just solve it as per your advice. Thank you very much sir.
Warm Regards,
 

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
Problem#2 has been solved. Complete code has given below for reference.

Option Explicit

Private Sub cmdLogin_Click()

Dim user As String
Dim password As String

user = Me.txtUserID.Value
password = Me.txtPassword.Value

If (user = "admin" And password = "admin") Or (user = "user" And password = "user") Then

Unload Me
Application.Visible = True
Worksheets("Biochemical_Report").Activate

ActiveSheet.Unprotect "639"
Worksheets("Biochemical_Report").Range("K31").Value = Me.txtUserID.Value
ActiveSheet.Protect "639"


Else
If LoginInstance < 2 Then

MsgBox "Invalid login credentials. Please try again.", vbOKOnly + vbCritical, "Invalid Login Details."
LoginInstance = LoginInstance + 1

Me.txtUserID.Value = ""
Me.txtPassword.Value = ""

Me.txtUserID.SetFocus

Else

MsgBox "Yoy have exceeded the maximum number of login attempt. Please contact your administrator.", vbOKOnly + vbCritical, "Invalid Credentials."
Unload Me
ThisWorkbook.Close savechanges:=False
Application.Visible = True
LoginInstance = 0

End If


End If


End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,052
Messages
5,622,420
Members
415,895
Latest member
Akhilesh28

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
Top