Passing sheet (using codename) to separate sub as optional paramet
 

I have a large, convoluted workbook. With some of my subs that bring in raw
data from other files, I want to unhide the sheet where that data is pasted
just so I can review it (without unhiding all sheets). There are also some
subs where I want to unhide every worksheet in the workbook.

So, I wrote a sub that I thought would handle both, but I'm getting errors
when I try to call it, and it also fails when I place the cursor inside the
sub and try to run it directly.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<codename, but irrelevant -just determines if a sheet was passed to
the sub
If (IndSht.Name) < "" Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

When I try to run the code above, it pops up a dialogue box with a list of
macros to run, rather than just running this one as I would have expected.

Also, when I try to call the sub (below) I get a run time error 438, object
doesn't support this property or method

Sub ALoad_FinRTs()
ShowAllSheets (Sheet15)
'do stuff
End Sub

I think I have more than one problem here, but I'm not sure what the problem
is, so I can't experiment with solutions...

Thanks!
Keith

Passing sheet (using codename) to separate sub as optional paramet
 

You cannot run a sub with an argument, even an optional one, you need a sub
that calls it and run that.

But your code is flawed anyway, if you don't pass a sheet then IndSht object
is nothing, so you cannot test name. Better to test for Nothing

If Not IndSht Is Nothing Then
IndSht.Visible = xlSheetVisible


--

HTH

Bob

"ker_01" wrote in message
...
I have a large, convoluted workbook. With some of my subs that bring in raw
data from other files, I want to unhide the sheet where that data is
pasted
just so I can review it (without unhiding all sheets). There are also some
subs where I want to unhide every worksheet in the workbook.

So, I wrote a sub that I thought would handle both, but I'm getting errors
when I try to call it, and it also fails when I place the cursor inside
the
sub and try to run it directly.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<codename, but irrelevant -just determines if a sheet was passed to
the sub
If (IndSht.Name) < "" Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

When I try to run the code above, it pops up a dialogue box with a list of
macros to run, rather than just running this one as I would have expected.

Also, when I try to call the sub (below) I get a run time error 438,
object
doesn't support this property or method

Sub ALoad_FinRTs()
ShowAllSheets (Sheet15)
'do stuff
End Sub

I think I have more than one problem here, but I'm not sure what the
problem
is, so I can't experiment with solutions...

Thanks!
Keith

Passing sheet (using codename) to separate sub as optional paramet
 

to test it, you need to call it with another macro. Also modified one line:

Sub dk()
Call ShowAllSheets(Sheets("Sheet2")) 'This can be a problem area for
other users.
End Sub 'They can
get type mismatch errors here.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<codename, but irrelevant -just determines if a sheet was passed tothe
sub
If Not IndSht Is Nothing Then '<<<modified
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub



"ker_01" wrote in message
...
I have a large, convoluted workbook. With some of my subs that bring in raw
data from other files, I want to unhide the sheet where that data is
pasted
just so I can review it (without unhiding all sheets). There are also some
subs where I want to unhide every worksheet in the workbook.

So, I wrote a sub that I thought would handle both, but I'm getting errors
when I try to call it, and it also fails when I place the cursor inside
the
sub and try to run it directly.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<codename, but irrelevant -just determines if a sheet was passed to
the sub
If (IndSht.Name) < "" Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

When I try to run the code above, it pops up a dialogue box with a list of
macros to run, rather than just running this one as I would have expected.

Also, when I try to call the sub (below) I get a run time error 438,
object
doesn't support this property or method

Sub ALoad_FinRTs()
ShowAllSheets (Sheet15)
'do stuff
End Sub

I think I have more than one problem here, but I'm not sure what the
problem
is, so I can't experiment with solutions...

Thanks!
Keith

Passing sheet (using codename) to separate sub as optional paramet
 

Drop the ()'s in your procedu

Sub ALoad_FinRTs()
ShowAllSheets Sheet15
'do stuff
End Sub

Or add the Call keyword:

Sub ALoad_FinRTs()
Call ShowAllSheets(Sheet15)
'do stuff
End Sub

But I'd listen to Bob, too.


ker_01 wrote:

I have a large, convoluted workbook. With some of my subs that bring in raw
data from other files, I want to unhide the sheet where that data is pasted
just so I can review it (without unhiding all sheets). There are also some
subs where I want to unhide every worksheet in the workbook.

So, I wrote a sub that I thought would handle both, but I'm getting errors
when I try to call it, and it also fails when I place the cursor inside the
sub and try to run it directly.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<codename, but irrelevant -just determines if a sheet was passed to
the sub
If (IndSht.Name) < "" Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

When I try to run the code above, it pops up a dialogue box with a list of
macros to run, rather than just running this one as I would have expected.

Also, when I try to call the sub (below) I get a run time error 438, object
doesn't support this property or method

Sub ALoad_FinRTs()
ShowAllSheets (Sheet15)
'do stuff
End Sub

I think I have more than one problem here, but I'm not sure what the problem
is, so I can't experiment with solutions...

Thanks!
Keith

--

Dave Peterson

Passing sheet (using codename) to separate sub as optional par
 

Thank you Bob and JLG; I have incorporated the "not nothing" line. I still am
unable to test it due to my second issue with passing the procedure the
codename of the sheet; I'm still getting a RTE 438. Any additional thoughts
would be greatly appreciated.

I prefer to always use codename because my users may change the sheetname,
and I don't want to risk having that break the code.

Sub testShowSheets()
ShowAllSheets (Sheet14) '<--RTE 438
End Sub

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
If Not IndSht Is Nothing Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

Just for comparison, I did try the sheetname as well, but got the same RTE 438

Sub testShowSheets()
ShowAllSheets (Sheets("Div 14")) '<--RTE 438
End Sub

Thank you,
Keith



"Bob Phillips" wrote:

You cannot run a sub with an argument, even an optional one, you need a sub
that calls it and run that.

But your code is flawed anyway, if you don't pass a sheet then IndSht object
is nothing, so you cannot test name. Better to test for Nothing

If Not IndSht Is Nothing Then
IndSht.Visible = xlSheetVisible


--

HTH

Bob

"ker_01" wrote in message
...
I have a large, convoluted workbook. With some of my subs that bring in raw
data from other files, I want to unhide the sheet where that data is
pasted
just so I can review it (without unhiding all sheets). There are also some
subs where I want to unhide every worksheet in the workbook.

So, I wrote a sub that I thought would handle both, but I'm getting errors
when I try to call it, and it also fails when I place the cursor inside
the
sub and try to run it directly.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<codename, but irrelevant -just determines if a sheet was passed to
the sub
If (IndSht.Name) < "" Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

When I try to run the code above, it pops up a dialogue box with a list of
macros to run, rather than just running this one as I would have expected.

Also, when I try to call the sub (below) I get a run time error 438,
object
doesn't support this property or method

Sub ALoad_FinRTs()
ShowAllSheets (Sheet15)
'do stuff
End Sub

I think I have more than one problem here, but I'm not sure what the
problem
is, so I can't experiment with solutions...

Thanks!
Keith


.

Passing sheet (using codename) to separate sub as optional par
 

Woohoo - success!

After careful rereading of the replies, I saw the use of the 'Call' keyword,
which isn't something I've used before (I can't recall ever passing
parameters to a sub before, I've always just used global variables).

It is now working as expected/hoped.
:)

Thanks again,
Keith

"ker_01" wrote:

Thank you Bob and JLG; I have incorporated the "not nothing" line. I still am
unable to test it due to my second issue with passing the procedure the
codename of the sheet; I'm still getting a RTE 438. Any additional thoughts
would be greatly appreciated.

I prefer to always use codename because my users may change the sheetname,
and I don't want to risk having that break the code.

Sub testShowSheets()
ShowAllSheets (Sheet14) '<--RTE 438
End Sub

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
If Not IndSht Is Nothing Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

Just for comparison, I did try the sheetname as well, but got the same RTE 438

Sub testShowSheets()
ShowAllSheets (Sheets("Div 14")) '<--RTE 438
End Sub

Thank you,
Keith



"Bob Phillips" wrote:

You cannot run a sub with an argument, even an optional one, you need a sub
that calls it and run that.

But your code is flawed anyway, if you don't pass a sheet then IndSht object
is nothing, so you cannot test name. Better to test for Nothing

If Not IndSht Is Nothing Then
IndSht.Visible = xlSheetVisible


--

HTH

Bob

"ker_01" wrote in message
...
I have a large, convoluted workbook. With some of my subs that bring in raw
data from other files, I want to unhide the sheet where that data is
pasted
just so I can review it (without unhiding all sheets). There are also some
subs where I want to unhide every worksheet in the workbook.

So, I wrote a sub that I thought would handle both, but I'm getting errors
when I try to call it, and it also fails when I place the cursor inside
the
sub and try to run it directly.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<codename, but irrelevant -just determines if a sheet was passed to
the sub
If (IndSht.Name) < "" Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

When I try to run the code above, it pops up a dialogue box with a list of
macros to run, rather than just running this one as I would have expected.

Also, when I try to call the sub (below) I get a run time error 438,
object
doesn't support this property or method

Sub ALoad_FinRTs()
ShowAllSheets (Sheet15)
'do stuff
End Sub

I think I have more than one problem here, but I'm not sure what the
problem
is, so I can't experiment with solutions...

Thanks!
Keith


.

Passing sheet (using codename) to separate sub as optional par
 

Interesting, "Call" is only needed if I put the parameters in parenthesis? It
works, but I don't comprehend the relationship (or reasons when to use) Call
vs the parens.

But I'd listen to Bob, too.

I always do :) ...and to you and the other experts in this forum too- I've
never had a formal class in VBA, but it is amazing how much I can do just
based on what I've learned here (and hours of hitting my head on my keyboard
while trying random syntax ;-)

Best,
Keith

"Dave Peterson" wrote:

Drop the ()'s in your procedu

Sub ALoad_FinRTs()
ShowAllSheets Sheet15
'do stuff
End Sub

Or add the Call keyword:

Sub ALoad_FinRTs()
Call ShowAllSheets(Sheet15)
'do stuff
End Sub

But I'd listen to Bob, too.


ker_01 wrote:

I have a large, convoluted workbook. With some of my subs that bring in raw
data from other files, I want to unhide the sheet where that data is pasted
just so I can review it (without unhiding all sheets). There are also some
subs where I want to unhide every worksheet in the workbook.

So, I wrote a sub that I thought would handle both, but I'm getting errors
when I try to call it, and it also fails when I place the cursor inside the
sub and try to run it directly.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<codename, but irrelevant -just determines if a sheet was passed to
the sub
If (IndSht.Name) < "" Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

When I try to run the code above, it pops up a dialogue box with a list of
macros to run, rather than just running this one as I would have expected.

Also, when I try to call the sub (below) I get a run time error 438, object
doesn't support this property or method

Sub ALoad_FinRTs()
ShowAllSheets (Sheet15)
'do stuff
End Sub

I think I have more than one problem here, but I'm not sure what the problem
is, so I can't experiment with solutions...

Thanks!
Keith

--

Dave Peterson
.

Passing sheet (using codename) to separate sub as optional par
 

If you don't use call, you may not need the ()'s. Excel will essentially try
evaluate the "sheet15" in this line:

ShowAllSheets(Sheet15)

But there really isn't any way to evaluate a sheet.

If you use Call, you have to use ()'s. Excel won't evaluate it, because it
knows you're passing something to the Call statement.

You use the ()'s kind of like when you're calling a function/returning a value:

msgbox "hi"

or

dim resp as long
resp = msgbox(prompt:="hi",buttons:=vbyesno)
msgbox resp



ker_01 wrote:

Interesting, "Call" is only needed if I put the parameters in parenthesis? It
works, but I don't comprehend the relationship (or reasons when to use) Call
vs the parens.

But I'd listen to Bob, too.

I always do :) ...and to you and the other experts in this forum too- I've
never had a formal class in VBA, but it is amazing how much I can do just
based on what I've learned here (and hours of hitting my head on my keyboard
while trying random syntax ;-)

Best,
Keith

"Dave Peterson" wrote:

Drop the ()'s in your procedu

Sub ALoad_FinRTs()
ShowAllSheets Sheet15
'do stuff
End Sub

Or add the Call keyword:

Sub ALoad_FinRTs()
Call ShowAllSheets(Sheet15)
'do stuff
End Sub

But I'd listen to Bob, too.


ker_01 wrote:

I have a large, convoluted workbook. With some of my subs that bring in raw
data from other files, I want to unhide the sheet where that data is pasted
just so I can review it (without unhiding all sheets). There are also some
subs where I want to unhide every worksheet in the workbook.

So, I wrote a sub that I thought would handle both, but I'm getting errors
when I try to call it, and it also fails when I place the cursor inside the
sub and try to run it directly.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<codename, but irrelevant -just determines if a sheet was passed to
the sub
If (IndSht.Name) < "" Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

When I try to run the code above, it pops up a dialogue box with a list of
macros to run, rather than just running this one as I would have expected.

Also, when I try to call the sub (below) I get a run time error 438, object
doesn't support this property or method

Sub ALoad_FinRTs()
ShowAllSheets (Sheet15)
'do stuff
End Sub

I think I have more than one problem here, but I'm not sure what the problem
is, so I can't experiment with solutions...

Thanks!
Keith

--

Dave Peterson
.


--

Dave Peterson