UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sheet Protection In Macro, Office 2013    
 
   
pwbrownapcom
post Apr 18 2019, 08:43 AM
Post#1



Posts: 9
Joined: 5-March 19



Good morning all. Have an issue that I thought I had resolved but guess not. Currently I have the below macro in Microsoft 2013.

Sub HideRows()
Dim ws As Worksheet, c As Range
Application.ScreenUpdating = False
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Things to do", "Review", "2018 Cost Center Rates" 'sheets to exclude
'do nothing
Case Else 'hide rows on these sheets
For Each c In ws.Range("a48:a612")
c.EntireRow.Hidden = c.Value = 0
Next c
End Select
Next ws
Application.ScreenUpdating = True
End Sub


Now, what I need to do is have the Macro
1) Unprotect the worksheet Using password "Excel"
2) Run the macro
3) Reprotect the worksheet "Excel"

At first, tried the below to see if it would protect it after the macro but did not work
Sub HideRows()
Dim ws As Worksheet, c As Range
Application.ScreenUpdating = False
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Things to do", "Review", "2018 Cost Center Rates" 'sheets to exclude
'do nothing
Case Else 'hide rows on these sheets
For Each c In ws.Range("a48:a612")
c.EntireRow.Hidden = c.Value = 0
Next c
End Select
Next ws
Application.ScreenUpdating = True
End Sub
Public Sub ProtectCells(strSheetName As Excel.Worksheet)
Dim wks As Excel.Worksheet

Set wks = ActiveWorkbook.Worksheets(strSheetName)

With Application
'.Cells.Select 'Select all Cells on strSheetName
'.Selection.Locked = False 'Unlock all Cells on strSheetName
'.FormulaHidden = False 'Do not hide Formulas
' wks.Range("D5,F13,H21,H8").Select 'Select 4 Cells to Protect Formulas
'.Selection.Locked = True 'Lock those 4 Cells
'.Selection.FormulaHidden = False

wks.Protect Password:="Excel"
End With
'end add
End Sub

Would greatly appreciate any help as this would save time and ensure we do not "accidentally" leave the sheet unprotected. Users just love to modify formulas to suit their needs because of "exceptions" and that is simply not acceptable.


Thanks in advance!
Go to the top of the page
 
ADezii
post Apr 19 2019, 09:03 AM
Post#2



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. This is relatively simple but nonetheless is a multi-step process.
    1. Loop thru all Worksheets in the Active Workbook.
    2. Do nothing for Worksheets: "Things to do", "Review", "2018 Cost Center Rates"
    3. For all other Worksheets, check and see if they are Protected and if so Unprotect them.
    4. Hide the specific Rows for these Worksheets.
    5. Re-protect these Sheets.
  2. Code Definition:
    CODE
    On Error Resume Next
    Dim ws As Excel.Worksheet
    Dim c As Excel.Range

    Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Worksheets
      Select Case ws.Name
        Case "Things to do", "Review", "2018 Cost Center Rates" 'sheets to exclude
          'do nothing
        Case Else     'hide rows on these sheets
          'If Sheet Is Protected Then Unprotect
          If ws.ProtectContents = True Then ws.Unprotect Password:="Excel"
            For Each c In ws.Range("a48:a612")
              c.EntireRow.Hidden = (c.Value = 0)
            Next c
            
            ws.Protect Password:="Excel"
      End Select
    Next ws

    Application.ScreenUpdating = True
  3. Hope this helps.
Go to the top of the page
 
pwbrownapcom
post Apr 23 2019, 10:56 AM
Post#3



Posts: 9
Joined: 5-March 19



It does and thanks! only question tho is when the button is clicked, it will run and hide but automatically takes me to the "Things to do" tab.

This has brought up another item, we do not want all users to be clicking the button as some areas need to show the blanks and others do not. Is there also a way to password protect the button?

Thanks again!
Go to the top of the page
 
ADezii
post Apr 24 2019, 09:15 AM
Post#4



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


You can Enable/Disable the Command Button depending on certain conditions.
Go to the top of the page
 
pwbrownapcom
post Apr 25 2019, 02:38 PM
Post#5



Posts: 9
Joined: 5-March 19



is there anyway to have a password requirement when someone clicks on the button? Ultimately would be great if the button was simply disabled based on the user, but not sure how to link the network user name to the button. There are only 4 of us that need to be using the buttons.
Go to the top of the page
 
Debaser
post Apr 26 2019, 06:49 AM
Post#6



Posts: 126
Joined: 11-October 18



Something like:

CODE
Select Case UCase$(environ("username"))
   ' enter the allowed user names in upper case
   Case "USER1", "USER2", "USER3", "USER4"
     ' allowed
   Case else
     Msgbox "You are not authorised to run this code"
     Exit Sub
End Select


should give you a start.
Go to the top of the page
 
ADezii
post Apr 26 2019, 07:47 AM
Post#7



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. You can place the following Code in the Click() Event of your Command Button:
    CODE
    Private Sub CommandButton1_Click()
    '********** USER DEFINED **********
    Const conPASSWORD = "qWehgY76"
    '**********************************
    Dim strPwd As String

    strPwd = InputBox$("Enter Password to access this feature", "Password Prompt")

    'Nothing entered or Cancel has been hit
    If strPwd = "" Then Exit Sub

    'Makes sure the Password comparison is case sensitive
    If StrComp(strPwd, conPASSWORD, vbBinaryCompare) <> 0 Then
      MsgBox "Invalid Password entered", vbExclamation, "Invalid Password"
        Exit Sub
    End If

    'If you get to this point, a Valid Password has been entered. Continue
    'normal processing at this point.
    End Sub
  2. There are two very important points to remember:
    1. IMHO, Password comparisons should be case-sensitive as I have indicated above ('abcDe' <> 'adcde').
    2. The Code is absolutely useless unless you Password Protect the VBA Project (select Lock project for viewing and enter a Password to View Project properties). This will prevent a User from seeing your Code. You can find these options under: Tools/VBA Project properties in a Code Window.
  3. Hope this helps.
Go to the top of the page
 
pwbrownapcom
post Apr 26 2019, 10:28 AM
Post#8



Posts: 9
Joined: 5-March 19



Thanks Debaser! Working great! Making some other "tweaks" but making progress!
ADezii, thank you as well. The user name criteria works for now, but as we all know, as soon as this gets out, others will "need" access and betting the number of people will "explode" so making a copy of the file and going to see what I can do for using a password on the macro. That way we will be covered either way.


Again, thanks to all! Certainly glad I found this site and that I am going to send the file out for review before we need to start to use it so that I know which way will work best for this need, but regardless, learned a lot from all responses!
This post has been edited by pwbrownapcom: Apr 26 2019, 10:33 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 09:28 PM