Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question for Jim Thomlinson
Thank you for pointing out that I should use the code name - rather than tab
name - how would the following code be change to use code name - rather than tab name? Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "Input" And sht.Visible = True Then sht.PrintOut End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question for Jim Thomlinson
That code is just fine. At no point does it rely on the tab name of the
sheets in your workbook. In the excel object hierarchy there is the Worksheet object and the Worksheets collection (note the s in the latter object). What your code is doing is it is taking a worksheet object and using that to traverse the worksheets collection. At no time are you refering to the sheets by their tab name which is dangerous since user have access to change the tab names. -- HTH... Jim Thomlinson "Brad" wrote: Thank you for pointing out that I should use the code name - rather than tab name - how would the following code be change to use code name - rather than tab name? Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "Input" And sht.Visible = True Then sht.PrintOut End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question for Jim Thomlinson
Okay - I changed sheet1 which was the input sheet to shtInput
Then Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "shtInput" And sht.Visible = True Then sht.PrintOut End If Next End Sub Prints out the input page and the two output pages If I make the code Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < shtInput And sht.Visible = True Then sht.PrintOut End If Next End Sub I get an error message "Jim Thomlinson" wrote: That code is just fine. At no point does it rely on the tab name of the sheets in your workbook. In the excel object hierarchy there is the Worksheet object and the Worksheets collection (note the s in the latter object). What your code is doing is it is taking a worksheet object and using that to traverse the worksheets collection. At no time are you refering to the sheets by their tab name which is dangerous since user have access to change the tab names. -- HTH... Jim Thomlinson "Brad" wrote: Thank you for pointing out that I should use the code name - rather than tab name - how would the following code be change to use code name - rather than tab name? Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "Input" And sht.Visible = True Then sht.PrintOut End If Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question for Jim Thomlinson
that is because shtInput probably isn't the code name of the sheet with the
tab name of "ShtInput" go into the project explorer in the VBE (alt+F11). In the list worksheets, you will probably see Sheet3 (ShtInput) use whatever value I have portrayed here as Sheet3. -- Regards, Tom Ogilvy "Brad" wrote: Okay - I changed sheet1 which was the input sheet to shtInput Then Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "shtInput" And sht.Visible = True Then sht.PrintOut End If Next End Sub Prints out the input page and the two output pages If I make the code Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < shtInput And sht.Visible = True Then sht.PrintOut End If Next End Sub I get an error message "Jim Thomlinson" wrote: That code is just fine. At no point does it rely on the tab name of the sheets in your workbook. In the excel object hierarchy there is the Worksheet object and the Worksheets collection (note the s in the latter object). What your code is doing is it is taking a worksheet object and using that to traverse the worksheets collection. At no time are you refering to the sheets by their tab name which is dangerous since user have access to change the tab names. -- HTH... Jim Thomlinson "Brad" wrote: Thank you for pointing out that I should use the code name - rather than tab name - how would the following code be change to use code name - rather than tab name? Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "Input" And sht.Visible = True Then sht.PrintOut End If Next End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question for Jim Thomlinson
Tom,
Jim suggested that I go into the properties window in VBE and rename the worksheet from sheet1 to something that describes the tab more. I did not change the tab name, that remained the same. The reason that he suggested doing this the my macros, that I've set up, should used the code name not the tab name (because users can change the tab name). I was able to change the code name in the properties section - but having difficulties making the macro's work. Does this help??? "Tom Ogilvy" wrote: that is because shtInput probably isn't the code name of the sheet with the tab name of "ShtInput" go into the project explorer in the VBE (alt+F11). In the list worksheets, you will probably see Sheet3 (ShtInput) use whatever value I have portrayed here as Sheet3. -- Regards, Tom Ogilvy "Brad" wrote: Okay - I changed sheet1 which was the input sheet to shtInput Then Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "shtInput" And sht.Visible = True Then sht.PrintOut End If Next End Sub Prints out the input page and the two output pages If I make the code Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < shtInput And sht.Visible = True Then sht.PrintOut End If Next End Sub I get an error message "Jim Thomlinson" wrote: That code is just fine. At no point does it rely on the tab name of the sheets in your workbook. In the excel object hierarchy there is the Worksheet object and the Worksheets collection (note the s in the latter object). What your code is doing is it is taking a worksheet object and using that to traverse the worksheets collection. At no time are you refering to the sheets by their tab name which is dangerous since user have access to change the tab names. -- HTH... Jim Thomlinson "Brad" wrote: Thank you for pointing out that I should use the code name - rather than tab name - how would the following code be change to use code name - rather than tab name? Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "Input" And sht.Visible = True Then sht.PrintOut End If Next End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question for Jim Thomlinson
Give this a try...
Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < shtInput.Name And sht.Visible = True Then sht.PrintOut End If Next End Sub ShtInput refers directly to the sheet object. What you need to do is to check the name property of sht against the name property of shtInput... -- HTH... Jim Thomlinson "Brad" wrote: Okay - I changed sheet1 which was the input sheet to shtInput Then Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "shtInput" And sht.Visible = True Then sht.PrintOut End If Next End Sub Prints out the input page and the two output pages If I make the code Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < shtInput And sht.Visible = True Then sht.PrintOut End If Next End Sub I get an error message "Jim Thomlinson" wrote: That code is just fine. At no point does it rely on the tab name of the sheets in your workbook. In the excel object hierarchy there is the Worksheet object and the Worksheets collection (note the s in the latter object). What your code is doing is it is taking a worksheet object and using that to traverse the worksheets collection. At no time are you refering to the sheets by their tab name which is dangerous since user have access to change the tab names. -- HTH... Jim Thomlinson "Brad" wrote: Thank you for pointing out that I should use the code name - rather than tab name - how would the following code be change to use code name - rather than tab name? Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "Input" And sht.Visible = True Then sht.PrintOut End If Next End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question for Jim Thomlinson
Looks like Jim provided support for his suggestion.
-- Regards, Tom Ogilvy "Brad" wrote in message ... Tom, Jim suggested that I go into the properties window in VBE and rename the worksheet from sheet1 to something that describes the tab more. I did not change the tab name, that remained the same. The reason that he suggested doing this the my macros, that I've set up, should used the code name not the tab name (because users can change the tab name). I was able to change the code name in the properties section - but having difficulties making the macro's work. Does this help??? "Tom Ogilvy" wrote: that is because shtInput probably isn't the code name of the sheet with the tab name of "ShtInput" go into the project explorer in the VBE (alt+F11). In the list worksheets, you will probably see Sheet3 (ShtInput) use whatever value I have portrayed here as Sheet3. -- Regards, Tom Ogilvy "Brad" wrote: Okay - I changed sheet1 which was the input sheet to shtInput Then Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "shtInput" And sht.Visible = True Then sht.PrintOut End If Next End Sub Prints out the input page and the two output pages If I make the code Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < shtInput And sht.Visible = True Then sht.PrintOut End If Next End Sub I get an error message "Jim Thomlinson" wrote: That code is just fine. At no point does it rely on the tab name of the sheets in your workbook. In the excel object hierarchy there is the Worksheet object and the Worksheets collection (note the s in the latter object). What your code is doing is it is taking a worksheet object and using that to traverse the worksheets collection. At no time are you refering to the sheets by their tab name which is dangerous since user have access to change the tab names. -- HTH... Jim Thomlinson "Brad" wrote: Thank you for pointing out that I should use the code name - rather than tab name - how would the following code be change to use code name - rather than tab name? Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "Input" And sht.Visible = True Then sht.PrintOut End If Next End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question for Jim Thomlinson
And the idiot of the hour award goes to me.
Thanks Jim, for being kind to a rookie - But I should have known better. "Jim Thomlinson" wrote: Give this a try... Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < shtInput.Name And sht.Visible = True Then sht.PrintOut End If Next End Sub ShtInput refers directly to the sheet object. What you need to do is to check the name property of sht against the name property of shtInput... -- HTH... Jim Thomlinson "Brad" wrote: Okay - I changed sheet1 which was the input sheet to shtInput Then Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "shtInput" And sht.Visible = True Then sht.PrintOut End If Next End Sub Prints out the input page and the two output pages If I make the code Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < shtInput And sht.Visible = True Then sht.PrintOut End If Next End Sub I get an error message "Jim Thomlinson" wrote: That code is just fine. At no point does it rely on the tab name of the sheets in your workbook. In the excel object hierarchy there is the Worksheet object and the Worksheets collection (note the s in the latter object). What your code is doing is it is taking a worksheet object and using that to traverse the worksheets collection. At no time are you refering to the sheets by their tab name which is dangerous since user have access to change the tab names. -- HTH... Jim Thomlinson "Brad" wrote: Thank you for pointing out that I should use the code name - rather than tab name - how would the following code be change to use code name - rather than tab name? Sub Print_pages() Dim sht As Worksheet For Each sht In Worksheets If sht.Name < "Input" And sht.Visible = True Then sht.PrintOut End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|