Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for making worksheets visible.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for making worksheets visible.
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 "LDanix" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for making worksheets visible.
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. "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "JulieD" wrote in message ... 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 "LDanix" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for making worksheets visible.
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. "LDanix" wrote: 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. "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "JulieD" wrote in message ... 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 "LDanix" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for making worksheets visible.
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. -- Don Guillett SalesAid Software "LDanix" wrote in message ... 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. "LDanix" wrote: 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. "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "JulieD" wrote in message ... 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 "LDanix" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for making worksheets visible.
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 "Don Guillett" wrote: 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. -- Don Guillett SalesAid Software "LDanix" wrote in message ... 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. "LDanix" wrote: 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. "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "JulieD" wrote in message ... 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 "LDanix" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for making worksheets visible.
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 "JulieD" wrote in message ... 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 "LDanix" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a form visible | Excel Discussion (Misc queries) | |||
Running a macro for all visible worksheets in a workbook | Excel Discussion (Misc queries) | |||
making only specified columns visible | Excel Discussion (Misc queries) | |||
making a group visible (or not...) | Excel Programming | |||
Help: Macro to to sum across selected visible worksheets | Excel Programming |