VBA Msg box when value not found

  • Hello all
    I am brand new to coding and I would like to get a Msg box pop up when the provider code / cant be found.
    I would like the text to be " provider ID not found" please check provider number or case"


    This is that I have working.The User form search ID is be typed into "C3" and is case sensitive. The result is found in a separate sheet with provider ID , email and name. Just a simple DB
    If the ID typed into cell C3 does not match any of the ID's in the DB I would like an automatic msg box to pop up .



    can anyone assist with this code ....



  • Code Tags Added
    Your post does not comply with our Forum RULES. Use code tags around code.


    Posting code between

    Code

    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.


    Highlight your code and click the # icon at the top of your post window.


    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  • Ok I have that but it locks the whole form. What is the code to click OK and exit to the user form
    I have written this




  • If you have a userform why are you typing into the sheet? The best way is to have a combox on the userform, containing only the IDs used, something like the attached. That way you don't waste time looking for something that doesn't exist, especially using a inefficient method, i.e. looping. Attach an example of your workbook and I'll take a look.


    I've tidied up your code


  • Thanks for the code and yes I would like some help with it .
    As and explanation the "user form" is used to inform specific users that they have done some thing incorrectly. I could do a combo box however I would have to scroll down over 200 ID's to get the ID. So I thought having a separate sheet that contains all the names all the names with ID codes and email address then have the VBA code to search that sheet /Db would make it easier.
    I then need to email the user form to the specific id.. after that I need to clear the form of all information


    I am open to any suggestions to make it easier

  • A combobox has will try to anticipate as you type which would probably help quicken the process.


    have you tried my amendments to your code? Perhaps you could attach an example of the workbook.

  • Thanks Roy for taking the time to assist . I'll try and explain further . My task is to email providers a doc that indicates errors that have been made and requesting them to correct. I have access to Word or Excel . I have a list of providers about 200 with their associated emails
    I was thinking that the doc could get the provider Id from a list via a search to populate the doc. If a provider is not found then Id like to get and error message box saying"provider not found". I have attached a basic layout for you. I am open to all suggestions


    So essentially I require:


    1. search for the Provider ID
    2. error message if ID not found
    3. Clear button if I select the wrong ID or other process
    4. name and email address based on the provider ID . I have it in sheet 1
    5.error items .. I have these in a combo box at the moment
    6. email the doc. I found code that takes the sheet and converts to PDF then opens outlook attaches the file , adds a subject and body text
    7. Clear the form with one button but keeping the formatting so I can resend . I thought range would do this and it does but the formatting is getting altered


    This was a little long and it is a lot to ask but I am up for learning . What I found so far I have cobbled together and broken several times but I like a challenge and learning

  • Just having a look at your workbook. I've corrected your workbook_open code, but I don't think it applies to this workbook. The sheets don't exist and there's no data in Column G


  • I have added Conditional Formatting to turn the search cell red if a match does not exist.


    I've edited the code, but I don't know what compid is. I have also removed the empty modules.


    You could probably do this without VBA.

  • Hi Roy
    Really appreciate you taking the time to assist . I like the RED very obvious . Couple of questions is the search done on any of the headings " or just the "Student Provider" [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 120"]Student Provider[/TD]
    [TD="width: 361"]Email[/TD]
    [TD="width: 137"]Name [/TD]
    [TD="width: 131"]Name 2[/TD]

    [/tr]


    [/TABLE]

    I wanted to test so I added more names , which resulted in the clear search and clear form buttons not working .. is that normal ?

    i will continue to test if you could clarify that would be great

  • Hi Roy
    At the risk of you saying " Please god No !" is there any way that this can be adapted to a User form . The goal posts have changed , I suspect that this will be used by multiple users and I need to keep it restricted . I would also like to make it more professional in appearance .


    I have added a couple more items provided in the attachment .I added an additional line (219 ) with MML and email however it doesn't come up in search , is there a line limit or does an email rather than text stop the search process.


    Hope you can provide advice . in the mean time I will be reading as much as I can on user forms .

  • In your database workbook I have re-written the code for the UserForm to use the ListObject's properties.


    I'm not entirely sure what you mean about the other userform.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!