Message Box before opening Workbook asking for Telephone number

[Darren]

Hi all

Been a while :hi:

This problem reverts to an upgrade i would like to do on a excel document i created with the help of Malcolm and Xld and a few others. i have attached the working file but i would like to upgrade it a little.

Happens at the moment...When the user clicks the file from the desktop shortcut the file open up and the user must enter a phone number in the active box. Then by pessing the tab key the current program searches the database and if it finds a match then it returns the information back to the sheet in the relevant boxes. This information is stored in DATABASE BUILDING worksheet. To test copy and paste a telephone number from this worksheet then press tab.... this all works well and data is put into the Tax Reciept correctly. To cut a long story short i would like to upgrade a little this is what i would like to happen

When the user open the Excel document from the desktop a Message Box opens asking for the Telephone Number before opening the workbook. The user then enters the Telephone Number and clicks OK. On clicking ok VBA will search for the telephone number in the Database Building worksheet and if found return the result in C7 in the worksheet DEFAULT PICKUP PROTECTED LAYOUT :help if no result is found return a message box that says "Number not in database would you like to create a new entry" yes or no. On yes open New Customer.xls if no close workbook

Not that cluded up but help would be appreciated

Kindest regards

Darren
South Africa

[Bob Phillips]

Try this

[Darren]

Hi XLD

Thank you for the quick reply. I have had a look at your solution and this is what i want. The information that needs to be returened is in coloum A starting at A2. this is a concantenated number or maybe it is seen as text at the moment the script is looking at coloum N and range N1 this only returns the first three digits of the telephone number. The TAB ORDER and GETTELNOS are not in this workbook. Also what may help you help me is i have Define - Names - Telnos

if the telephone number is not found then open a different file located on the C: drive called New Customer.xls

Thanks so much for your help

Darren

[Bob Phillips]

Is that what you mean?



Private Sub Workbook_Open()
Dim Tmp As String
Dim cell As Range

Tmp = InputBox("Please supply telephone number")
If Tmp <> "" Then

With Worksheets("DATABASE BUILDING")

Set cell = .Range("TelNos").Find(Tmp, After:=.Range("TelNos").Cells(1, 1))
If Not cell Is Nothing Then

Worksheets("DEFAULT PICKUP PROTECTED LAYOUT").Range("C7").Value = Tmp
Exit Sub

Else

If MsgBox("Number not in database would you like to create a new entry?", vbYesNo, "Database fail") = vbYes Then

.Activate
Exit Sub
Else

Workbooks.Open "C:\Database.xls"
End If
End If
End With
End If

ThisWorkbook.Close savechanges:=False
End Sub