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

[Atiqul Haque]

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.

 

[Atiqul Haque]

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..........

 

[smozgur]

It has been solved..........

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.

 

[Atiqul Haque]

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]

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.

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]

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.

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.

 

[smozgur]

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:

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]

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:

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]

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:

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]

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