Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Sheet By Codename | Excel Programming | |||
Codename passing | Excel Programming | |||
change sheet codename | Excel Programming | |||
Selecting a sheet by codename | Excel Programming | |||
Using sheet codename problems | Excel Programming |