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

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

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!!!

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!!!