Use Inputbox value to exit sub problem

**HOT97ECLIPSEGSX** · 02-21-2010, 05:39 AM

I'm having a problem with:

Please Login or Register  to view this content.

Or, I'm trying to get the sub to cancel if the user clicks cancel. I tried replacing (If remove = "") with (If remove = vbCancel) but Excel isn't returning a value like it's supposed to, or I'm using the wrong user input box, so the sub isn't closing like I want it to. There is an option for Application.InputBox but I haven't tried it yet. I'm not sure if it'll return a "cancel" value like I want it to.

So, I created a Msgbox to display exactly what remove is equal when cancel is clicked so I could complete my If statement. Come to find out remove becomes blank, or "", so I set my sub to exit if remove = "". This works, but later on if the user just clicks ok and leaves the input box value blank the sub will end. I do not want this to happen.

I did some searching and it may have to do with how I'm declaring "remove". My understanding of "Variant", although taking up the most memory, should work as it covers all data types, correct? Any ideas?

**DonkeyOte** · 02-21-2010, 05:45 AM

The code you have should work as I see it.

If however the intention is to generate a Date Value I'd suggest using Application.InputBox (as you mention) with Type:=1

Please Login or Register  to view this content.

change the default date as desired

**HOT97ECLIPSEGSX** · 02-21-2010, 05:51 AM

Thanks for the fast post. I don't want to declare remove as a date for the simple fact I don't want the user using the tool I'm creating to have to stop and figure out what they're doing wrong to continue. If they type it in format 11/10/10 or 11-10-2009 format, or november 1st, 2010, etc, I want them to be able to continue on without the format condition.

The problem with the code I provided in the OP is remove isn't getting a value from the input box after cancel is clicked. I figured this was either due to the declaration of the variable, or I need to use Application.InputBox. As far as my understanding of the input box goes, if cancel is clicked, a value of "2" is set for the vb code. So if I refer to it later as:

Please Login or Register  to view this content.

What is the difference between Application.InputBox and InputBox? And adding Type:=1 confuses me as well. I'm sorry if I sound like a newb...but I haven't worked with VBA for that long.

**Marcol** · 02-21-2010, 06:24 AM

Try

Please Login or Register  to view this content.

Adding the default value should prompt the user to enter valid data

**mikerickson** · 02-21-2010, 06:33 AM

StrPtr = 0 indicates if Cancel was pressed (vs. null string entered).
If you use the Application.InputBox rather than InputBox, you can test if the returned value is False.

Please Login or Register  to view this content.

**Marcol** · 02-21-2010, 06:54 AM

hi mikerickson

VarPtr, StrPtr, and ObjPtr ?

Not documented in Excel ?

**teylyn** · 02-21-2010, 07:15 AM

Google is your friend

http://vb.mvps.org/tips/varptr.asp

**HOT97ECLIPSEGSX** · 02-21-2010, 03:19 PM

I thought cancel pressed by the user returned a value of 2? Please forgive me if this sounds newbie...I don't want to declare myself a VBA expert.

Why doesn't this work? I'll have to read up on the "VarPtr, StrPtr, and ObjPtr". Does it mean varient pointer, string pointer, and object pointer? What exactly does this do? I did read the page you provided above but I don't seem to understand it.

If I us Application.InputBox("blah blah", "blah blah") instead will it return a value like I want it to? Like stated earlier, the value being returned when the user hits cancel is "null", so asking if it's = to "" closes the sub like I want. Obviously this isn't how I really want it. You guys are great help, I just don't understand why the below code doesn't work. Maybe I missed something:

Please Login or Register  to view this content.

**shg** · 02-21-2010, 03:57 PM

MsgBox returns vbCancel if the user presses the Cancel button, not InputBox.

**HOT97ECLIPSEGSX** · 02-21-2010, 04:14 PM

Ok...that makes sense now. So, how do I capture the "cancel" selection from the input box so the sub ends when the user hits cancel? It seems input box doesn't return a value when the user hits cancel.

Can I say:

Please Login or Register  to view this content.

Wait, this won't work, either. Because if the user just hits enter when the question is asked, the value returned is "null". I'm confused as to how to make this work.

***EDIT*** Nevermind...I used the code DonkeyOte provided and it works, but it requires the full 4 digit year (YYYY). How can I make it so it can be (YY OR YYYY)?

**shg** · 02-21-2010, 04:30 PM

Mike gave you a perfectly good example.

**HOT97ECLIPSEGSX** · 02-21-2010, 04:50 PM

You're right. I used his example to text what the input was and I believe I'll be able to do what I'm trying to do. I'll post up the code result when I get it to work.

Use Inputbox value to exit sub problem

LinkBack

Thread Tools

Rate This Thread

Display

Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Re: Use Inputbox value to exit sub problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions