VBA Error Message

  • 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: http://img28.imageshack.us/img28/1803/10300400.png]

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

  • Re: VBA Error Message

    So is the name of the workbook wrong, or the name of the worksheet?

    You aren't providing enough informaton to make a useful suggestion.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • 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

    I still don't understand what you're trying to do. Maybe this:

    On Error Resume Next
        With Workbooks("Costing Sheet - Private Hire.xls").Worksheets("Data Input").Range("E6")
            .Value = .Value + 1
        End With
        On Error GoTo 0

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!