Posted to microsoft.public.excel.programming
|
|
Referring to a worksheet using variable
Hi WSF
I see I did not remove the Exit sub in your macro
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl
"WSF" wrote in message ...
Thanks for your help Ron.
Regards,
WSF
"Ron de Bruin" wrote in message
...
Hi Bill
This is working for me
Sub CheckReport()
Dim ReportSheetMonth As String, ReportSheetType As String,
ReportSheetName As String
ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the
reports you wish to check [mmm-yy]" & Chr$(13) &
Chr$(13) _
& "eg Mar-04", "Report Period"))
ReportSheetType = "Sales Report - "
ReportSheetName = ReportSheetType & ReportSheetMonth
If WorksheetExists(ReportSheetName) = True Then
MsgBox "Report does exist"
Else
MsgBox "Report does NOT exist"
Exit Sub
End If
End Sub
Public Function WorksheetExists(WSName As String, Optional WB As Workbook
= Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook,
WB).Worksheets(WSName).Name))
End Function
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl
"WSF" wrote in message
...
Hello Ron,
That is a typo on my part. I have correct the error and still no go.
The issue is why the code will accept a text string but not my variable.
Regards,
Bill Fraser
"Ron de Bruin" wrote in message
...
Hi
The only thing I see is that you use this line
GoTo ReportExists
But he can't find that because the name = TatReportExists
TatReportExists:
MsgBox "Report does exist" ' do what is required as a
result
Exit Sub
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl
"WSF" wrote in message
...
Excel 8.0
I am testing a means of identifying / selecting (and then copying) a
worksheet within the current workbook.
The report sheets use a standard naming format - Report Name -
Mmm-YY
e.g.
"Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out
Report -
Dec-03" etc etc
I am asking the code to confirm the sheets existence.
Sub CheckReport()
Dim ReportSheetMonth As String, ReportSheetType as String,
ReportSheetName
as String
'Users enter the Month Required
ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of
the
reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg
Mar-04",
"Report Period"))
' I have other code here that checks the validity of the entry. An
example
of an entry would be say "Dec-03"
ReportSheetType = "Sales Report - "
ReportSheetName = ReportSheetType & ReportSheetMonth
If WorksheetExists(ReportSheetName) = True Then _ ' see below
GoTo ReportExists
MsgBox "Report does NOT exist" ' do what is required as a result
Exit Sub
TatReportExists:
MsgBox "Report does exist" ' do what is required as a
result
Exit Sub
To test for the worksheet's existence I use the following Function.
Public Function WorksheetExists(WSName As String, Optional WB As
Workbook =
Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook,
WB).Worksheets(WSName).Name))
End Function
The above does not work.
But if I paste the actual name of the worksheet (eg Sales Report -
Dec-03)
directly into the code above
i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc
etc
it does work.
What am I doing wrong here?
Any help gratefully appreciated.
WSF
|