Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
Hi All,
Using Excel2003 with WinXP. Still learning VBA... Worksheet property has two names, one in brackets; call it CodeName and the other without brackets call it TabName. I have edited the CodeName to read "K1, K2, ... K10". Some sheets are very hidden, others appear as needed when macro button is pressed. On some sheets the user can rename the TabName. Thus some of my macros will call on procedures to select and show appro. sheets and to very hide others etc. My codes: Sub ShowShK2() Dim ShCodeName as String ShCodeName = "K2" ShCodeName.Visible = xlSheetVisible '/ i get invalid qualifer error '/ if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Call HideOtShs(ShCodeName) '/ i get subscript out of range error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" End Sub Sub HideOtShs(ShCodeName) '/ always make sure there is 1 sht thats visible bef calling this sub Dim oWkSh As Worksheet For Each oWkSh In ThisWorkbook.Worksheets If oWkSh.CodeName = ShCodeName Then oWkSh.Visible = -1 Else On Error Resume Next oWkSh.Visible = 2 End If Next oWkSh End Sub My understanding: 1. The CodeName cannot be programmatically changed; only by editing thru the properties window. ie they must be prenamed or use default name. 2. One sheet must be visible at all times. 3. Workbook protection must be off but worksheet protection can be on to manipulate names and visible properties. Help: Not sure why i am getting the errors in my coding. Your help and reasonings is much appreciated. God bless. Rgds from Zambia "The Real Africa" KZ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
Hi
1. Worksheets are visible, not their names, so you need Worksheets(ShCodeName).Visible = xlSheetVisible ' / i get invalid qualifer error 2. if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Do you have a sheet called "K2" in the active workbook? I would also use Sheets(ShCodeName).Visible = TRUE rahter than xlSheetVisible regards Paul On Mar 23, 9:32 am, "Kieranz" wrote: Hi All, Using Excel2003 with WinXP. Still learning VBA... Worksheet property has two names, one in brackets; call it CodeName and the other without brackets call it TabName. I have edited the CodeName to read "K1, K2, ... K10". Some sheets are very hidden, others appear as needed when macro button is pressed. On some sheets the user can rename the TabName. Thus some of my macros will call on procedures to select and show appro. sheets and to very hide others etc. My codes: Sub ShowShK2() Dim ShCodeName as String ShCodeName = "K2" ShCodeName.Visible = xlSheetVisible '/ i get invalid qualifer error '/ if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Call HideOtShs(ShCodeName) '/ i get subscript out of range error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" End Sub Sub HideOtShs(ShCodeName) '/ always make sure there is 1 sht thats visible bef calling this sub Dim oWkSh As Worksheet For Each oWkSh In ThisWorkbook.Worksheets If oWkSh.CodeName = ShCodeName Then oWkSh.Visible = -1 Else On Error Resume Next oWkSh.Visible = 2 End If Next oWkSh End Sub My understanding: 1. The CodeName cannot be programmatically changed; only by editing thru the properties window. ie they must be prenamed or use default name. 2. One sheet must be visible at all times. 3. Workbook protection must be off but worksheet protection can be on to manipulate names and visible properties. Help: Not sure why i am getting the errors in my coding. Your help and reasonings is much appreciated. God bless. Rgds from Zambia "The Real Africa" KZ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
On Mar 23, 11:44 am, wrote:
Hi 1. Worksheets are visible, not their names, so you need Worksheets(ShCodeName).Visible = xlSheetVisible ' / i get invalid qualifer error 2. if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Do you have a sheet called "K2" in the active workbook? I would also use Sheets(ShCodeName).Visible = TRUE rahter than xlSheetVisible regards Paul On Mar 23, 9:32 am, "Kieranz" wrote: Hi All, Using Excel2003 with WinXP. Still learning VBA... Worksheet property has two names, one in brackets; call it CodeName and the other without brackets call it TabName. I have edited the CodeName to read "K1, K2, ... K10". Some sheets are very hidden, others appear as needed when macro button is pressed. On some sheets the user can rename the TabName. Thus some of my macros will call on procedures to select and show appro. sheets and to very hide others etc. My codes: Sub ShowShK2() Dim ShCodeName as String ShCodeName = "K2" ShCodeName.Visible = xlSheetVisible '/ i get invalid qualifer error '/ if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Call HideOtShs(ShCodeName) '/ i get subscript out of range error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" End Sub Sub HideOtShs(ShCodeName) '/ always make sure there is 1 sht thats visible bef calling this sub Dim oWkSh As Worksheet For Each oWkSh In ThisWorkbook.Worksheets If oWkSh.CodeName = ShCodeName Then oWkSh.Visible = -1 Else On Error Resume Next oWkSh.Visible = 2 End If Next oWkSh End Sub My understanding: 1. The CodeName cannot be programmatically changed; only by editing thru the properties window. ie they must be prenamed or use default name. 2. One sheet must be visible at all times. 3. Workbook protection must be off but worksheet protection can be on to manipulate names and visible properties. Help: Not sure why i am getting the errors in my coding. Your help and reasonings is much appreciated. God bless. Rgds from Zambia "The Real Africa" KZ Paul, I still get subscript out of range error. Its something to do with the variable. If i remove all to do with variable ie K2.visible = true it works. I am lost. Rgds K |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
You can't refer to codename as a variable like that. Use
Sub ShowShK2() Dim ShCodeName as String K2.Visible = xlSheetVisible Application.Goto K2.Range("A1"), True K2.Name = "Inc2" End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message oups.com... On Mar 23, 11:44 am, wrote: Hi 1. Worksheets are visible, not their names, so you need Worksheets(ShCodeName).Visible = xlSheetVisible ' / i get invalid qualifer error 2. if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Do you have a sheet called "K2" in the active workbook? I would also use Sheets(ShCodeName).Visible = TRUE rahter than xlSheetVisible regards Paul On Mar 23, 9:32 am, "Kieranz" wrote: Hi All, Using Excel2003 with WinXP. Still learning VBA... Worksheet property has two names, one in brackets; call it CodeName and the other without brackets call it TabName. I have edited the CodeName to read "K1, K2, ... K10". Some sheets are very hidden, others appear as needed when macro button is pressed. On some sheets the user can rename the TabName. Thus some of my macros will call on procedures to select and show appro. sheets and to very hide others etc. My codes: Sub ShowShK2() Dim ShCodeName as String ShCodeName = "K2" ShCodeName.Visible = xlSheetVisible '/ i get invalid qualifer error '/ if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Call HideOtShs(ShCodeName) '/ i get subscript out of range error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" End Sub Sub HideOtShs(ShCodeName) '/ always make sure there is 1 sht thats visible bef calling this sub Dim oWkSh As Worksheet For Each oWkSh In ThisWorkbook.Worksheets If oWkSh.CodeName = ShCodeName Then oWkSh.Visible = -1 Else On Error Resume Next oWkSh.Visible = 2 End If Next oWkSh End Sub My understanding: 1. The CodeName cannot be programmatically changed; only by editing thru the properties window. ie they must be prenamed or use default name. 2. One sheet must be visible at all times. 3. Workbook protection must be off but worksheet protection can be on to manipulate names and visible properties. Help: Not sure why i am getting the errors in my coding. Your help and reasonings is much appreciated. God bless. Rgds from Zambia "The Real Africa" KZ Paul, I still get subscript out of range error. Its something to do with the variable. If i remove all to do with variable ie K2.visible = true it works. I am lost. Rgds K |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
On Mar 23, 2:15 pm, "Bob Phillips" wrote:
You can't refer to codename as a variable like that. Use Sub ShowShK2() Dim ShCodeName as String K2.Visible = xlSheetVisible Application.Goto K2.Range("A1"), True K2.Name = "Inc2" End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message oups.com... On Mar 23, 11:44 am, wrote: Hi 1. Worksheets are visible, not their names, so you need Worksheets(ShCodeName).Visible = xlSheetVisible ' / i get invalid qualifer error 2. if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Do you have a sheet called "K2" in the active workbook? I would also use Sheets(ShCodeName).Visible = TRUE rahter than xlSheetVisible regards Paul On Mar 23, 9:32 am, "Kieranz" wrote: Hi All, Using Excel2003 with WinXP. Still learning VBA... Worksheet property has two names, one in brackets; call it CodeName and the other without brackets call it TabName. I have edited the CodeName to read "K1, K2, ... K10". Some sheets are very hidden, others appear as needed when macro button is pressed. On some sheets the user can rename the TabName. Thus some of my macros will call on procedures to select and show appro. sheets and to very hide others etc. My codes: Sub ShowShK2() Dim ShCodeName as String ShCodeName = "K2" ShCodeName.Visible = xlSheetVisible '/ i get invalid qualifer error '/ if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Call HideOtShs(ShCodeName) '/ i get subscript out of range error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" End Sub Sub HideOtShs(ShCodeName) '/ always make sure there is 1 sht thats visible bef calling this sub Dim oWkSh As Worksheet For Each oWkSh In ThisWorkbook.Worksheets If oWkSh.CodeName = ShCodeName Then oWkSh.Visible = -1 Else On Error Resume Next oWkSh.Visible = 2 End If Next oWkSh End Sub My understanding: 1. The CodeName cannot be programmatically changed; only by editing thru the properties window. ie they must be prenamed or use default name. 2. One sheet must be visible at all times. 3. Workbook protection must be off but worksheet protection can be on to manipulate names and visible properties. Help: Not sure why i am getting the errors in my coding. Your help and reasonings is much appreciated. God bless. Rgds from Zambia "The Real Africa" KZ Paul, I still get subscript out of range error. Its something to do with the variable. If i remove all to do with variable ie K2.visible = true it works. I am lost. Rgds K Hello Bob Your sub is what i orginally had and it did work but quickly ran into problem. The problem was if a user renamed the sheet (called TabName in my scenario) i will not have any clue as to name and position of the sheet. So using the sheet property window i can atleast prename the CodeName which i are called eg K1, K2 etc i was hoping to control hide and unhide. Also i need to use a variable for the CodeName so that i can pass on to the my other sub for hiding the other sheets. See my first msg. I hope you follow what i am saying. Its something to do with the variable, maybe it should be WkSht as Worksheet, my grey cells are getting jammed. Your help really appreciated. Rgds K |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
You can get at it like so
Worksheets(ActiveWorkbook.VBProject.VBComponents(S hCodeName).Properties("Name").Value).Visible = xlSheetVisible -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message oups.com... On Mar 23, 2:15 pm, "Bob Phillips" wrote: You can't refer to codename as a variable like that. Use Sub ShowShK2() Dim ShCodeName as String K2.Visible = xlSheetVisible Application.Goto K2.Range("A1"), True K2.Name = "Inc2" End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message oups.com... On Mar 23, 11:44 am, wrote: Hi 1. Worksheets are visible, not their names, so you need Worksheets(ShCodeName).Visible = xlSheetVisible ' / i get invalid qualifer error 2. if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Do you have a sheet called "K2" in the active workbook? I would also use Sheets(ShCodeName).Visible = TRUE rahter than xlSheetVisible regards Paul On Mar 23, 9:32 am, "Kieranz" wrote: Hi All, Using Excel2003 with WinXP. Still learning VBA... Worksheet property has two names, one in brackets; call it CodeName and the other without brackets call it TabName. I have edited the CodeName to read "K1, K2, ... K10". Some sheets are very hidden, others appear as needed when macro button is pressed. On some sheets the user can rename the TabName. Thus some of my macros will call on procedures to select and show appro. sheets and to very hide others etc. My codes: Sub ShowShK2() Dim ShCodeName as String ShCodeName = "K2" ShCodeName.Visible = xlSheetVisible '/ i get invalid qualifer error '/ if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Call HideOtShs(ShCodeName) '/ i get subscript out of range error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" End Sub Sub HideOtShs(ShCodeName) '/ always make sure there is 1 sht thats visible bef calling this sub Dim oWkSh As Worksheet For Each oWkSh In ThisWorkbook.Worksheets If oWkSh.CodeName = ShCodeName Then oWkSh.Visible = -1 Else On Error Resume Next oWkSh.Visible = 2 End If Next oWkSh End Sub My understanding: 1. The CodeName cannot be programmatically changed; only by editing thru the properties window. ie they must be prenamed or use default name. 2. One sheet must be visible at all times. 3. Workbook protection must be off but worksheet protection can be on to manipulate names and visible properties. Help: Not sure why i am getting the errors in my coding. Your help and reasonings is much appreciated. God bless. Rgds from Zambia "The Real Africa" KZ Paul, I still get subscript out of range error. Its something to do with the variable. If i remove all to do with variable ie K2.visible = true it works. I am lost. Rgds K Hello Bob Your sub is what i orginally had and it did work but quickly ran into problem. The problem was if a user renamed the sheet (called TabName in my scenario) i will not have any clue as to name and position of the sheet. So using the sheet property window i can atleast prename the CodeName which i are called eg K1, K2 etc i was hoping to control hide and unhide. Also i need to use a variable for the CodeName so that i can pass on to the my other sub for hiding the other sheets. See my first msg. I hope you follow what i am saying. Its something to do with the variable, maybe it should be WkSht as Worksheet, my grey cells are getting jammed. Your help really appreciated. Rgds K |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
On Mar 23, 3:20 pm, "Bob Phillips" wrote:
You can get at it like so Worksheets(ActiveWorkbook.VBProject.VBComponents(S hCodeName).Properties("Name").Value).Visible = xlSheetVisible -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message oups.com... On Mar 23, 2:15 pm, "Bob Phillips" wrote: You can't refer to codename as a variable like that. Use Sub ShowShK2() Dim ShCodeName as String K2.Visible = xlSheetVisible Application.Goto K2.Range("A1"), True K2.Name = "Inc2" End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message groups.com... On Mar 23, 11:44 am, wrote: Hi 1. Worksheets are visible, not their names, so you need Worksheets(ShCodeName).Visible = xlSheetVisible ' / i get invalid qualifer error 2. if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Do you have a sheet called "K2" in the active workbook? I would also use Sheets(ShCodeName).Visible = TRUE rahter than xlSheetVisible regards Paul On Mar 23, 9:32 am, "Kieranz" wrote: Hi All, Using Excel2003 with WinXP. Still learning VBA... Worksheet property has two names, one in brackets; call it CodeName and the other without brackets call it TabName. I have edited the CodeName to read "K1, K2, ... K10". Some sheets are very hidden, others appear as needed when macro button is pressed. On some sheets the user can rename the TabName. Thus some of my macros will call on procedures to select and show appro. sheets and to very hide others etc. My codes: Sub ShowShK2() Dim ShCodeName as String ShCodeName = "K2" ShCodeName.Visible = xlSheetVisible '/ i get invalid qualifer error '/ if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Call HideOtShs(ShCodeName) '/ i get subscript out of range error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" End Sub Sub HideOtShs(ShCodeName) '/ always make sure there is 1 sht thats visible bef calling this sub Dim oWkSh As Worksheet For Each oWkSh In ThisWorkbook.Worksheets If oWkSh.CodeName = ShCodeName Then oWkSh.Visible = -1 Else On Error Resume Next oWkSh.Visible = 2 End If Next oWkSh End Sub My understanding: 1. The CodeName cannot be programmatically changed; only by editing thru the properties window. ie they must be prenamed or use default name. 2. One sheet must be visible at all times. 3. Workbook protection must be off but worksheet protection can be on to manipulate names and visible properties. Help: Not sure why i am getting the errors in my coding. Your help and reasonings is much appreciated. God bless. Rgds from Zambia "The Real Africa" KZ Paul, I still get subscript out of range error. Its something to do with the variable. If i remove all to do with variable ie K2.visible = true it works. I am lost. Rgds K Hello Bob Your sub is what i orginally had and it did work but quickly ran into problem. The problem was if a user renamed the sheet (called TabName in my scenario) i will not have any clue as to name and position of the sheet. So using the sheet property window i can atleast prename the CodeName which i are called eg K1, K2 etc i was hoping to control hide and unhide. Also i need to use a variable for the CodeName so that i can pass on to the my other sub for hiding the other sheets. See my first msg. I hope you follow what i am saying. Its something to do with the variable, maybe it should be WkSht as Worksheet, my grey cells are getting jammed. Your help really appreciated. Rgds K Bob, Many thanks, worked like a charm not sure why, read up quickly on Chip Pearsons site via threads. Will study more over the weekend. However, sorry not letting u go lightly... When the code returned back from the called sub i got the following errors on both the code lines '/ i get "subscript out of range" error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" Do i have to do a Set xxx not sure what and how. Thanks again Rgds K |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
As I said previously, you cannot get at the worksheet via a codename
variable in that way, you are still trying that. What my code was doing was getting the sheet name from the code name, via your variable, and using that. I would set a variable to the sheet and use that throughout Dim mySheet As Worksheet Set mySheet = Worksheets(ActiveWorkbook.VBProject.VBComponents(S hCodeName) _ .Properties("Name").Value) mySheet.Visible = xlSheetVisible Application.Goto mySheet.Range("A1") mySheet.Name = "Inc2" -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message ups.com... On Mar 23, 3:20 pm, "Bob Phillips" wrote: You can get at it like so Worksheets(ActiveWorkbook.VBProject.VBComponents(S hCodeName).Properties("Name").Value).Visible = xlSheetVisible -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message oups.com... Bob, Many thanks, worked like a charm not sure why, read up quickly on Chip Pearsons site via threads. Will study more over the weekend. However, sorry not letting u go lightly... When the code returned back from the called sub i got the following errors on both the code lines '/ i get "subscript out of range" error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" Do i have to do a Set xxx not sure what and how. Thanks again Rgds K |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
If you try to hide sheets in the wrong order, it can fail--one sheet has to be
visible at all times. Sub HideOtShs(ShCodeName) Dim oWkSh As Worksheet dim Foundit as boolean foundit = false 'loop once to make sure a sheet is visible For Each oWkSh In ThisWorkbook.Worksheets If lcase(oWkSh.CodeName) = lcase(ShCodeName) Then oWkSh.Visible = xlsheetvisible 'I like the constants! foundit = true exit for end if next owksh if foundit=true then For Each oWkSh In ThisWorkbook.Worksheets 'hide the rest If lcase(oWkSh.CodeName) = lcase(ShCodeName) Then 'do nothing else oWkSh.Visible = xlsheetveryhidden end if next owksh end if end sub ======= I'd loop through the code names. There are certain circumstances that this can fail (adding a new sheet can confuse the codenames--when the VBE hasn't been open, IIRC). But you can try this. It worked when I stepped through it and if the code window was open--but it failed other times. Option Explicit Function HideByCodeName(myCodeName As String, _ Optional Wkbk As Workbook) As Boolean 'with a reference to Visual Basic for application Extensibility x.x 'you can use VBComponent 'without the reference, use Object Dim VBC As Object 'VBComponent Dim myShtName As String If Wkbk Is Nothing Then Set Wkbk = ActiveWorkbook End If HideByCodeName = False Set VBC = Nothing On Error Resume Next Set VBC = Wkbk.VBProject.VBComponents(myCodeName) On Error GoTo 0 If VBC Is Nothing Then 'don't even try Else 'On Error Resume Next myShtName = VBC.Properties("Name") Wkbk.Sheets(myShtName).Visible = xlSheetHidden If Err.Number = 0 Then HideByCodeName = True End If On Error GoTo 0 End If End Function Sub testme() Dim myCodeName As String myCodeName = "Sheet1" MsgBox HideByCodeName(myCodeName) End Sub Kieranz wrote: On Mar 23, 2:15 pm, "Bob Phillips" wrote: You can't refer to codename as a variable like that. Use Sub ShowShK2() Dim ShCodeName as String K2.Visible = xlSheetVisible Application.Goto K2.Range("A1"), True K2.Name = "Inc2" End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message oups.com... On Mar 23, 11:44 am, wrote: Hi 1. Worksheets are visible, not their names, so you need Worksheets(ShCodeName).Visible = xlSheetVisible ' / i get invalid qualifer error 2. if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Do you have a sheet called "K2" in the active workbook? I would also use Sheets(ShCodeName).Visible = TRUE rahter than xlSheetVisible regards Paul On Mar 23, 9:32 am, "Kieranz" wrote: Hi All, Using Excel2003 with WinXP. Still learning VBA... Worksheet property has two names, one in brackets; call it CodeName and the other without brackets call it TabName. I have edited the CodeName to read "K1, K2, ... K10". Some sheets are very hidden, others appear as needed when macro button is pressed. On some sheets the user can rename the TabName. Thus some of my macros will call on procedures to select and show appro. sheets and to very hide others etc. My codes: Sub ShowShK2() Dim ShCodeName as String ShCodeName = "K2" ShCodeName.Visible = xlSheetVisible '/ i get invalid qualifer error '/ if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Call HideOtShs(ShCodeName) '/ i get subscript out of range error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" End Sub Sub HideOtShs(ShCodeName) '/ always make sure there is 1 sht thats visible bef calling this sub Dim oWkSh As Worksheet For Each oWkSh In ThisWorkbook.Worksheets If oWkSh.CodeName = ShCodeName Then oWkSh.Visible = -1 Else On Error Resume Next oWkSh.Visible = 2 End If Next oWkSh End Sub My understanding: 1. The CodeName cannot be programmatically changed; only by editing thru the properties window. ie they must be prenamed or use default name. 2. One sheet must be visible at all times. 3. Workbook protection must be off but worksheet protection can be on to manipulate names and visible properties. Help: Not sure why i am getting the errors in my coding. Your help and reasonings is much appreciated. God bless. Rgds from Zambia "The Real Africa" KZ Paul, I still get subscript out of range error. Its something to do with the variable. If i remove all to do with variable ie K2.visible = true it works. I am lost. Rgds K Hello Bob Your sub is what i orginally had and it did work but quickly ran into problem. The problem was if a user renamed the sheet (called TabName in my scenario) i will not have any clue as to name and position of the sheet. So using the sheet property window i can atleast prename the CodeName which i are called eg K1, K2 etc i was hoping to control hide and unhide. Also i need to use a variable for the CodeName so that i can pass on to the my other sub for hiding the other sheets. See my first msg. I hope you follow what i am saying. Its something to do with the variable, maybe it should be WkSht as Worksheet, my grey cells are getting jammed. Your help really appreciated. Rgds K -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
On Mar 23, 4:39 pm, Dave Peterson wrote:
If you try to hide sheets in the wrong order, it can fail--one sheet has to be visible at all times. Sub HideOtShs(ShCodeName) Dim oWkSh As Worksheet dim Foundit as boolean foundit = false 'loop once to make sure a sheet is visible For Each oWkSh In ThisWorkbook.Worksheets If lcase(oWkSh.CodeName) = lcase(ShCodeName) Then oWkSh.Visible = xlsheetvisible 'I like the constants! foundit = true exit for end if next owksh if foundit=true then For Each oWkSh In ThisWorkbook.Worksheets 'hide the rest If lcase(oWkSh.CodeName) = lcase(ShCodeName) Then 'do nothing else oWkSh.Visible = xlsheetveryhidden end if next owksh end if end sub ======= I'd loop through the code names. There are certain circumstances that this can fail (adding a new sheet can confuse the codenames--when the VBE hasn't been open, IIRC). But you can try this. It worked when I stepped through it and if the code window was open--but it failed other times. Option Explicit Function HideByCodeName(myCodeName As String, _ Optional Wkbk As Workbook) As Boolean 'with a reference to Visual Basic for application Extensibility x.x 'you can use VBComponent 'without the reference, use Object Dim VBC As Object 'VBComponent Dim myShtName As String If Wkbk Is Nothing Then Set Wkbk = ActiveWorkbook End If HideByCodeName = False Set VBC = Nothing On Error Resume Next Set VBC = Wkbk.VBProject.VBComponents(myCodeName) On Error GoTo 0 If VBC Is Nothing Then 'don't even try Else 'On Error Resume Next myShtName = VBC.Properties("Name") Wkbk.Sheets(myShtName).Visible = xlSheetHidden If Err.Number = 0 Then HideByCodeName = True End If On Error GoTo 0 End If End Function Sub testme() Dim myCodeName As String myCodeName = "Sheet1" MsgBox HideByCodeName(myCodeName) End Sub Kieranz wrote: On Mar 23, 2:15 pm, "Bob Phillips" wrote: You can't refer to codename as a variable like that. Use Sub ShowShK2() Dim ShCodeName as String K2.Visible = xlSheetVisible Application.Goto K2.Range("A1"), True K2.Name = "Inc2" End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message roups.com... On Mar 23, 11:44 am, wrote: Hi 1. Worksheets are visible, not their names, so you need Worksheets(ShCodeName).Visible = xlSheetVisible ' / i get invalid qualifer error 2. if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Do you have a sheet called "K2" in the active workbook? I would also use Sheets(ShCodeName).Visible = TRUE rahter than xlSheetVisible regards Paul On Mar 23, 9:32 am, "Kieranz" wrote: Hi All, Using Excel2003 with WinXP. Still learning VBA... Worksheet property has two names, one in brackets; call it CodeName and the other without brackets call it TabName. I have edited the CodeName to read "K1, K2, ... K10". Some sheets are very hidden, others appear as needed when macro button is pressed. On some sheets the user can rename the TabName. Thus some of my macros will call on procedures to select and show appro. sheets and to very hide others etc. My codes: Sub ShowShK2() Dim ShCodeName as String ShCodeName = "K2" ShCodeName.Visible = xlSheetVisible '/ i get invalid qualifer error '/ if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Call HideOtShs(ShCodeName) '/ i get subscript out of range error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" End Sub Sub HideOtShs(ShCodeName) '/ always make sure there is 1 sht thats visible bef calling this sub Dim oWkSh As Worksheet For Each oWkSh In ThisWorkbook.Worksheets If oWkSh.CodeName = ShCodeName Then oWkSh.Visible = -1 Else On Error Resume Next oWkSh.Visible = 2 End If Next oWkSh End Sub My understanding: 1. The CodeName cannot be programmatically changed; only by editing thru the properties window. ie they must be prenamed or use default name. 2. One sheet must be visible at all times. 3. Workbook protection must be off but worksheet protection can be on to manipulate names and visible properties. Help: Not sure why i am getting the errors in my coding. Your help and reasonings is much appreciated. God bless. Rgds from Zambia "The Real Africa" KZ Paul, I still get subscript out of range error. Its something to do with the variable. If i remove all to do with variable ie K2.visible = true it works. I am lost. Rgds K Hello Bob Your sub is what i orginally had and it did work but quickly ran into problem. The problem was if a user renamed the sheet (called TabName in my scenario) i will not have any clue as to name and position of the sheet. So using the sheet property window i can atleast prename the CodeName which i are called eg K1, K2 etc i was hoping to control hide and unhide. Also i need to use a variable for the CodeName so that i can pass on to the my other sub for hiding the other sheets. See my first msg. I hope you follow what i am saying. Its something to do with the variable, maybe it should be WkSht as Worksheet, my grey cells are getting jammed. Your help really appreciated. Rgds K -- Dave Peterson Hello Paul, Bob, Dave. You guys are gentlemen and officers!!!! Thanks, thanks a lot. Yep lot of homeworks for me. But i am liking it. Caveat. The user will not be able to add or delete sheets. Well hope so. When i first tried it was along what Bob suggested in his first msg and then it just went i think ten notches up on difficulty level. Pls bear with me i am on dialup <weep weep. But i will post next week what i hope will be a reasonable coding. Its the understanding thats hard. Anyway thks guys. Have a lovely weekend. Hope i do get to play golf... God bless Rgds K |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name and Visible... How do I...
On Mar 23, 5:03 pm, "Kieranz" wrote:
On Mar 23, 4:39 pm, Dave Peterson wrote: If you try to hide sheets in the wrong order, it can fail--one sheet has to be visible at all times. Sub HideOtShs(ShCodeName) Dim oWkSh As Worksheet dim Foundit as boolean foundit = false 'loop once to make sure a sheet is visible For Each oWkSh In ThisWorkbook.Worksheets If lcase(oWkSh.CodeName) = lcase(ShCodeName) Then oWkSh.Visible = xlsheetvisible 'I like the constants! foundit = true exit for end if next owksh if foundit=true then For Each oWkSh In ThisWorkbook.Worksheets 'hide the rest If lcase(oWkSh.CodeName) = lcase(ShCodeName) Then 'do nothing else oWkSh.Visible = xlsheetveryhidden end if next owksh end if end sub ======= I'd loop through the code names. There are certain circumstances that this can fail (adding a new sheet can confuse the codenames--when the VBE hasn't been open, IIRC). But you can try this. It worked when I stepped through it and if the code window was open--but it failed other times. Option Explicit Function HideByCodeName(myCodeName As String, _ Optional Wkbk As Workbook) As Boolean 'with a reference to Visual Basic for application Extensibility x.x 'you can use VBComponent 'without the reference, use Object Dim VBC As Object 'VBComponent Dim myShtName As String If Wkbk Is Nothing Then Set Wkbk = ActiveWorkbook End If HideByCodeName = False Set VBC = Nothing On Error Resume Next Set VBC = Wkbk.VBProject.VBComponents(myCodeName) On Error GoTo 0 If VBC Is Nothing Then 'don't even try Else 'On Error Resume Next myShtName = VBC.Properties("Name") Wkbk.Sheets(myShtName).Visible = xlSheetHidden If Err.Number = 0 Then HideByCodeName = True End If On Error GoTo 0 End If End Function Sub testme() Dim myCodeName As String myCodeName = "Sheet1" MsgBox HideByCodeName(myCodeName) End Sub Kieranz wrote: On Mar 23, 2:15 pm, "Bob Phillips" wrote: You can't refer to codename as a variable like that. Use Sub ShowShK2() Dim ShCodeName as String K2.Visible = xlSheetVisible Application.Goto K2.Range("A1"), True K2.Name = "Inc2" End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kieranz" wrote in message roups.com... On Mar 23, 11:44 am, wrote: Hi 1. Worksheets are visible, not their names, so you need Worksheets(ShCodeName).Visible = xlSheetVisible ' / i get invalid qualifer error 2. if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Do you have a sheet called "K2" in the active workbook? I would also use Sheets(ShCodeName).Visible = TRUE rahter than xlSheetVisible regards Paul On Mar 23, 9:32 am, "Kieranz" wrote: Hi All, Using Excel2003 with WinXP. Still learning VBA... Worksheet property has two names, one in brackets; call it CodeName and the other without brackets call it TabName. I have edited the CodeName to read "K1, K2, ... K10". Some sheets are very hidden, others appear as needed when macro button is pressed. On some sheets the user can rename the TabName. Thus some of my macros will call on procedures to select and show appro. sheets and to very hide others etc. My codes: Sub ShowShK2() Dim ShCodeName as String ShCodeName = "K2" ShCodeName.Visible = xlSheetVisible '/ i get invalid qualifer error '/ if i use the below i get subscript out of range error Sheets(ShCodeName).Visible = xlSheetVisible Call HideOtShs(ShCodeName) '/ i get subscript out of range error on below Application.Goto WorkSheet.ShCodeName.Range("A1"), True '/ rename TabName Worksheets(ShCodeName).Name = "Inc2" End Sub Sub HideOtShs(ShCodeName) '/ always make sure there is 1 sht thats visible bef calling this sub Dim oWkSh As Worksheet For Each oWkSh In ThisWorkbook.Worksheets If oWkSh.CodeName = ShCodeName Then oWkSh.Visible = -1 Else On Error Resume Next oWkSh.Visible = 2 End If Next oWkSh End Sub My understanding: 1. The CodeName cannot be programmatically changed; only by editing thru the properties window. ie they must be prenamed or use default name. 2. One sheet must be visible at all times. 3. Workbook protection must be off but worksheet protection can be on to manipulate names and visible properties. Help: Not sure why i am getting the errors in my coding. Your help and reasonings is much appreciated. God bless. Rgds from Zambia "The Real Africa" KZ Paul, I still get subscript out of range error. Its something to do with the variable. If i remove all to do with variable ie K2.visible = true it works. I am lost. Rgds K Hello Bob Your sub is what i orginally had and it did work but quickly ran into problem. The problem was if a user renamed the sheet (called TabName in my scenario) i will not have any clue as to name and position of the sheet. So using the sheet property window i can atleast prename the CodeName which i are called eg K1, K2 etc i was hoping to control hide and unhide. Also i need to use a variable for the CodeName so that i can pass on to the my other sub for hiding the other sheets. See my first msg. I hope you follow what i am saying. Its something to do with the variable, maybe it should be WkSht as Worksheet, my grey cells are getting jammed. Your help really appreciated. Rgds K -- Dave Peterson Hello Paul, Bob, Dave. You guys are gentlemen and officers!!!! Thanks, thanks a lot. Yep lot of homeworks for me. But i am liking it. Caveat. The user will not be able to add or delete sheets. Well hope so. When i first tried it was along what Bob suggested in his first msg and then it just went i think ten notches up on difficulty level. Pls bear with me i am on dialup <weep weep. But i will post next week what i hope will be a reasonable coding. Its the understanding thats hard. Anyway thks guys. Have a lovely weekend. Hope i do get to play golf... God bless Rgds K Dear Bob, Your codes worked beautifully. Your quote "As I said previously, you cannot get at the worksheet via a codename variable in that way, you are still trying that." Sorry Bob.. Will try to learn. Dave, i will take your caution and see how i can use your code. But this weekend I am going a golfing. Thanks to u guys. Many many thks. Rgds K |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet visible in an addin ? | Excel Programming | |||
Goto next visible worksheet | Excel Programming | |||
Worksheet has to set to visible as it is not visible after saving and closing Excel by VB. | Excel Programming | |||
print different worksheet by pressing visible button on worksheet | Excel Worksheet Functions | |||
How come the XLA worksheet becomes visible? | Excel Programming |