I have many sheets in the workbook. The cells in Column A of Sheet1 contains
the sheet names of each worksheet in the workbook, which is filled in by
myself manually, periodically. Because I sometimes delete some sheets
without updating the records in Column A of Sheet1, I need a macro to
distinguish if the names contained in Column A of Sheet1 is still valid ie.
sheet name is still existing among the worksheets in the workbook. It will be
good to return the result as a remark (€œpresent€� or €œabsent€�) in Column B of
Sheet1.

VBA rookie here.
Thanks a lot

--
Edmund
(Using Excel XP)

If possible, it will be excellent if you can demonstrate it with an array.

--
Edmund
(Using Excel XP)

Why an array?

Tim

"Edmund" wrote in message ...
If possible, it will be excellent if you can demonstrate it with an array.

--
Edmund
(Using Excel XP)

Hi Edmund

Try this, please


Option Explicit

Const hil As String = "Best Regards fro Joergen Bondesen"

Const SheetNameSheet As String = "Sheet1"

'----------------------------------------------------------
' Procedure : SheetExistTest
' Date : 20060709
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Does sheet(s) exist.
' Note : Sheets names is in 'SheetNameSheet' from
' cell A1
'----------------------------------------------------------
'
Sub SheetExistTest()

Dim Lastrow As String
Dim Shrange As Range
Dim cell As Range
Dim wkSht As Worksheet

Lastrow = Sheets(SheetNameSheet).Cells _
(Sheets(SheetNameSheet).Rows.Count, 1).End(xlUp).Row

Set Shrange = Sheets(SheetNameSheet).Range("A1:A" _
& Lastrow)

For Each cell In Shrange
On Error Resume Next
Set wkSht = Worksheets(cell.Value)
If Err < 0 Then
MsgBox "Sheet: " & cell.Value _
& " do not exist." & vbCr _
& "Macro will terminate.", vbCritical, hil

GoTo xit
End If
On Error GoTo 0
Next cell

xit:
Set Shrange = Nothing
End Sub


--
Best Regards
Joergen Bondesen


"Edmund" wrote in message
...
If possible, it will be excellent if you can demonstrate it with an array.

--
Edmund
(Using Excel XP)

Hi Edmund,

In a standard modulr paste the following funtion:

'=============
Public Function SheetExists(SHName As String) As String
Dim blExists As Boolean
On Error Resume Next
blExists = CBool(Len(Worksheets(SHName).Name))
On Error GoTo 0

SheetExists = IIf(blExists, "Present", "Absent")

End Function
'<<=============

In Cell B2 on Sheet1, enter the formula:

=SheetExists(A2)

and copy down as far as required.

To remove the need subsequentky to update the list with newly added sheets,
try:

'=============
Private Sub Workbook_NewSheet(ByVal SH As Object)
Dim WS As Worksheet
Dim rng As Range

Set WS = Me.Sheets("Sheet1") '<<==== CHANGE
Set rng = WS.Cells(Rows.Count, "A").End(xlUp)(2)

rng.Value = SH.Name
rng.Offset(0, 1).FormulaR1C1 = "=SheetExists(RC[-1])"
End Sub
'<<=============

This latter procedure is workbook event code and should be pasted into the
workbook's ThisWorkbook module *not* a standard module or a sheet module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman


"Edmund" wrote in message
...
I have many sheets in the workbook. The cells in Column A of Sheet1
contains
the sheet names of each worksheet in the workbook, which is filled in by
myself manually, periodically. Because I sometimes delete some sheets
without updating the records in Column A of Sheet1, I need a macro to
distinguish if the names contained in Column A of Sheet1 is still valid
ie.
sheet name is still existing among the worksheets in the workbook. It will
be
good to return the result as a remark ("present" or "absent") in Column B
of
Sheet1.

VBA rookie here.
Thanks a lot

--
Edmund
(Using Excel XP)

Edmund

You could try here for code which will rebuild your index for you and
provide hyperlinks and the sheet type

http://www.nickhodge.co.uk/vba/codeexamples.htm

At the bottom of page

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Edmund" wrote in message
...
I have many sheets in the workbook. The cells in Column A of Sheet1
contains
the sheet names of each worksheet in the workbook, which is filled in by
myself manually, periodically. Because I sometimes delete some sheets
without updating the records in Column A of Sheet1, I need a macro to
distinguish if the names contained in Column A of Sheet1 is still valid
ie.
sheet name is still existing among the worksheets in the workbook. It will
be
good to return the result as a remark ("present" or "absent") in Column B
of
Sheet1.

VBA rookie here.
Thanks a lot

--
Edmund
(Using Excel XP)

Hi Edmond

I forgot this, sorry.


Option Explicit

'// Placed both in ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'// Macro
On Error Resume Next
SheetExistTest
On Error GoTo 0
End Sub


Private Sub Workbook_Open()
'// Macro
On Error Resume Next
SheetExistTest
On Error GoTo 0
End Sub

'******

'// Placed in Module
Sub SheetExistTest()


--
Best Regards
Joergen Bondesen


"Joergen Bondesen" wrote in message
...
Hi Edmund

Try this, please


Option Explicit

Const hil As String = "Best Regards fro Joergen Bondesen"

Const SheetNameSheet As String = "Sheet1"

'----------------------------------------------------------
' Procedure : SheetExistTest
' Date : 20060709
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Does sheet(s) exist.
' Note : Sheets names is in 'SheetNameSheet' from
' cell A1
'----------------------------------------------------------
'
Sub SheetExistTest()

Dim Lastrow As String
Dim Shrange As Range
Dim cell As Range
Dim wkSht As Worksheet

Lastrow = Sheets(SheetNameSheet).Cells _
(Sheets(SheetNameSheet).Rows.Count, 1).End(xlUp).Row

Set Shrange = Sheets(SheetNameSheet).Range("A1:A" _
& Lastrow)

For Each cell In Shrange
On Error Resume Next
Set wkSht = Worksheets(cell.Value)
If Err < 0 Then
MsgBox "Sheet: " & cell.Value _
& " do not exist." & vbCr _
& "Macro will terminate.", vbCritical, hil

GoTo xit
End If
On Error GoTo 0
Next cell

xit:
Set Shrange = Nothing
End Sub


--
Best Regards
Joergen Bondesen


"Edmund" wrote in message
...
If possible, it will be excellent if you can demonstrate it with an
array.

--
Edmund
(Using Excel XP)

Dear Tim,

I wrote the below sample code. It served the purpose in identifying if the
sheets exist. But because I'm a learner (self study), I was not satisfied
with this below approach as I actually intend to use the "computer's memory"
to remember the names of all the existing sheet name, then cycle through
these memorized names to see if any matches that in the activecell of Column
A Sheet1. So, I thought the word was "array". I wanted to learn more.

Though my below procedure serves the purpose by cycling via For-Next, I'm
still ignorant on how to utilize computer's memory. I've searched my VBA book
& also F1 Help but can't find an appropriate example to grasp. Perhaps, I hv
not understood how to use array yet. That's why I wanted a sample in array(if
any), instead of just the usual For-Next.

My calamity:
I hv absolutely no programming background nor any friends nor colleagues who
can help with "Excel VBA". Absolutely no one to ask. Infact, I know very
little about any applications except with MS Excel (but not in VBA. Still
self studying). And though I work for a very big & prominent company with
7000 workforce, which includes many good & skilled programmers (mainly VB &
VB.net), but absolutely non of them has the slightest knowledge on Excel VBA
(Excel VBA is something almost unheard of in this part of the world, even to
IT people. No one talks about Excel VBA because non hv realized its
potential).

Hope that explains.
Good day.


Private Sub DoesSheetExist()
Dim n As Variant
Do While ActiveCell < ""
For Each n In Sheets
If ActiveCell.Value = n.Name Then _
ActiveCell.Offset(0, 1) = "Present"
Next n
ActiveCell.Offset(1, 0).Select
Loop
End Sub

--
Edmund
(Using Excel XP)


"Tim Williams" wrote:

Why an array?

Tim

"Edmund" wrote in message ...
If possible, it will be excellent if you can demonstrate it with an array.

--
Edmund
(Using Excel XP)

Edmund,

Actually by using the keyword of "Sheets" you are technically using an
array... It is actually defined as a collection, but works just like an
array you would define yourself. The following code demonstrates how
"Sheets" is a single dimensional array:

Dim i as Ineger

For i = 1 to Sheets.Count
MsgBox Sheets(i).Name
Next i

VB(A) provides many default 'arrays' of things which it calls "Collections".
In the code you used, you just can't see how Sheets is used as an array
because of the way you use the keyword...

Private Sub DoesSheetExist()
Dim n As Variant
Do While ActiveCell < ""
For Each n In Sheets
If ActiveCell.Value = n.Name Then _
ActiveCell.Offset(0, 1) = "Present"
Next n
ActiveCell.Offset(1, 0).Select
Loop
End Sub

This could be translated as:

Private Sub DoesSheetExist()
Dim i as Integer
Dim s as Integer
Dim temp as String

For i = 1 to ActiveSheet.UsedRange.Rows.Count ' Change 1 to 2 if row 1
is a header
temp = Cells(i,1).Value ' Assuming column 1 has the names...
For s = 1 to Sheets.Count
If Sheets(s).Name = temp then
Cells(i,2).Value = "Present"
Exit For
End If
Next s
If s Sheets.Count Then Cells(i,2).Value = "NOT Present" 'addition
to original code...
Next i
End Sub

Technically, "Cells" is a two dimensional array of the cells on the active
worksheet!

David


"Edmund" wrote in message
...
Dear Tim,

I wrote the below sample code. It served the purpose in identifying if the
sheets exist. But because I'm a learner (self study), I was not satisfied
with this below approach as I actually intend to use the "computer's
memory"
to remember the names of all the existing sheet name, then cycle through
these memorized names to see if any matches that in the activecell of
Column
A Sheet1. So, I thought the word was "array". I wanted to learn more.

Though my below procedure serves the purpose by cycling via For-Next, I'm
still ignorant on how to utilize computer's memory. I've searched my VBA
book
& also F1 Help but can't find an appropriate example to grasp. Perhaps, I
hv
not understood how to use array yet. That's why I wanted a sample in
array(if
any), instead of just the usual For-Next.

My calamity:
I hv absolutely no programming background nor any friends nor colleagues
who
can help with "Excel VBA". Absolutely no one to ask. Infact, I know very
little about any applications except with MS Excel (but not in VBA. Still
self studying). And though I work for a very big & prominent company with
7000 workforce, which includes many good & skilled programmers (mainly VB
&
VB.net), but absolutely non of them has the slightest knowledge on Excel
VBA
(Excel VBA is something almost unheard of in this part of the world, even
to
IT people. No one talks about Excel VBA because non hv realized its
potential).

Hope that explains.
Good day.


Private Sub DoesSheetExist()
Dim n As Variant
Do While ActiveCell < ""
For Each n In Sheets
If ActiveCell.Value = n.Name Then _
ActiveCell.Offset(0, 1) = "Present"
Next n
ActiveCell.Offset(1, 0).Select
Loop
End Sub

--
Edmund
(Using Excel XP)


"Tim Williams" wrote:

Why an array?

Tim

"Edmund" wrote in message
...
If possible, it will be excellent if you can demonstrate it with an
array.

--
Edmund
(Using Excel XP)

To each & everyone of u who responded, I sincerely thank you very very much.

Especially to those of u who put in so much extra effort in explaining &
putting indicators in your code to guide me, I'm really really touched.

Your spirit, effort & contribution is benefiting many.

--
Edmund
(Using Excel XP)


"Dove" wrote:

Edmund,

Actually by using the keyword of "Sheets" you are technically using an
array... It is actually defined as a collection, but works just like an
array you would define yourself. The following code demonstrates how
"Sheets" is a single dimensional array:

Dim i as Ineger

For i = 1 to Sheets.Count
MsgBox Sheets(i).Name
Next i

VB(A) provides many default 'arrays' of things which it calls "Collections".
In the code you used, you just can't see how Sheets is used as an array
because of the way you use the keyword...

Private Sub DoesSheetExist()
Dim n As Variant
Do While ActiveCell < ""
For Each n In Sheets
If ActiveCell.Value = n.Name Then _
ActiveCell.Offset(0, 1) = "Present"
Next n
ActiveCell.Offset(1, 0).Select
Loop
End Sub

This could be translated as:

Private Sub DoesSheetExist()
Dim i as Integer
Dim s as Integer
Dim temp as String

For i = 1 to ActiveSheet.UsedRange.Rows.Count ' Change 1 to 2 if row 1
is a header
temp = Cells(i,1).Value ' Assuming column 1 has the names...
For s = 1 to Sheets.Count
If Sheets(s).Name = temp then
Cells(i,2).Value = "Present"
Exit For
End If
Next s
If s Sheets.Count Then Cells(i,2).Value = "NOT Present" 'addition
to original code...
Next i
End Sub

Technically, "Cells" is a two dimensional array of the cells on the active
worksheet!

David


"Edmund" wrote in message
...
Dear Tim,

I wrote the below sample code. It served the purpose in identifying if the
sheets exist. But because I'm a learner (self study), I was not satisfied
with this below approach as I actually intend to use the "computer's
memory"
to remember the names of all the existing sheet name, then cycle through
these memorized names to see if any matches that in the activecell of
Column
A Sheet1. So, I thought the word was "array". I wanted to learn more.

Though my below procedure serves the purpose by cycling via For-Next, I'm
still ignorant on how to utilize computer's memory. I've searched my VBA
book
& also F1 Help but can't find an appropriate example to grasp. Perhaps, I
hv
not understood how to use array yet. That's why I wanted a sample in
array(if
any), instead of just the usual For-Next.

My calamity:
I hv absolutely no programming background nor any friends nor colleagues
who
can help with "Excel VBA". Absolutely no one to ask. Infact, I know very
little about any applications except with MS Excel (but not in VBA. Still
self studying). And though I work for a very big & prominent company with
7000 workforce, which includes many good & skilled programmers (mainly VB
&
VB.net), but absolutely non of them has the slightest knowledge on Excel
VBA
(Excel VBA is something almost unheard of in this part of the world, even
to
IT people. No one talks about Excel VBA because non hv realized its
potential).

Hope that explains.
Good day.


Private Sub DoesSheetExist()
Dim n As Variant
Do While ActiveCell < ""
For Each n In Sheets
If ActiveCell.Value = n.Name Then _
ActiveCell.Offset(0, 1) = "Present"
Next n
ActiveCell.Offset(1, 0).Select
Loop
End Sub

--
Edmund
(Using Excel XP)


"Tim Williams" wrote:

Why an array?

Tim

"Edmund" wrote in message
...
If possible, it will be excellent if you can demonstrate it with an
array.

--
Edmund
(Using Excel XP)