activate cancel button in a inputbox

**mania112** · 10-19-2009, 05:52 AM

in vb, i have the following code to enter clients details, which works fine:

Sub NewUpdate()

    Dim DateRec As Date
    Dim ClientRef As String
    Dim ClientForename As String
    Dim ClientSurname As String
    Dim ClientEmail As String
    Dim NoOfPols As Integer
    Dim Polcount As Integer
    Dim Policies(1 To 9) As String
    Dim InsRow As Integer
    Dim DestCell As Integer
    
    
    DateRec = InputBox("Enter Date Factfind received (DD/MM/YY)", "Date Received")
    ClientRef = InputBox("Enter Client reference code", "Client RefCode")
    ClientForename = InputBox("Enter Client Forename", "Client Forename")
    ClientSurname = InputBox("Enter Client Surname", "Client Surname")
    ClientEmail = InputBox("Enter Client Email address", "Client Email")
    InsRow = 16
    DestCell = 15
    
    NoOfPols = InputBox("How many policies?", "No Of Pols")
    
    Polcount = NoOfPols
    
    Do While Polcount > 0
        Policies(Polcount) = InputBox("Policy Name", "Policy Name")
        Polcount = Polcount - 1
    Loop
    
    Sheets("#Template - do not delete").Select
    Sheets("#Template - do not delete").Copy After:=Sheets("#Template - do not delete")
    Sheets("#Template - do not delete (2)").Name = ClientRef
    Sheets(ClientRef).Range("F12").Value = DateRec
    Sheets(ClientRef).Range("C1").Value = ClientEmail
    Sheets(ClientRef).Range("C1").Font.Color = RGB(255, 255, 255)
    Sheets(ClientRef).Range("A8,F8").Value = ClientRef & " - Case Progression"
    
    Polcount = NoOfPols
    
    Do While Polcount > 0
        Sheets(ClientRef).Range("C" & DestCell).Value = Policies(Polcount)
        Polcount = Polcount - 1
        If Polcount > 0 Then
            Range("A" & InsRow).Select
            Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
            InsRow = InsRow + 1
            DestCell = DestCell + 1
        End If
    Loop
    
    Sheets("#Client List").Range("A200").Value = ClientSurname
    Sheets("#Client List").Range("B200").Value = ClientForename
    Sheets("#Client List").Range("C200").Value = ClientRef
    Sheets("#Client List").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Range("A200"), Address:="", SubAddress:= _
        ClientRef & "!A1", TextToDisplay:=ClientSurname
    Call SortAlpha
    Call SortWorksheets

We have some new staff starting next week, so i wanted to tidy a few things up, namely when you 'add new client' the inputbox appears, but at the moment if you hit cancel the code chokes and the usual vb runtime error appears.

all i wanted therefore is an idea of how to apply a line or two to allow the user to cleanly cancel and return to the 'client list' sheet.

i'm sure this is quite simple, i just hope it doesnt need a complete overhaul of the way this code's written.

many thanks

**Richard Buttrey** · 10-19-2009, 06:20 AM

Hi,


On Error Resume Next
DateRec = InputBox("Enter Date Factfind received (DD/MM/YY)", "Date Received")
If Err.Number = 13 Then Exit Sub
on Error Goto 0

Incidentally provided the user always enters dates in the ddmmyy format you will be OK, but you might want to consider what would happen if a US user say entered mmddyy. You might like to consider capturing the day, date and month in separate listboxes which contain days 1-31, months January - December and Year numbers. Then you can build the Date variable by using the DateSerial() instruction.

HTH

**mania112** · 10-19-2009, 07:08 AM

thanks, that worked great.

I have another problem while doing this 'tidy up'

i'll have to start a new thread on it, for some reason i cannot get to the manage attachments pane, hoping a new thread will solve the problem

**royUK** · 10-19-2009, 07:09 AM

Originally Posted by mania112

thanks, that worked great.

I have another problem while doing this 'tidy up'

i'll have to start a new thread on it, for some reason i cannot get to the manage attachments pane, hoping a new thread will solve the problem

You should start a new Thread for additional questions, add a link to this one if i might help.