Posts by 047yneedss

    Re: VBA Error Message

    The error doesn't display when I open up the workbook Costing Sheet - Private Hire.

    That workbook is the main workbook. I then input data to go with a certain customer, and then save it under a new name. I save it as reference number, eg 101.

    The main workbook will automatically change a reference number when re-opened, but this is not needed for the other files, eg 101.

    When I open 101 - it gives me the error because the automatic reference changer it set for Costing Sheet - Private Hire.

    Therefore I need a statement that goes like this;

    If open Costing Sheet - Private Hire
    then do the automatic changer (code above)

    Else (so else any other file name)
    Don't change reference and don't show the error

    Re: VBA Error Message

    Yes I know.

    The file name is "Costing sheet - Private Hire'

    When I input all the data for the customer I then save it under the reference number.

    So then I have a file name 101 for example.

    When I re-open Costing Sheet it changes the reference number automatic = GREAT

    Now when I open 101 it gives me the error because the reference number changing is link for "Costing sheet - Private Hire".

    So to stop the error I need to put an IF or ELSE statement so;
    1. So it only changes the ref no in Costing sheet - Private Hire
    2. It doesn't show the error to tell me that the file name is wrong.

    I know why the error is happening, it's because once I've created a quote for a customer I save it with a reference number eg 101. The code below changes the ref no on launch, I only want it to change on the sheet name Costing Sheet - Private Hire.xls

    Therefore, when I open the file 101 I get the below error message. Do I need and ELSE or IF statement somewhere?

    Error message and code below.

    With Workbooks("Costing Sheet - Private Hire.xls")
                With Sheets("Data Input")
                .Range("E6").Value = .Range("E6").Value + 1
                End With
            End With

    [Blocked Image:]

    I have the below code to save the quotation, and it asks what to save the file as within a InputBox.

    Is there a way of this being automatically generated so Q0001, Q0002 and so on ...

    I'm currently developing a system for a coach company where they'll be able to write out quotations and save/open them all at a click of a button.

    I've done the Save button, but I can't get my head around the open. I've written the following code below;

    Sub Open_Quotation()
        Dim sFile As String
        Dim MyFilename As String
        MyFilename = InputBox("Enter quotation number") & ".xls"
        sFile = "..\Desktop\NT System\Private Hire\Quotations" & MyFilename
        Workbook.Open Filename:=sFile
    End Sub

    I just can't get it to work.

    Some help would be appreciated.