if until requirement met

Hi,
the user is required to enter data in one cell as ##/## i.e. 04/04
I would like to restrict the cell input to that and prompt the user if/how to
enter data.
I also want to embed this into a makro.
something like
If cellvalue <> "##/##"
then msgbox "Please enter date in this format ##/## i.e. 04/04"
Else
msgbox "Good Format"
...and continue with the code once the data was inot correctly.
i cant figure out how to do this. Thank you for your help.
stefan


--
Message posted via http://www.officekb.com

Hi Stefan,

Adopting a different approach, why not show a calendar control, when the
relevant cells are selected, and then use or format the resultant input as
required.

For example code, see Ron de Bruin popup Calendar page at:

http://www.rondebruin.nl/calendar.htm

---
Regards,
Norman



"stefan via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
> the user is required to enter data in one cell as ##/## i.e. 04/04
> I would like to restrict the cell input to that and prompt the user if/how
> to
> enter data.
> I also want to embed this into a makro.
> something like
> If cellvalue <> "##/##"
> then msgbox "Please enter date in this format ##/## i.e. 04/04"
> Else
> msgbox "Good Format"
> ..and continue with the code once the data was inot correctly.
> i cant figure out how to do this. Thank you for your help.
> stefan
>
>
> --
> Message posted via http://www.officekb.com

An alternativel to Norman's suggestion is to go with Data Validation. Someone
can probably come up with a more elegant formula than mine:-

1) Select the Data menu
2) Select Validation
3) Select the Custom option from the Allow dropdown list
4) Enter the following formula in the formula window. Unfortunately, you
will have to type it in. The window won't accept copy and paste.

=AND(LEN(A1) = 5,
ISNUMBER(VALUE(LEFT(A1,2))),MID(A1,3,1)="/",ISNUMBER(VALUE(RIGHT(A1,2))))

5) Enter suitable text for Input Message and Error Alert. See these tabs.

Regards,
Greg

"stefan via OfficeKB.com" wrote:

>
> Hi,
> the user is required to enter data in one cell as ##/## i.e. 04/04
> I would like to restrict the cell input to that and prompt the user if/how to
> enter data.
> I also want to embed this into a makro.
> something like
> If cellvalue <> "##/##"
> then msgbox "Please enter date in this format ##/## i.e. 04/04"
> Else
> msgbox "Good Format"
> ...and continue with the code once the data was inot correctly.
> i cant figure out how to do this. Thank you for your help.
> stefan
>
>
> --
> Message posted via http://www.officekb.com
>

Hi Greg,

> 4) Enter the following formula in the formula window. Unfortunately, you
> will have to type it in. The window won't accept copy and paste.

Using Ctrl-V, I had no problem pasting you formula into the DV window.


---
Regards,
Norman



"Greg Wilson" <[email protected]> wrote in message
news:[email protected]...
> An alternativel to Norman's suggestion is to go with Data Validation.
> Someone
> can probably come up with a more elegant formula than mine:-
>
> 1) Select the Data menu
> 2) Select Validation
> 3) Select the Custom option from the Allow dropdown list
> 4) Enter the following formula in the formula window. Unfortunately, you
> will have to type it in. The window won't accept copy and paste.
>
> =AND(LEN(A1) = 5,
> ISNUMBER(VALUE(LEFT(A1,2))),MID(A1,3,1)="/",ISNUMBER(VALUE(RIGHT(A1,2))))
>
> 5) Enter suitable text for Input Message and Error Alert. See these tabs.
>
> Regards,
> Greg
>
> "stefan via OfficeKB.com" wrote:
>
>>
>> Hi,
>> the user is required to enter data in one cell as ##/## i.e. 04/04
>> I would like to restrict the cell input to that and prompt the user
>> if/how to
>> enter data.
>> I also want to embed this into a makro.
>> something like
>> If cellvalue <> "##/##"
>> then msgbox "Please enter date in this format ##/## i.e. 04/04"
>> Else
>> msgbox "Good Format"
>> ...and continue with the code once the data was inot correctly.
>> i cant figure out how to do this. Thank you for your help.
>> stefan
>>
>>
>> --
>> Message posted via http://www.officekb.com
>>

Further to my post:
The cell involved apparently needs to be formated as text. Note Norman's
technique for pasting a formula to the Formula window. I never knew that.
Glad I posted.

"Greg Wilson" wrote:

> An alternativel to Norman's suggestion is to go with Data Validation. Someone
> can probably come up with a more elegant formula than mine:-
>
> 1) Select the Data menu
> 2) Select Validation
> 3) Select the Custom option from the Allow dropdown list
> 4) Enter the following formula in the formula window. Unfortunately, you
> will have to type it in. The window won't accept copy and paste.
>
> =AND(LEN(A1) = 5,
> ISNUMBER(VALUE(LEFT(A1,2))),MID(A1,3,1)="/",ISNUMBER(VALUE(RIGHT(A1,2))))
>
> 5) Enter suitable text for Input Message and Error Alert. See these tabs.
>
> Regards,
> Greg
>
> "stefan via OfficeKB.com" wrote:
>
> >
> > Hi,
> > the user is required to enter data in one cell as ##/## i.e. 04/04
> > I would like to restrict the cell input to that and prompt the user if/how to
> > enter data.
> > I also want to embed this into a makro.
> > something like
> > If cellvalue <> "##/##"
> > then msgbox "Please enter date in this format ##/## i.e. 04/04"
> > Else
> > msgbox "Good Format"
> > ...and continue with the code once the data was inot correctly.
> > i cant figure out how to do this. Thank you for your help.
> > stefan
> >
> >
> > --
> > Message posted via http://www.officekb.com
> >

Hi Greg,
This is excellent! I love it.
You wouldnt happen to know a magic formula to require 6 or 16 characters
(numbers, but entered as text) for validation like this?
Thanks.
Stefan

Greg Wilson wrote:

>=AND(LEN(A1) = 5,
>ISNUMBER(VALUE(LEFT(A1,2))),MID(A1,3,1)="/",ISNUMBER(VALUE(RIGHT(A1,2))))
>


--
Message posted via http://www.officekb.com

Thanks a million pointing that out Norman !!!

I can't get it to work when I copy directly from a worksheet, whether using
Ctrl-V, the right-click menu or through the Edit menu. Apparently, Excel
kills what's on the clipboard when copied from a worksheet (i.e. when in
CutCopy mode) when dialogs are opened. The same holds for the Conditional
Formatting forumula window and the named range "Refers to" window.

So, the answer appears to be to write your formulas in a code module, then
copy and paste to either of the above using Ctrl-V. After all this time I
never knew that. This will be extremely useful.

Looks like I will benefit more from this post than stephan. Please advise if
I'm missing something. Thanks again.

Best regards,
Greg

using a "regular formula" i came up with this
=IF(LEN(E15)=6,LEFT(E15,2)&"xx xxxx xxxx "&RIGHT(E15,4),IF(LEN(E15)=16,LEFT
(E15,4)&" "&MID(E15,5,4)&" "&MID(E15,9,4)&" "&RIGHT(E15,4),"NOT CORRECT"))
but i'm not sure how, if possible, to work it into the validation!?
Stefan

stefan wrote:
>Hi Greg,
>This is excellent! I love it.
>You wouldnt happen to know a magic formula to require 6 or 16 characters
>(numbers, but entered as text) for validation like this?
>Thanks.
>Stefan
>
>>=AND(LEN(A1) = 5,
>>ISNUMBER(VALUE(LEFT(A1,2))),MID(A1,3,1)="/",ISNUMBER(VALUE(RIGHT(A1,2))))


--
Message posted via http://www.officekb.com

Assuming by six you mean "###/###":

'=AND(LEN(A1) = 7,
ISNUMBER(VALUE(LEFT(A1,3))),MID(A1,4,1)="/",ISNUMBER(VALUE(RIGHT(A1,3))))

Assuming by 16 you mean "########/########":

'=AND(LEN(A1) = 17,
ISNUMBER(VALUE(LEFT(A1,8))),MID(A1,9,1)="/",ISNUMBER(VALUE(RIGHT(A1,8))))

I advise that you copy the formulae and paste to, say, a code module (not a
worksheet), then correct for wordwrap. Copy again after correcting the
wordwrap and paste to the formula window using Ctrl_V as per Norman's advice.

Regards,
Greg

>
> Hi Greg,
> This is excellent! I love it.
> You wouldnt happen to know a magic formula to require 6 or 16 characters
> (numbers, but entered as text) for validation like this?
> Thanks.
> Stefan
>
> Greg Wilson wrote:
>
> >=AND(LEN(A1) = 5,
> >ISNUMBER(VALUE(LEFT(A1,2))),MID(A1,3,1)="/",ISNUMBER(VALUE(RIGHT(A1,2))))
> >
>
>
> --
> Message posted via http://www.officekb.com
>

Hi Greg, I apologize, i feel like rainman now.
the input would ocure in the same cell (your A1 - my E15), either as
######
or
################ (16x #) actually, since excel cannot work with 16 digit
numbers, the cell IS formatted to text and the formatting would actually be
@'s instead of #'s

Once the data was input as shown above the output would change to
@@xx xxxx xxxx @@@@ or
@@@@ @@@@ @@@@ @@@@

The formula i have does check and format as wanted, but i dont know how to
apply this to datavalidation.
..=IF(LEN(E15)=6,LEFT(E15,2)&"xx xxxx xxxx "&RIGHT(E15,4),IF(LEN(E15)=16,LEFT
(E15,4)&" "&MID(E15,5,4)&" "&MID(E15,9,4)&" "&RIGHT(E15,4),"NOT"))
Thanks and greeting from Nevada.


Greg Wilson wrote:
>Assuming by six you mean "###/###":
>
>'=AND(LEN(A1) = 7,
>ISNUMBER(VALUE(LEFT(A1,3))),MID(A1,4,1)="/",ISNUMBER(VALUE(RIGHT(A1,3))))
>
>Assuming by 16 you mean "########/########":
>
>'=AND(LEN(A1) = 17,
>ISNUMBER(VALUE(LEFT(A1,8))),MID(A1,9,1)="/",ISNUMBER(VALUE(RIGHT(A1,8))))


--
Message posted via http://www.officekb.com

I'm still not sure I know what you're trying to do. Mainly the " xx xxxx xx "
has me confused. This is my take:

In the code module for the worksheet paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("E15")) Is Nothing Then
If Len(Target) = 6 Then
Target = Left(Target, 2) & " xx xxxx xx " & Right(Target, 4)
Else
Target = Left(Target, 4) & " " & Mid(Target, 5, 4) & " " & _
Mid(Target, 9, 4) & " " & Right(Target, 4)
End If
End If
Application.EnableEvents = True
End Sub

Format Data Validation for cell E15 as before except use this formula:
=OR(LEN(E15) = 6, LEN(E15) = 16)

Include appropriate text for the Input Message and Error Alert as before.

Note that the Data Validation prevents anything other than 6 or 16 character
entries. So the above Worksheet_Change code has been simplified.

Regards,
Greg

Hi Greg,

I had no problem using a worksheet as the source, providing I copied the
formula from the formula bar (F2, Ctrl-C, Enter, and then paste using Ctrl-V
as before.)

I often use this technique for complex defined names too.

---
Regards,
Norman



"Greg Wilson" <[email protected]> wrote in message
news:[email protected]...
> Thanks a million pointing that out Norman !!!
>
> I can't get it to work when I copy directly from a worksheet, whether
> using
> Ctrl-V, the right-click menu or through the Edit menu. Apparently, Excel
> kills what's on the clipboard when copied from a worksheet (i.e. when in
> CutCopy mode) when dialogs are opened. The same holds for the Conditional
> Formatting forumula window and the named range "Refers to" window.
>
> So, the answer appears to be to write your formulas in a code module, then
> copy and paste to either of the above using Ctrl-V. After all this time I
> never knew that. This will be extremely useful.
>
> Looks like I will benefit more from this post than stephan. Please advise
> if
> I'm missing something. Thanks again.
>
> Best regards,
> Greg
>

Thanks again Norman. I obviously havn't applied much lateral thinking on this
issue. I suspect I'm in the minority for not discovering this. I came to this
conclusion early on when I was a novice and have never assumed any different.
Better late than never !!!

To reiterate, this post will be of more benefit to me than to stephan.

Regards,
Greg

"Norman Jones" wrote:

> Hi Greg,
>
> I had no problem using a worksheet as the source, providing I copied the
> formula from the formula bar (F2, Ctrl-C, Enter, and then paste using Ctrl-V
> as before.)
>
> I often use this technique for complex defined names too.
>
> ---
> Regards,
> Norman
>
>
>
> "Greg Wilson" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks a million pointing that out Norman !!!
> >
> > I can't get it to work when I copy directly from a worksheet, whether
> > using
> > Ctrl-V, the right-click menu or through the Edit menu. Apparently, Excel
> > kills what's on the clipboard when copied from a worksheet (i.e. when in
> > CutCopy mode) when dialogs are opened. The same holds for the Conditional
> > Formatting forumula window and the named range "Refers to" window.
> >
> > So, the answer appears to be to write your formulas in a code module, then
> > copy and paste to either of the above using Ctrl-V. After all this time I
> > never knew that. This will be extremely useful.
> >
> > Looks like I will benefit more from this post than stephan. Please advise
> > if
> > I'm missing something. Thanks again.
> >
> > Best regards,
> > Greg
> >
>
>
>