Message box/UserForm with lookup

[Dave H9]

Hi all, I am a novice with VBA so any help is much appreciated.

I have attached an example workbook. I am trying to set this up so that when the book is opened a message box appears asking the user if they have a reference number (YES or NO answer), if NO then in the sheet "Input Sheet" cell B2 should read "Not Applicable". If the answer is YES a second message box should request the user type their reference and check this against the sheet "Request Register" column A, if it exists then input into the sheet "Input Sheet" cell B2, if not retry.

I know this is probably easy for you pro's but I am struggling to even make a start.

Thank you

[Richard Buttrey]

Hi Dave,

Rather than try and re-invent the wheel with a userform and command buttons, just use the standard Message and Input boxes.

Call the following procedure from your workbook open event.

Please Login or Register  to view this content.

Regards

[Dave H9]

Hi Richard,

Thank you very much, this seems a much simpler approach. I am now trying to include a loop so only a valid reference or N/A is returned in cell B2.

I'll give it a go myself but any pointers would be appreciated.

Thanks again

[snb]

For a simpler approach see this file.

[Dave H9]

Thanks snb but I don't think this suits my application.

I am trying to get the message to loop if an invalid reference is used, the code I am using is as follows

Please Login or Register  to view this content.

I am probably missing the obvious but I have a few bugs I could do with sorting
1. The first inputbox can be closed or cancelled by the user. I managed to have the input box in the loop return "Not Applicable" if it is cancelled or closed but can't get it to work on the first.
2. Either of the input boxes will accept a value of "0". I'm sure this is due to the "lRow" element but I cannot find a workround.

Any suggestions please?

[Richard Buttrey]

Hi,

I like snb's approach of forcing the user to pick from a list, but if you want to use the MsgBox / Input Box route this simpler modified procedure will do what you want.

Please Login or Register  to view this content.

Regards

[Dave H9]

Thanks, I am trying to develop as part of a userform but at the moment I need to address the two bugs. I can then use a modified version to include in a userform.

Thank you