Checking Input box

I'm trying to verify input given via an input box:

Sub copyData()
  Dim varNameInput As String
  myNameInput = Application.InputBox(prompt:="Enter a sheet name", _
        Title:="Sheet Name", Type:=2)
  Do While myNameInput = ""
    MsgBox "You didn't enter a sheet name!", 16
    myNameInput = Application.InputBox(prompt:="Enter a sheet name", _
          Title:="Sheet Name", Type:=2)
  Loop
  If Not myNameInput = False Then
    MsgBox myNameInput
  End If
  Exit Sub
End Sub

This works, however... Because I can't quite translate what's in my mind into VBA, I'm stuck with the following cases which should all trigger a failure MsgBox and repeat the loop:

- If the user enters a white space
- If the user enters a name with a white space
- If the user enters a name that doesn't match any of the sheets in the workbook.

The latter, if I'm not mistaken, I need to do with Intersect, something like (and please correct me if I'm wrong here):

If Not Intersect(myNameInput, Range("A1:A10")) is Nothing Then
... successful match, run necessary code ...
Else
... trigger failure again and go back to loop ...
End If

I just don't know how to translate that into VBA.


And on a slightly different note, can an evaluation contain ORs? For example, If (myNameInput = "" || myNameInput = False || ...etc.) Then

Sub copyData()
Dim myNameInput As String
Dim fValid As Boolean
Do While Not fValid
myNameInput = Application.InputBox(prompt:="Enter a sheet name", _
Title:="Sheet Name", Type:=2)
If myNameInput = "False" Then Exit Sub
fValid = True
If myNameInput Like "* *" Then
fValid = False
ElseIf Not SheetExists(myNameInput) Then
fValid = False
End If
If Not fValid Then MsgBox "Invalid value"
Loop
End Sub

Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> I'm trying to verify input given via an input box:
>
> Code:
> --------------------
> Sub copyData()
> Dim varNameInput As String
> myNameInput = Application.InputBox(prompt:="Enter a sheet name", _
> Title:="Sheet Name", Type:=2)
> Do While myNameInput = ""
> MsgBox "You didn't enter a sheet name!", 16
> myNameInput = Application.InputBox(prompt:="Enter a sheet name", _
> Title:="Sheet Name", Type:=2)
> Loop
> If Not myNameInput = False Then
> MsgBox myNameInput
> End If
> Exit Sub
> End Sub
> --------------------
>
>
> This works, however... Because I can't quite translate what's in my
> mind into VBA, I'm stuck with the following cases which should all
> trigger a failure MsgBox and repeat the loop:
>
> - If the user enters a white space
> - If the user enters a name with a white space
> - If the user enters a name that doesn't match any of the sheets in
> the workbook.
>
> The latter, if I'm not mistaken, I need to do with Intersect, something
> like (and please correct me if I'm wrong here):
>
> Code:
> --------------------
> If Not Intersect(myNameInput, Range("A1:A10")) is Nothing Then
> ... successful match, run necessary code ...
> Else
> ... trigger failure again and go back to loop ...
> End If
> --------------------
>
>
> I just don't know how to translate that into VBA.
>
>
> And on a slightly different note, can an evaluation contain ORs? For
> example, *If (myNameInput = "" || myNameInput = False || ...etc.) Then*
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=504320
>

Hi AMK4,
Bob's solution to your problem would be the way to go, I'm sure his
code is bullet-proof, but if you're interested in a solution that keeps
most of your original code see below (just for fun!)
All I did was throw in a for each/next loop to check the input against
the workbook's sheet names. I've included the Lcase bit so that the
user can get away with being lazy and not bother with capitals and
lower case in the input of the sheet name.

Sub copyData()
Dim Sht As Worksheet
Dim varNameInput As String
Dim booGoodInput As Boolean
mynameinput = Application.InputBox(prompt:="Enter a sheet name", _
Title:="Sheet Name", Type:=2)
Do While Not booGoodInput
For Each Sht In ActiveWorkbook.Worksheets
If LCase(Sht.Name) = LCase(mynameinput) Then
booGoodInput = True
Exit For
End If
Next Sht
If Not booGoodInput Then
MsgBox "You didn't enter a sheet name!", 16
mynameinput = Application.InputBox(prompt:="Enter a sheet name", _
Title:="Sheet Name", Type:=2)
End If
Loop
If Not mynameinput = False Then
MsgBox mynameinput
End If
Exit Sub
End Sub

Ken Johnson

I forgot, yes OR's can be used in comparisons...
If myNameInput = "" OR myNameInput = False Then

(What does || mean? I've not seen it before.)

Ken Johnson

"Ken Johnson" <[email protected]> wrote in message
news:[email protected]...
> Hi AMK4,
> Bob's solution to your problem would be the way to go, I'm sure his
> code is bullet-proof, but if you're interested in a solution that keeps
> most of your original code see below (just for fun!)

I kept the Application.Inputbox <G>

Very funny Bob!
Ken Johnson

Originally Posted by Bob Phillips

I kept the Application.Inputbox <G>

Now see, this brings me to my next question: is there any advantage between using Application.InputBox, or just InputBox?

I guess there are others as well where one can use one syntax versus another, I just don't know if there's any real advantage. Someone enlighten me please?


And while we're on the subject of my original question, I took Bob's code (sorry Ken, his came in first) and added another piece to it:

  Do While Not ValidPage
    myPageInput = Application.InputBox(prompt:="Which form should this go on? (1 through 4)", _
          Title:="Form number", Type:=1)
    If myPageInput = 0 Then Exit Sub
    ValidPage = True
    If myPageInput <1  OR myPageInput > 4 Then
      ValidPage = False
    End If
    If Not ValidPage Then MsgBox "Only values beteen 1 and 4 are allowed.", 16
  Loop

Two things:
a) if one just hits the return key, Excel pops up it's standard 'formula error' message. Like when you enter a bad formula in a cell. Um, why?

And b) I need to make it so one can not enter '0' either, which at the moment will act as if you hit Cancel.

The primary difference is that Application.Inputbox allows limited in-flight
validation of the data, give a type of 1 and you cannot input text. Best of
all is type 8, as this allows you to drop into a worksheet and specify a
range.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> Bob Phillips Wrote:
> > I kept the Application.Inputbox <G>
>
> Now see, this brings me to my next question: is there any advantage
> between using Application.InputBox, or just InputBox?
>
> I guess there are others as well where one can use one syntax versus
> another, I just don't know if there's any real advantage. Someone
> enlighten me please?
>
>
> And while we're on the subject of my original question, I took Bob's
> code (sorry Ken, his came in first) and added another piece to it:
>
> Code:
> --------------------
> Do While Not ValidPage
> myPageInput = Application.InputBox(prompt:="Which form should this go
on? (1 through 4)", _
> Title:="Form number", Type:=1)
> If myPageInput = 0 Then Exit Sub
> ValidPage = True
> If myPageInput <1 OR myPageInput > 4 Then
> ValidPage = False
> End If
> If Not ValidPage Then MsgBox "Only values beteen 1 and 4 are allowed.",
16
> Loop
> --------------------
>
>
> Two things:
> a) if one just hits the return key, Excel pops up it's standard
> 'formula error' message. Like when you enter a bad formula in a cell.
> Um, why?
>
> And b) I need to make it so one can not enter '0' either, which at the
> moment will act as if you hit Cancel.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=504320
>

Originally Posted by Bob Phillips

The primary difference is that Application.Inputbox allows limited in-flight
validation of the data, give a type of 1 and you cannot input text. Best of
all is type 8, as this allows you to drop into a worksheet and specify a
range.

Am I correct in assuming that either would work at any time then, or are there cases where one would fail while the other won't? I'm trying to learn all these little quirks and improve on my own coding.

Anyone want to try and explain/tackle the second part of my question (in my last message)?

Originally Posted by Ken Johnson

I've included the Lcase bit so that the
user can get away with being lazy and not bother with capitals and
lower case in the input of the sheet name.

Actually, with Bob's code, whether I type in 'CheckThisOut' or 'checkthisout', it always matches the sheet (providing it actually exists.) I'm not concerned with UpPErlOwERcaSe matching to be honest.

But I like the fact that there are different approaches to the same solution (essentially). Makes me take a look at both of them and maybe, just maybe, have some of it seep through into my brain...

And the || comes from another C-like language (rather distant now) I used to code in:

|| - OR
&& - AND

And the usual (to me):

<eval> ? return_1 | return_2
Which is the same as:
If <eval> Then return_1 Else return_2
...but without the closing End If

Hi AMK4,
I usually go for Application.InputBox for the same reasons as Bob. The
first thing I tried with your problem was to use Type:= 8 then click a
worksheet tab, I know it's not a range, but it was worth a try. I got
my hopes up when Sheet3! appeared in the text box. My hopes were
quickly dashed when the error message popped up.
It's interesting that Bob's code, without the use of LCase or Ucase,
leads to the input not being case sensitive. I can't see how he's
managed that (damned clever).
Thanks for clearing up the ||'s for me.
Ken Johnson