Hierarchy of Codename and error catching if no worksheet.

[Kieranz]

Hi all,
Using Excel2003/2007 with Win XP/Vista.

I have a userform which has a number of checkboxes to tick to show up
the appropriate worksheets. ie to lessen clutter per individual user
preference.

Within my commandOK_click button i have the following code:
If chkSh01 then Sh01.visible = xlSheetVisible else
Sh01.visible=xlSheetVeryHidden
If chkSh02 then Sh02.visible = xlSheetVisible else
Sh02.visible=xlSheetVeryHidden
....
If chkSh30 then Sh30.visible = xlSheetVisible else
Sh30.visible=xlSheetVeryHidden
etc
to 30 sheets.

Sh01, Sh02 ... thru to Sh30 refers to sheet property codename.
Primarily to allow user change the name via the sheet tab AND also to
allow delete of sheets but not add.

If all the 30 sheets are there then the code works okay.
But if one or more sheets are not there then the code fails with a
compile error: "Variable not found".

Qn: 1. how do i code so that if the sheet does not exist then that
the error is ignored. What error catching routine can i use.
Qn: 2. to avoid the "variable not found" i need to reference the Sh01
properly so that VBA knows its refering to a sheet object.

I have to use the sheet codename as this is predetermined at design
stage with the names Sh01 to Sh30 thus allowing the user to change tab
name, delete the sheets but not add.

Still learning and enjoying...Many thanks
Rgds
Kieranz

 

[Dave Peterson]

There are ways to address the sheet directly using the codename as a variable,
but those ways require that the user allow the code to have access to the project.

And if you're going to have others run this procedure, I'd stay away from that.
Instead, I'd just loop through the sheets looking for a match between the
codename and checkbox name (well, close to a match -- sh## matches chksh##).

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim sh As Object 'could be any kind of sheet
Dim MaxShts As Long
MaxShts = 4 '30 for you

For iCtr = 1 To MaxShts
For Each sh In ThisWorkbook.Worksheets
If LCase(sh.CodeName) = LCase("sh" & Format(iCtr, "00")) Then
sh.Visible = Me.Controls("chksh" & Format(iCtr, "00")).Value
Exit For
End If
Next sh
Next iCtr

End Sub

I wouldn't use this, but if you want to try:

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim sh As Object 'could be any kind of sheet
Dim MaxShts As Long
Dim myCodeName As String
Dim myProt As Long

MaxShts = 4 '30 for you

myProt = vbext_pp_locked
On Error Resume Next
'this was added in xl2k, IIRC
myProt = ThisWorkbook.VBProject.Protection
On Error GoTo 0

If myProt = vbext_pp_locked Then
MsgBox "This procedure can't be used here!"
Exit Sub
End If

For iCtr = 1 To MaxShts
myCodeName = "Sh" & Format(iCtr, "00")
Set sh = Nothing
On Error Resume Next
With ThisWorkbook
Set sh = .Worksheets(CStr(.VBProject.VBComponents(myCodeName) _
.Properties("Name")))
End With
On Error GoTo 0

If sh Is Nothing Then
'not found, just skip it
MsgBox myCodeName & " wasn't found"
Else
sh.Visible = Me.Controls("chksh" & Format(iCtr, "00")).Value
End If

Next iCtr

End Sub

The setting you'll have to change (and each user would have to change this!) can
be found here:

In xl2003 menus:
Tools|macro|security|trusted publishers tab
check "trust access to visual basic project"