Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing worksheet as an argument
Is there a way to pass a worksheet name as an argument? I want to
hide all of my worksheet tabs and i have created macro buttons to open the worksheets, but they have to be unhidden first. What I would like it to do is when I click the button.... unhide the worksheet i want to open, then show that one... then I would like to take that active sheet and brand it with oldSheet and then when they go back to main worksheet tab and click another button, to hide oldSheet and unhide the one they just clicked on and then show that one. The only way it seems logical to me is to pass the argument in each macro button, but it's not working. ANy Ideas as to where I'm going wrong? p.s. and would I have to make them all public sub to pass the info? -Marcus |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing worksheet as an argument
Declare the variable that you use for the name of the currently-open sheet as
a public variable: in an area above the first sub, type: Public MySheet as string Then when you open a sheet: let MySheet="EmployeeListing" Sheets(MySheet).Visible = True Then in your code when you go to the next sheet: if len(MySheet)0 then 'if there has not been a sheet open, you need to test for it this way sheets(MySheet).visible=false end if Let MySheet="NextSheet" sheets(MySheet).visible=true "Marcusdmc" wrote: Is there a way to pass a worksheet name as an argument? I want to hide all of my worksheet tabs and i have created macro buttons to open the worksheets, but they have to be unhidden first. What I would like it to do is when I click the button.... unhide the worksheet i want to open, then show that one... then I would like to take that active sheet and brand it with oldSheet and then when they go back to main worksheet tab and click another button, to hide oldSheet and unhide the one they just clicked on and then show that one. The only way it seems logical to me is to pass the argument in each macro button, but it's not working. ANy Ideas as to where I'm going wrong? p.s. and would I have to make them all public sub to pass the info? -Marcus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing worksheet as an argument
On Oct 30, 6:27 am, Mike H. wrote:
Declare the variable that you use for the name of the currently-open sheet as a public variable: in an area above the first sub, type: Public MySheet as string Then when you open a sheet: let MySheet="EmployeeListing" Sheets(MySheet).Visible = True Then in your code when you go to the next sheet: if len(MySheet)0 then 'if there has not been a sheet open, you need to test for it this way sheets(MySheet).visible=false end if Let MySheet="NextSheet" sheets(MySheet).visible=true "Marcusdmc" wrote: Is there a way to pass a worksheet name as an argument? I want to hide all of my worksheet tabs and i have created macro buttons to open the worksheets, but they have to be unhidden first. What I would like it to do is when I click the button.... unhide the worksheet i want to open, then show that one... then I would like to take that active sheet and brand it with oldSheet and then when they go back to main worksheet tab and click another button, to hide oldSheet and unhide the one they just clicked on and then show that one. The only way it seems logical to me is to pass the argument in each macro button, but it's not working. ANy Ideas as to where I'm going wrong? p.s. and would I have to make them all public sub to pass the info? -Marcus- Hide quoted text - - Show quoted text - Thank you very much for all of your help!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing worksheet as an argument
Sure. It took me a minute to figure out what was until I saw when I first
posted. Later.... "Marcusdmc" wrote: On Oct 30, 6:27 am, Mike H. wrote: Declare the variable that you use for the name of the currently-open sheet as a public variable: in an area above the first sub, type: Public MySheet as string Then when you open a sheet: let MySheet="EmployeeListing" Sheets(MySheet).Visible = True Then in your code when you go to the next sheet: if len(MySheet)0 then 'if there has not been a sheet open, you need to test for it this way sheets(MySheet).visible=false end if Let MySheet="NextSheet" sheets(MySheet).visible=true "Marcusdmc" wrote: Is there a way to pass a worksheet name as an argument? I want to hide all of my worksheet tabs and i have created macro buttons to open the worksheets, but they have to be unhidden first. What I would like it to do is when I click the button.... unhide the worksheet i want to open, then show that one... then I would like to take that active sheet and brand it with oldSheet and then when they go back to main worksheet tab and click another button, to hide oldSheet and unhide the one they just clicked on and then show that one. The only way it seems logical to me is to pass the argument in each macro button, but it's not working. ANy Ideas as to where I'm going wrong? p.s. and would I have to make them all public sub to pass the info? -Marcus- Hide quoted text - - Show quoted text - Thank you very much for all of your help!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a Control as an Argument | Excel Programming | |||
Passing a UDF as an argument to a UDF | Excel Discussion (Misc queries) | |||
Passing argument to another Sub | Excel Programming | |||
Passing Worksheet tab as an argument. | Excel Programming | |||
Passing range as argument | Excel Programming |