Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
if until requirement met
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bedroom requirement | Excel Worksheet Functions | |||
Sum with special requirement | Excel Worksheet Functions | |||
formula requirement | New Users to Excel | |||
Requirement in Bar chart | Charts and Charting in Excel | |||
Before Save Requirement | Excel Programming |