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.


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


    [Blocked Image: http://img28.imageshack.us/img28/1803/10300400.png]

    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;


    Code
    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.