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