Need help writing a VBA code excel

[LearningOnMyOwn]

I am a beginner and need things outlined very detailed.

I am creating a file for scheduling. In the User tab, in column A I have Admin User Names. I have a code written that in C4 "Username" cell it displays the username of the person. This is from the network. I want to write a code that will make the Users tab Very Hidden if their user name does not appear in the list in column A.

If A2:A1000 = C4 (Username) then Users tab is visible
If not then Users tab is Very Hidden

I will probably need help knowing which sheets to insert the codes.

 

[L. Howard]

Hi L O M O, welcome to the board.

Try this in the sheet module of the sheet with the usernames. (The same sheet with the C4 username display)

This relies on a sheet_change_event which I believe C4 will produce when the username is entered.

Change the sheet names to match exactly your sheet tab name. I am using Sheet2.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$C$4")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim uNme As String
uNme = Cells(4, 3)

If WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), uNme) = 0 Then

  Sheets("Sheet2").Visible = xlVeryHidden

End If

End Sub

To UN-Hide sheet2, use this. Copy to the ThisWorkbook sheet module and run it with a button or from Alt + f8 key action.

Sub v_UN_Hide()
'
Sheets("Sheet2").Visible = True
'
End Sub