Checking Input box
 

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 Su
-------------------


This works, however... Because I can't quite translate what's in m
mind into VBA, I'm stuck with the following cases which should al
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 i
the workbook.

The latter, if I'm not mistaken, I need to do with Intersect, somethin
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 I
-------------------


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


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

--
AMK
-----------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...fo&userid=1914
View this thread: http://www.excelforum.com/showthread.php?threadid=50432

Checking Input box
 

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" wrote in message
...

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

Checking Input box
 

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

Checking Input box
 

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

Checking Input box
 

"Ken Johnson" wrote in message
oups.com...
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

Checking Input box
 

Very funny Bob!
Ken Johnson

Checking Input box
 

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

Checking Input box
 

Ken Johnson Wrote:
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:
Code:
--------------------
|| - OR
&& - AND
--------------------

And the usual (to me):
Code:
--------------------
<eval ? return_1 | return_2
Which is the same as:
If <eval Then return_1 Else return_2
...but without the closing End If
--------------------


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=504320

Checking Input box
 

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" wrote in message
...

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

Checking Input box
 

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

Checking Input box
 

Bob Phillips Wrote:
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)?


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=504320

Checking Input box
 

Hi AMK4,
I just used Bob's code and 0 (zero) resulted in the Invalid message, so
it remained in the loop.
Ken Johnson

Checking Input box
 

Ken Johnson Wrote:
Hi AMK4,
I just used Bob's code and 0 (zero) resulted in the Invalid message,
so
it remained in the loop.
Ken Johnson

Yesh. Bob's code for the first part I needed it to. But I expanded it
(by duplicating and modifying it) for the second part, which is asking
for an Integer instead of a name. Basically the code asks for a sheet
name first, and then continues on to ask for an Integer. It's that
part (which I posted) that fails.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=504320

Checking Input box
 

Hi AMK4,
are you talking about this 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

if you are then what about the line " If myPageInput = 0 Then Exit
Sub"?
When I enter 0 this line takes you out of the Sub and hence out of the
loop. After I commented this line out, 0 results in the error message
then it returns to the loop.
Does that sound right?
Ken Johnson

Checking Input box
 

Ken Johnson Wrote:
Hi AMK4,
are you talking about this code...

Yep. That line is there in case someone hits Cancel. Canceling will
result in myPageInput being 0. I need a way to distinguish between
someone entering 0 or hitting Cancel.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=504320

Checking Input box
 

Hi AMK4,
I've got it! (I think)
A close read of the Application.InputBox Help file reveals that Cancel
results in myPageInput being False, not 0, so you need to test if
False, not 0, before exiting the sub. I tried If myPageInput = False
then Exit Sub, but that didn't work, False equates to 0!
I succeeded with...

If Application.IsLogical(myPageInput) then Exit Sub

There is now only 1 tiny glitch, what if User inputs true? Turns out it
equates to 1 and would be processed as Form 1. If you want to do away
with that problem you can change the inputbox type to 2 (text) and that
line of code to...

If Application.IsLogical(mypageinput) And mypageinput < True Then Exit
Sub

I think, and hope that is the solution.

If it isn't, somebody else will have to help, I've got to get to bed,
it's 4:30 am here in Sydney. Thankfully today is a holiday (Australia
Day)

Checking Input box
 

Ken Johnson Wrote:
If Application.IsLogical(mypageinput) And mypageinput < True Then Exi
Sub


Happy AU Day! Go celebrate!

Problems. When I get to that inputbox, if I just hit the RETURN key
it fails on that line with a Type Mismatch. And since it's now a tex
box, I'll have to figure out a way to recode the rest of it to chec
for a numerical value between 1 and 4. I have to believe that there'
a better or easier method

--
AMK
-----------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...fo&userid=1914
View this thread: http://www.excelforum.com/showthread.php?threadid=50432

Checking Input box
 

Hi AMK4,
The only other way (that I can think of) is with a userform with 4
option buttons all within the same frame. However, you are so close to
getting the inputbox working it would be a shame to let a little Type
Mismatch stop you. What if, as soon as the code is past the point of
checking for Cancel versus 0 and true versus 1, you use one of the
Conversion methods to convert it from text back to whatever variable
type you want. My guess is CInt, which converts it to integer. The
quickest way to view all the Conversion methods is to open up the
Object Browser and type "conversion" (no speech marks of course) into
the box just to the right of the search button (binoculars icon). Then
they'll all appear under the heading "Members of Conversion" where you
can select one then click the ? button to view the Help file. I use the
Object Browser a lot. It helps me understand the relationships between
all the objects, properties and methods etc.
Funny thing though, I don't get the Type Mismatch error when I just hit
Return.
I'm going to do a bit of experimenting to see if I can get this error,
then get rid of it. Sounds stupid I know but whatever!

Ken Johnson

Checking Input box
 

Hi AMK4,
I get the Type Mismatch error after including "Dim MyPageInput As
Integer" at the top of the code. Is that what you have at the top of
your code?
Do you use Option Explicit at the top of all your Modules? All the
experts recommend it. It can save you a lot of trouble. If you
accidentally typed (say) MyPageImput Excel will let you know there's a
problem the instant you try to run the code. With Option Explicit you
cannot use any variables that have not been dimensioned. You wouldn't
have dimensioned MyPageImput, it's a typo. Without Option Explicit the
code would run and you would likely end up with unexpected results and
it's up to you to hunt done the source of the problem, and checking for
typos might be the last thing you think of.
So, if you use Option Explicit change MyPageInput's Dim statement to
just "Dim MyPageInput" which dimensions it as variant, the default
variable type. That way you should not get the Type Mismatch error and
you won't have to worry about using any Conversion methods.

Ken Johnson

Checking Input box
 

Ken Johnson Wrote:
Hi AMK4,
I get the Type Mismatch error after including "Dim MyPageInput As
Integer" at the top of the code. Is that what you have at the top of
your code?
Do you use Option Explicit at the top of all your Modules?

Yep, I do.

Here's the current code as it is:
Code:
--------------------
Option Explicit
Sub copyData()
Dim myPageInput As String
Dim ValidPage As Boolean
Do While Not ValidPage
myPageInput = Application.InputBox(prompt:="Which form should this go on? (1 through 4)", _
Title:="Form number", Type:=2)
If Application.IsLogical(myPageInput) And myPageInput < True And myPageInput < "" Then Exit Sub
ValidPage = True
Loop
MsgBox "myPageInput: " & myPageInput, 64
End Sub
--------------------


When I run that, hitting return will produce a Type mismatch error.

If I change my Dim statement to simply say *Dim myPageInput*, I no
longer get the error. However, I just realised that regardless of what
I type in, whether it's a number, a string, the words TRUE or FALSE, it
never actually exits the Sub. I get the MsgBox coming up either way.

So something's definitely up here.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=504320

Checking Input box
 

Hi AMK4,
try this.
I've changed to variant and put the code lines that wouldn't work with
string data back in since they do work with variant.
If you just want the InputBox to remain in place until the user enters
a 1,2,3 or 4 rather than show the invalid entry message just delete
line that goes...

If Not ValidPage Then MsgBox "Only values between 1 and 4 are
allowed.", 16

Also, I found two new problems (don't panic, they're solved, I hope!).

1. What if the user enters a decimal eg 1.2 I've included a test for
integer value, namely
Or Int(myPageInput) < CSng(myPageInput) Then

2. What if the user enters a string other than true. This results in
the Type Mismatch error so I've had to resort to "On Error Resume Next"
which forces Excel to ignore the error. The code then goes on to let
ValidPage = False so the loop is not exited. After that line the "On
Error Goto 0" reactivates Excels error detection ability.

Sub copyData()
Dim myPageInput 'was String
Dim ValidPage As Boolean
Do While Not ValidPage
myPageInput = Application.InputBox(prompt:="Which form should this go
on? (1 through 4)", _
Title:="Form number", Type:=2)
'Your 2nd AND in next line made no difference so I took it out
If Application.IsLogical(myPageInput) And myPageInput < True Then Exit
Sub
ValidPage = True
'with variant myPageInput next three lines work
On Error Resume Next
If myPageInput < 1 _
Or myPageInput 4 _
Or Int(myPageInput) < CSng(myPageInput) Then 'back in + test for
integer
On Error GoTo 0
ValidPage = False 'back in
End If 'back in
If Not ValidPage Then MsgBox "Only values between 1 and 4 are
allowed.", 16 'back in
Loop

MsgBox "myPageInput: " & myPageInput, 64
End Sub

I'll now be spending a little time undoing all the knots that have
formed in my brain;-)

Ken Johnson

Checking Input box
 

Hi AMK4,
Just in case the line breaks are a problem (which they probably aren't
for you) here's the code in direct pastable form (I hope, it's always a
gamble)

Sub copyData()
Dim myPageInput 'was String
Dim ValidPage As Boolean
Do While Not ValidPage
myPageInput = Application.InputBox( _
prompt:="Which form should this go on? (1 through 4)", _
Title:="Form number", Type:=2)
'Your 2nd AND in next line made no difference so I took it out
If Application.IsLogical(myPageInput) And myPageInput < True _
Then Exit Sub
ValidPage = True
'with variant myPageInput next three lines work
On Error Resume Next
If myPageInput < 1 _
Or myPageInput 4 _
Or Int(myPageInput) < CSng(myPageInput) Then 'back in
'+ test for integer
On Error GoTo 0
ValidPage = False 'back in
End If 'back in
If Not ValidPage Then _
MsgBox "Only values between 1 and 4 are allowed.", 16 'back in
Loop
MsgBox "myPageInput: " & myPageInput, 64
End Sub
Ken Johnson