Macro for making worksheets visible.

[Guest]

I have 12 agents that I manage. I want to create a workbook that includes
each person's vacation accrued and spent on separate worksheets.

I want to make all the worksheets invisible upon load of the workbook (I
figured that out). I want to give each agent access to their own worksheet
without being able to view the other agents.

I have each worksheet named as the agents' employee ID numbers. I want to
create a macro that will make visible a worksheet based on the employee ID
number an agent types in somewhere.

Thanks,
Luke

 

[JulieD]

Hi Luke

have a button on the front sheet called "click me" (or similar) with the
following code behind it

sub clickme()
dim eID as string

on error goto err_handle
eID=Inputbox("Please, enter your employee ID number","Log In")

Sheets(eID).visible = true
Sheets(eID).activate

exit sub

err_handle:
msgbox "Invalid number, please try again",vbCritical,"Wrong"

end sub

 

[Don Guillett]

something like this?
Sub unhide()
Sheets(InputBox("enter num")).Visible = True
End Sub
OR
Sub unhidea()
x = InputBox("your num")
Sheets(x).Visible = True
Application.Goto Sheets(x).Range("a1")
End Sub

 

[Guest]

I have put in the code exactly like I see it. Are there any special
characters that I should be using anywhere? I can't seem to get it to work.
I'm new to programming in VB.

 

[Guest]

I have been able to Step through the macro and it works. But when I click on
the button to activate the macro, nothing happens. I have made sure I am not
in Design Mode when clicking.

 

[Don Guillett]

If using EXACTLY as shown the OR would present a problem. Either comment it
out with ' in front. These are 2 different subs and your button should be
assigned to one. I do NOT like the control box buttons. I suggest a forms
button or a shape from the drawing toolbar. BTW, Julie provided an error
check so maybe use hers.

 

[Guest]

Hi Don!

I have copied and pasted the exact code below. This works using F8 and Shift
F8 to step through; however, I can't get it to work by clicking on the
button. I made sure that the button is set as Active in its properties, and I
was out of Design Mode when attempting to execute. Would the code be any
different for an image or shape (like you suggested)? Do the button
properties have to be set any specific way? Could there be an error in the
code that is preventing the macro from executing by clicking? :

Sub Logon()
Dim eID As String

On Error GoTo err_handle
eID = InputBox("Please enter your WWID number", "Log In")

Sheets(eID).Visible = True
Sheets(eID).Activate

Exit Sub

err_handle:
MsgBox "Invalid number, please try again", vbCritical, "Wrong"

End Sub

Private Sub LogOn_Click()

End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub CommandButton1_Click()

End Sub

 

[Paul Black]

Hi JulieD,

I have a Project that this would be VERY Useful for. I Ran your Code
and it Works Great. Just a Couple of Questions though.
(1) When you Enter the Wrong Id ( TAB Name ) it Drops Out and you have
to Re-Click the Button Again. Is there Any Way that it could go
Directly Back to the Enter ID Input Box After Clicking OK.
(2) If the File was to be Used in a Room with say 10 Users ( Shared
Workbook ), is there Any Way to Hide the Sheet TAB Names so Nobody
Else would be Able to see what the TAB Name ( Password ) is.

Thanks in Advance.
All the Best
Paul