Msgbox entry validation

The following Macro will not loop more than twice the error entries.
Will someone help me correct it to loop indefinely? Thanks

Sub Entry_validation()
  Dim Teststring As String
  Dim Inputstring As String

      Cells(1, 1) = "ABC"         'as given filename
      Cells(2, 1) = "DEF"         'as given filename
      Cells(3, 1) = "GHI"         'as given filename
      Cells(4, 1) = "JKL"         'as given filename

  errorloop:
      Inputstring = InputBox(prompt:="Enter Filename" & vbLf & "Enter abort
to abort entry" & "")
      If Inputstring = "abort" Then GoTo eof
      On Error GoTo errornote
      Teststring = Application.Match(Inputstring, Range("A1:A10"), 0)

      MsgBox "A valid entry."

      GoTo eof
  errornote:
      MsgBox "Not a valid entry." & vbLf & "Try again"
      GoTo errorloop
  eof:
End Sub


0
danchen (87)
9/19/2004 1:49:31 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
983 Views

Similar Articles

[PageSpeed] 39

Hi David,

Try:

Sub Entry_validation()
    Dim Inputstring As String
    Dim boolValidEntry As Boolean

    Cells(1, 1) = "ABC"         'as given filename
    Cells(2, 1) = "DEF"         'as given filename
    Cells(3, 1) = "GHI"         'as given filename
    Cells(4, 1) = "JKL"         'as given filename
errorloop:
    Do While boolValidEntry = False
        Inputstring = InputBox(prompt:="Enter Filename" _
                    & vbLf & "Enter abort to abort entry" & "")
        If LCase(Inputstring) = "abort" Then Exit Sub
        If Not IsError(Application.Match _
                       (Inputstring, Range("A1:A10"), 0)) Then
            GoTo eof
        Else
            MsgBox "Not a valid entry." & vbLf & "Try again"
            GoTo errorloop
        End If
    Loop

errornote:
    MsgBox "Not a valid entry." & vbLf & "Try again"
    GoTo errorloop
eof:
    MsgBox "A valid entry."
    boolValidEntry = True
End Sub

---
Regards,
Norman



"daniel chen" <danchen@worldnet.att.net> wrote in message 
news:LA53d.606937$Gx4.2532@bgtnsc04-news.ops.worldnet.att.net...
> The following Macro will not loop more than twice the error entries.
> Will someone help me correct it to loop indefinely? Thanks
>
> Sub Entry_validation()
>  Dim Teststring As String
>  Dim Inputstring As String
>
>      Cells(1, 1) = "ABC"         'as given filename
>      Cells(2, 1) = "DEF"         'as given filename
>      Cells(3, 1) = "GHI"         'as given filename
>      Cells(4, 1) = "JKL"         'as given filename
>
>  errorloop:
>      Inputstring = InputBox(prompt:="Enter Filename" & vbLf & "Enter abort
> to abort entry" & "")
>      If Inputstring = "abort" Then GoTo eof
>      On Error GoTo errornote
>      Teststring = Application.Match(Inputstring, Range("A1:A10"), 0)
>
>      MsgBox "A valid entry."
>
>      GoTo eof
>  errornote:
>      MsgBox "Not a valid entry." & vbLf & "Try again"
>      GoTo errorloop
>  eof:
> End Sub
>
> 


0
normanjones (1047)
9/19/2004 3:10:41 AM
Hi David,

I omitted to delete redundant bits of legacy code.

Replace the code with:

Sub Entry_validation()
    Dim Inputstring As String
    Dim boolValidEntry As Boolean

    Cells(1, 1) = "ABC"         'as given filename
    Cells(2, 1) = "DEF"         'as given filename
    Cells(3, 1) = "GHI"         'as given filename
    Cells(4, 1) = "JKL"         'as given filename

    Do While boolValidEntry = False
        Inputstring = InputBox(prompt:="Enter Filename" _
                    & vbLf & "Enter abort to abort entry" & "")
        If LCase(Inputstring) = "abort" Then Exit Sub
        If Not IsError(Application.Match _
                       (Inputstring, Range("A1:A10"), 0)) Then
            GoTo eof
        Else
            MsgBox "Not a valid entry." & vbLf & "Try again"
        End If
    Loop

eof:
    MsgBox "A valid entry."
    boolValidEntry = True
End Sub


---
Regards,
Norman



"Norman Jones" <normanjones@whereforartthou.com> wrote in message 
news:ObtWAZfnEHA.716@TK2MSFTNGP10.phx.gbl...
> Hi David,
>
> Try:
>
> Sub Entry_validation()
>    Dim Inputstring As String
>    Dim boolValidEntry As Boolean
>
>    Cells(1, 1) = "ABC"         'as given filename
>    Cells(2, 1) = "DEF"         'as given filename
>    Cells(3, 1) = "GHI"         'as given filename
>    Cells(4, 1) = "JKL"         'as given filename
> errorloop:
>    Do While boolValidEntry = False
>        Inputstring = InputBox(prompt:="Enter Filename" _
>                    & vbLf & "Enter abort to abort entry" & "")
>        If LCase(Inputstring) = "abort" Then Exit Sub
>        If Not IsError(Application.Match _
>                       (Inputstring, Range("A1:A10"), 0)) Then
>            GoTo eof
>        Else
>            MsgBox "Not a valid entry." & vbLf & "Try again"
>            GoTo errorloop
>        End If
>    Loop
>
> errornote:
>    MsgBox "Not a valid entry." & vbLf & "Try again"
>    GoTo errorloop
> eof:
>    MsgBox "A valid entry."
>    boolValidEntry = True
> End Sub
>
> ---
> Regards,
> Norman
>
>
>
> "daniel chen" <danchen@worldnet.att.net> wrote in message 
> news:LA53d.606937$Gx4.2532@bgtnsc04-news.ops.worldnet.att.net...
>> The following Macro will not loop more than twice the error entries.
>> Will someone help me correct it to loop indefinely? Thanks
>>
>> Sub Entry_validation()
>>  Dim Teststring As String
>>  Dim Inputstring As String
>>
>>      Cells(1, 1) = "ABC"         'as given filename
>>      Cells(2, 1) = "DEF"         'as given filename
>>      Cells(3, 1) = "GHI"         'as given filename
>>      Cells(4, 1) = "JKL"         'as given filename
>>
>>  errorloop:
>>      Inputstring = InputBox(prompt:="Enter Filename" & vbLf & "Enter 
>> abort
>> to abort entry" & "")
>>      If Inputstring = "abort" Then GoTo eof
>>      On Error GoTo errornote
>>      Teststring = Application.Match(Inputstring, Range("A1:A10"), 0)
>>
>>      MsgBox "A valid entry."
>>
>>      GoTo eof
>>  errornote:
>>      MsgBox "Not a valid entry." & vbLf & "Try again"
>>      GoTo errorloop
>>  eof:
>> End Sub
>>
>>
>
> 


0
normanjones (1047)
9/19/2004 3:18:56 AM
Hi, Norman
It is great.  I appreciate it very much. Have a nice weekend!

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:%23HrNndfnEHA.536@TK2MSFTNGP11.phx.gbl...
> Hi David,
>
> I omitted to delete redundant bits of legacy code.
>
> Replace the code with:
>
> Sub Entry_validation()
>     Dim Inputstring As String
>     Dim boolValidEntry As Boolean
>
>     Cells(1, 1) = "ABC"         'as given filename
>     Cells(2, 1) = "DEF"         'as given filename
>     Cells(3, 1) = "GHI"         'as given filename
>     Cells(4, 1) = "JKL"         'as given filename
>
>     Do While boolValidEntry = False
>         Inputstring = InputBox(prompt:="Enter Filename" _
>                     & vbLf & "Enter abort to abort entry" & "")
>         If LCase(Inputstring) = "abort" Then Exit Sub
>         If Not IsError(Application.Match _
>                        (Inputstring, Range("A1:A10"), 0)) Then
>             GoTo eof
>         Else
>             MsgBox "Not a valid entry." & vbLf & "Try again"
>         End If
>     Loop
>
> eof:
>     MsgBox "A valid entry."
>     boolValidEntry = True
> End Sub
>
>
> ---
> Regards,
> Norman
>
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:ObtWAZfnEHA.716@TK2MSFTNGP10.phx.gbl...
> > Hi David,
> >
> > Try:
> >
> > Sub Entry_validation()
> >    Dim Inputstring As String
> >    Dim boolValidEntry As Boolean
> >
> >    Cells(1, 1) = "ABC"         'as given filename
> >    Cells(2, 1) = "DEF"         'as given filename
> >    Cells(3, 1) = "GHI"         'as given filename
> >    Cells(4, 1) = "JKL"         'as given filename
> > errorloop:
> >    Do While boolValidEntry = False
> >        Inputstring = InputBox(prompt:="Enter Filename" _
> >                    & vbLf & "Enter abort to abort entry" & "")
> >        If LCase(Inputstring) = "abort" Then Exit Sub
> >        If Not IsError(Application.Match _
> >                       (Inputstring, Range("A1:A10"), 0)) Then
> >            GoTo eof
> >        Else
> >            MsgBox "Not a valid entry." & vbLf & "Try again"
> >            GoTo errorloop
> >        End If
> >    Loop
> >
> > errornote:
> >    MsgBox "Not a valid entry." & vbLf & "Try again"
> >    GoTo errorloop
> > eof:
> >    MsgBox "A valid entry."
> >    boolValidEntry = True
> > End Sub
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "daniel chen" <danchen@worldnet.att.net> wrote in message
> > news:LA53d.606937$Gx4.2532@bgtnsc04-news.ops.worldnet.att.net...
> >> The following Macro will not loop more than twice the error entries.
> >> Will someone help me correct it to loop indefinely? Thanks
> >>
> >> Sub Entry_validation()
> >>  Dim Teststring As String
> >>  Dim Inputstring As String
> >>
> >>      Cells(1, 1) = "ABC"         'as given filename
> >>      Cells(2, 1) = "DEF"         'as given filename
> >>      Cells(3, 1) = "GHI"         'as given filename
> >>      Cells(4, 1) = "JKL"         'as given filename
> >>
> >>  errorloop:
> >>      Inputstring = InputBox(prompt:="Enter Filename" & vbLf & "Enter
> >> abort
> >> to abort entry" & "")
> >>      If Inputstring = "abort" Then GoTo eof
> >>      On Error GoTo errornote
> >>      Teststring = Application.Match(Inputstring, Range("A1:A10"), 0)
> >>
> >>      MsgBox "A valid entry."
> >>
> >>      GoTo eof
> >>  errornote:
> >>      MsgBox "Not a valid entry." & vbLf & "Try again"
> >>      GoTo errorloop
> >>  eof:
> >> End Sub
> >>
> >>
> >
> >
>
>


0
danchen (87)
9/19/2004 3:58:05 AM
Reply:

Similar Artilces:

Message Tracking with Multiple Entries
I am experiencing similar issues to this article: http://support.microsoft.com/kb/330093 However, I am running Exchange 2003 SP2 and the sender is not sending from a PDA device and I don't have ActiveSync enabled. Anybody have any ideas? ...

MsgBox and a pop up form
I have an application that pop up a form (vbModal) asking for password in order to proceed. It pops up after a period of idling. It works prettty well. The hassle comes when there is a MsgBox popping up and the user does not respond, then the modal form pop up. Now I got 2 modal things, and you cannot click anything to move on. I have been sidestepping the problem by disabling the modal form whenever I show msgbox. I am wondering if there is a better way to do this. "phil hunt" <a@abc.com> wrote in message news:i43f9i$k9b$1@speranza.aioe.org... >I have...

Sheet msgbox
I have a msgbox that I wish to appear when a sheet is active, I hav tried the code: Private Sub Workbook_SheetActivate But that doesnt seem to be working. Any ideas as to how to fix thi problem? Regards -- Message posted from http://www.ExcelForum.com Hi you need to put the code in the workbook module of your workbook. See http://www.cpearson.com/excel/events.htm for instructions -- Regards Frank Kabel Frankfurt, Germany > I have a msgbox that I wish to appear when a sheet is active, I have > tried the code: > Private Sub Workbook_SheetActivate > > But that doesnt se...

Journal Entry Notes
When attaching supporting spreadsheets to the Note of a Journal Entry file, the attachment/spreadsheet can be modified after the journal entry has been posted. Is there a way to turn that functionality off, either in a global or user security setting? password protect the spreadsheet in Excel "Dan" wrote: > When attaching supporting spreadsheets to the Note of a Journal Entry file, > the attachment/spreadsheet can be modified after the journal entry has been > posted. > > Is there a way to turn that functionality off, either in a global or user > securi...

MsgBox
Hi, How can you insert a enter in your MsgBox? Fe. --------------------------------------------------------------------------- MsgBox prompt:="Please note, there a no files in this directory." "Please log off know, and restart the application." _ , Title:="OOCL Extractor, release version 5.1", Buttons:=vbOKOnly + vbExclamation --------------------------------------------------------------------------- This wil not work, so what is the solution for this? Thanks as always, Mischa Do you mean to wrap the message on m...

Alt + A doesn't work in Payables Trx Entry Distribution Window
In Version 9, there's a problem with the Alt+A keyboard shortcut in the Payables Trx Entry Distribution Window. When you use the Alt+A it accessed Transactions from the Menu rather than selcting the Default button from the Distribution Window. There are other problems with Alt + keys as well so I'm sure this just needs to be added to the problem report list. Thanks, Peggy Aitken Aitken Accounting Services (972) 365-8611 ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click th...

How to stop pop-up re MS Office is not valid?
Every time I open Microsoft Office a pop-up stating that my program is not valid and it has Microsoft Advantage which takes me to microsoft web site wanting to sell me an updated and valid program. I am feeling harassed by this and would like to get this pop-up off my M.O. thanks for any help Buy a genuine copy of Office instead of a pirated one. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "de6lab" <de6lab@discussions.microsoft.com> wrote in message news:00A5DC2E-EB74-4645-8FC8-343E96B...

duplicate entries in To field
I am experiencing a problem when I create new mail messages. Whenever I chose a contact from the global address list Outlook creates a duplicate entry in the "To:" field. Is there a setting that I have checked off or need to check off? Any assistance would be greatly appreciated. ...

Valid CWinThread handles
Hello fellow newsgroupies! Is there a way to find out whether or not a CWinThread process has stopped or not or is still valid. I have several CWinThread's (none of which are set to auto-delete) which may occasionally finish their processes before I signal them to stop. I sometimes need to reuse these CWinThread's and restart their processes but I need to know that they are no longer running first. They may already have their stop CEvent set but the thread may have already acted on it and stopped. Their m_hThread values will not be NULL but no longer handles to valid threads. Any s...

Data validation with a cell value linked to the same cell.
I Think that my post name is very confusing but I'll explain. I�m doing a data validation in cell f1 usig the data validation option the value entered in cell f1 need to be equal or greatter than th value in cell b2 and cell b2 is calculated with the formula =a2+c4-f1. What I'm trying to do is to prevent to enter a value in cell f1 tha I'll make cell b2 negative. But its not working, any suggetsions??? Thanks. Rodolf -- Message posted from http://www.ExcelForum.com Change your CF criterion to Formula is = F1 <= (A2+C4) In article <rodolform.161w07@excelforum-n...

On POS (not RMS) Adjusting Time Clock Entry Defaults to Todays Date
When an employee negelects to time out and we make adjustments on a subsequent day, the Time Clock display will default to today's date even though the correct date is selected in the Employee's Time Clock window. You have to select Save and Close, then reopen the Time Clock event, then adjust the time out time to the correct date. Anybody else notice this bug? M Kalmus Dogtooth Coffee Company ...

Validation, VlookUp
I have a table with 4 columns. 3 columns (Product, Mode, City) have data that I choose in 3 validation lists. In turn, I want to match up (link) the Product, Mode and City from the lists, look them up in the table to get the number of units. I tried to use the offset /vlookup options but could not match the 3 items. As an example: Product: A, Mode: TC, City: Salem. In matching these the Number of units is 34. Formula would go in the yellow cell. Attached is the spreadsheet. Thanks......Comicfly Product Mode location Number of A TT NYC 20 A A TC Salem 34 TC A DR Richmon...

Finding records with no entry within past year
I have two tables [Tbl_Customers] and [Tbl_Orders] these are linked by [CustID]. When orders are entered into [Tbl_Orders] a [Date of Order] field is populated with current date. I am trying to write a query that will show what customers have not placed an order within the past 365 days. I have an expression that will show orders within a year {Between Date() And (Date()-365)} but that's as far as I can take it. Thanks, -- Message posted via http://www.accessmonster.com See: Subquery basics: Identifying what is NOT there at: http://allenbrowne.com/subquery-01.html The firs...

Validation and entering data
Hi, I've got two spreadsheets. One has got data on, and one is blank but with validation on (from the data -> validation menu). I need to get the data onto the sheet with the validation and got through the validation process. If you paste or use vb commands such as range or cell, the validation is bypassed. Is there a way of simulating the act of typing in the data into the spreadsheet? Unfortunately I don't have control from either the source sheet, or the destination, but I can set up a routine to transfer the data. Thank you Ian ...

Data Entry #3
We are into BPO and Software development business for past six and half years. We are looking for the following any kind of business from your end. Back Office Process Data Entry, Large Volume Data Processing, Data Conversion, Forms Processing, Process flow & Quality Data Entry Text, Numeric or Alphanumeric entry, Printed or Handwritten matter, Hardcopies or Scanned Images, Some typical examples include, Mortgage Documents, Abstract records, Index cards, Market Research Tabulations, Various different forms, Guest/Customer comment cards, Property tax records, Accounting and Book keepin...

Help with InputBox and MsgBox
How do I fix the below code so if a user does not enter anything in the input box or clicks on the cancel button they get what I have in the MsgBox? MsgBox ("You did not enter a new pay period start date" & vbCrLf & _ "click on the 'EnterNewPayPeriod' button and re-enter the new pay period") Sub NewStartDate() Dim Prompt, Title As String Prompt = "What's the new pay period start date?" Title = "Start Date" StartDate = InputBox(Prompt, Title) End Sub -- Mike Mast Special Education Preschool Teacher Sub NewStartDat...

sending updated calendar entry doesn't update sender
a client is sending an updated calendar appointment (from someone's calendar) and it updates everyone once they have accepted the update but it does not update the calendar from the person she is sending it from. any ideas? ...

Preserve Excel formula entry
When I type an arithmetic formula into a cell, excel automatically condenses it. For instance, =3/27 becomes =.1111111. I am not referring to the displayed value, but the actual entry in the cell. How can I turn this "feature" off? It is important, because when I return to the sheet months later, the full formula helps me to remember how I decided to calculate the result. Hi odd, in my version (2003), when i click on the cell and look at the formula bar i see =3/27 and the cell displays 0.111111, do you actually see =.1111111 in the formula bar? Cheers JulieD "...

Smartlist Goto
We have created our own Smartlist with SOP Work/Open/History Tables and Union into one list. Does anyone know if you can option to open different SOP Entry or Inquiry depending on the document if in Work/Open/History? Thanks in Advance, T I worked on it for a month and finally created two smartlists, one for open and one for history. I would love to know how as well but tech support could not explain how. They said it is a known issue in SLB. -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our boo...

Msgbox
Hi I have a message box that pops up under certain conditions . At presen the text on this box is on long line . Is it possible to start some o the message on a new line where I specify Thank -- Message posted from http://www.ExcelForum.com MsgBox "abc" & Chr(13) & "abc" The chr(13) gives [enter] so starts the new line. Dunca -- Message posted from http://www.ExcelForum.com Perfect - Thanks for your help -- Message posted from http://www.ExcelForum.com And in case you did't know, you can add spaces to center the message if you want. Something li...

Superscript entries
Is it possible to show supersript in a text entry in excel for example the symbol for H2O or Cubic metres - m3?? TIA Mick Please don't multi-post. See answers to your other post in the excel group. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "MGT" <no1@nospam.com> wrote in message news:cch0gj$6mn$2@news5.svr.pol.co.uk... Is it possible to show supersript in a text entry in excel for examp...

Validating in-memory XML against in-memory DTD?
Hi I need to validate an in-memory XML document against an in-memory DTD. I've tried using XmlValidatingReader and it works but the compiler complains that the class is obsolete. Here's the code: string dtd = "<!ELEMENT bookstore (book)*> " + "<!ELEMENT book (title,author*,price)>" + "<!ATTLIST book genre CDATA #REQUIRED>" + "<!ELEMENT title (#PCDATA)>" + "<!ELEMENT author (name | (first-name,last-name...

AUTO_OPEN MSGBOX with button to perminently stop it
Im trying to create a MSGBOX in my AUTO_OPEN macro that displays message and has the YES/NO option. If Yes is pushed the MSGBOX will po up each time the workbook is opened, if NO is pushed the MSGBOX will no open again. Does anyone know a way of doing this -- Message posted from http://www.ExcelForum.com Hello One very simple but tricky way would be to place some value in a cell of your workbook (1/0 or TRUE/FALSE for example) and then test this value in your auto_open macro to choose the relevant action. HTH Cordially Pascal "LB79 >" <<LB79.1atgtr@excelforum-nospam.c...

Purchase Order Entry Error
We are using GP 7.0 SQL 2000. We have a problem a user Great Plains froze on him and he got locked out. He had to be deleted from the user activity log. When he went to edit the PO he got an error message "this transaction is being edited by another user". There is no one else editing that PO. When I try to edit the PO I get the same error message. Does anyone know how to fix this problem? Thank you, Kevin Kbrown, Go to SQL server or query analyzer and run a delete statement on the SY00800 and SY00801 DELETE FROM SY00800 WHERE (USERID = 'xxx') DELE...

sales transaction entry window
i have a user that when they go to the sales transaction entry window to do a sales transaction it comes up with this error: Your previous transaction-level posting session has not finished procesing. Please allow time for it to finish. if you believe it has failed, log out of great plains and log back in to recover transactions. The use has logged out and back in but it still says this. Does anyone know what causes this and how it can be resolved? Thank you Christian See Techknowledge 852623 for instructions on this error. >-----Original Message----- >i have a user that whe...