Worksheet Name and Visible... How do I...

[Kieranz]

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.robinson]

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

 

[Kieranz]

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

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

 

[Bob Phillips]

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]

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)

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 Phillips]

You can get at it like so

Worksheets(ActiveWorkbook.VBProject.VBComponents(ShCodeName).Properties("Name").Value).Visible
= xlSheetVisible

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Kieranz]

You can get at it like so

Worksheets(ActiveWorkbook.VBProject.VBComponents(ShCodeName).Properties("Name").Value).Visible
= xlSheetVisible

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

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

 

[Bob Phillips]

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(ShCodeName) _
.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)

 

[Dave Peterson]

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]

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

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

 

[Kieranz]

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