Forum Discussion

marvel2r's avatar
marvel2r
Copper Contributor
May 14, 2022

Excel Visual Basic help

Hello. Was wondering if you could help me with a workbook I'm trying to create. The purpose is to track employees with numbers; on the first sheet (Form) someone will scan their number, it will then ...
Excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    May 14, 2022

    marvel2r 

    Try

    Sub inout()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim barcode As String
    Dim rng As Range
    Dim rownumber As Long
    Set ws1 = Worksheets("Form")
    ' *** Change Sheet2 to the name of the other sheet ***
    Set ws2 = Worksheets("Sheet2")
    barcode = ws1.Range("B2").Value
    Set rng = ws2.Range("A:A").Find(What:=barcode, LookIn:=xlValues, LookAt:=xlWhole)
    If rng Is Nothing Then
        Set rng = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(RowOffset:=1)
        rng.Value = barcode
    End If
    rownumber = rng.Row
    Set rng = ws2.Cells(rownumber, ws2.Columns.Count).End(xlToLeft).Offset(ColumnOffset:=1)
    rng.Value = Now
    rng.NumberFormat = "m/d/yyyy h:mm AM/PM"
    ws1.Range("B2").ClearContents
End Sub
marvel2r's avatar
marvel2r
Copper Contributor

HansVogelaar Can I have two versions of this workbook? One where employees can only see/access sheet1 Form; and one where managers see/access both sheets?

HansVogelaar's avatar
HansVogelaar
MVP
May 14, 2022

marvel2r 

Do you really want two versions of the workbook, or one workbook where only some people can view the second sheet?

  • marvel2r's avatar
    marvel2r
    Copper Contributor
    May 14, 2022
    One workbook where only some can view second sheet
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 14, 2022

      marvel2r 

      In the Visual Basic Editor, double-click ThisWorkbook, then copy the following code into it:

      Private Sub Workbook_Open()
    Worksheets("Form").Activate
    Worksheets("Sheet2").Visible = xlSheetVeryHidden
End Sub

      Change Sheet2 to the name of the other sheet.

       This hides the second sheet, while it can only be unhidden using VBA,not from the Excel interface.

      In the same module that contains the inout macro, create a new macro:

      Sub UnhideSheet()
    If InputBox("Enter password", "Activate other sheet") = "mypassword" Then
        With Worksheets("Sheet2")
            .Visible = xlSheetVisible
            .Activate
        End With
    Else
        MsgBox "Incorrect password. Access denied.", vbCritical
    End If
End Sub

      Change mypassword to the password you want to use (it should be more secure than mypassword!), and change Sheet2 to the name of the other sheet.

      Switch back to Excel.

      Create a new button on the Form sheet, and assign the UnhideSheet macro to it. Set its caption to "Show other sheet" or something like that.

       

      Tell the managers who will need to access the second sheet the password,

       

      Warning: VBA-savvy users will be able to view the code and thus the password.

      If you would like to prevent that, you have to lock the VBA project for viewing:

      • marvel2r's avatar
        marvel2r
        Copper Contributor
        May 14, 2022
        Thank you so much!

Resources