invalid inputbox entry

  • Thread starter Thread starter Annika
  • Start date Start date
A

Annika

I am creating a macro in Excel that will be used by many
different users, each with their own worksheets, etc.
The macro is used to extract data from a worksheet that
they already have open, and then output new data in the
worksheet that the macro was activated in.
In my code I have an input box where I ask the user for
the name of the worksheet that contains the source data.

My question is, what if they type in an invalid worksheet
name? What code can I put in there so that the user
doesn't have to deal with VBA error messages?

For example, if the worksheet name is "Sheet1" and they
type "Sheet_1," I would like a message box to be
activated. I know how to activate a message box, I just
don't know how to code for the computer recognizing
whether or not a workwheet by that name exists, without it
going into VBA error message hell.

Here's a chunk of my code, to give you a better idea of my
problem.
*******************
'Ask user for source worksheet
HrlySheet = InputBox("Which sheet would you like to take
the hourly data from?", _
"Source Sheet", ActiveSheet.Name)

'If user clicks cancel or leaves field blank, exit Sub
If HrlySheet = "" Then Exit Sub

'Activate the source sheet
Sheets(HrlySheet).Activate
*******************
 
There are several ways of doing this. This is longer than some methods.Just
do something likeIf DoesWorksheetExist("sheet_1")=True Then ...Public
Function DoesWorksheetExist(WKName As String) As Boolean
Dim objWorksheet As Worksheet

DoesWorksheetExist = False
WKName = LCase(WKName)

For Each objWorksheet In Worksheets
If WKName = LCase(objWorksheet.Name) Then
DoesWorksheetExist = True
Exit Function
End If
Next
End
Functionhttp://www.billlunney.com/Excel/FAQ/DisplayFAQ.ascx?ExcelFAQID=16
--

Regards,


Bill Lunney
www.billlunney.com
 
It's not particulary constructive to critisice people who have used their
own time in an attempt to help out. The code needs no comments due to it's
size and is laid out in a completely standard way. Perhaps the formatting
when posted looks a little strange.

The idea with this is that you would get the worksheet name with something
like

WName = InputBox ("Enter worksheet name")

then do

If DoesWorksheetExist(WName)=True Then
' ...
Else
' ...
End If




--

Regards,


Bill Lunney
www.billlunney.com
 
My most sincere apologies, Bill. I didn't mean to come off
that way. I am fairly new to VBA and the message that I
got from you showed up funny on my screen.
Thank you for clarifying your answer, andagain, I'm really
sorry for how I responded.
No reply necessary.
Annika
 
Back
Top