Method 'Range' Error When Code is Run Automatically When Opening Workbook

Hi,

I believe that the event is in an Add-IN and when you open a workbook the addin is opened first but as there are no sheets in an addin you cannot get a value there, try:

Private Sub App_WorkbookOpen(ByVal wb As Workbook)
    If Not wb.IsAddin Then
        CheckBook
    End If
End Sub

Thanks guys, tried both solutions but with no luck.

jason's solution gave an "ambiguous" error while tehneXus' left me with the exact same problem.

Have you tried specifying the workbook and worksheet you want to check C1 in?

You don't need to know the workbook name.

This sub is passed a variable, wb, which should refer to the workbook that's just been opened.

Private Sub App_WorkbookOpen(ByVal wb As Workbook)
    CheckBook
End Sub

So you could pass that variable to the CheckBook sub and use it there.

This is how you would pass it.

Private Sub App_WorkbookOpen(ByVal wb As Workbook)
    CheckBook wb
End Sub

This is how you would use it

Sub CheckBook(wb As Workbook)
    'Only run if correct worksheet opens
    If Not wb.ActiveSheet.Range("C1").Value = "Transfer Bookings Report" Then
            Exit Sub
    End If
    DialogBox1.Show
End Sub

By the way, what sheet is active when you get an error?

Could it possibly be a chart sheet or similar?

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic


PS What exactly is the code meant to do?

Where is DialogBox1 located and what is it?

Hello,

Attached a sample workbook, with personal information omitted.

Also below here is the full code:

In ThisWorkbook I have:

Private WithEvents App As Application

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal wb As Workbook)
    If Not wb.IsAddin Then
        CheckBook
    End If
End Sub

In a module I have:

Sub CheckBook()
    'Only run if correct worksheet opens
    If Not Range("C1").Value = "Transfer Bookings Report" Then
    Exit Sub
    End If
    DialogBox1.Show
End Sub

DialogBox1 gives a yes no option to "tidy the report" by running the sub TidyUp.

Sub TidyUp()

'Only run if correct worksheet opens
        Dim wb As Workbook
        With wb
        If Not Range("C1").Value = "Transfer Bookings Report" Then
        Exit Sub
        End If
        End With
        
Application.ScreenUpdating = False

'Remove logo area if it exists still
        If Range("C1").Value = "Transfer Bookings Report" Then
            Range("A1:A12").EntireRow.Delete
        End If
        
'Searches for empty columns and delete them
    Range("A1:AN500").UnMerge
    Dim CCount As Long
    On Error Resume Next
    
    With Range("A1:AL1")

        CCount = .SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count

        If CCount = 0 Then
            MsgBox "There are no blank cells"
        ElseIf CCount = .Cells.Count Then
            MsgBox "There are more then 8192 areas"
        Else
            .SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
        End If

    End With
 
    On Error GoTo 0

'Remove unwanted columns
    If Range("A1").Value = "Source" Then
            Range("A1:B1").EntireColumn.Delete
        End If
    If Range("B1").Value = "Booking Id" Then
            Range("B1:C1").EntireColumn.Delete
        End If
    If Range("U1").Value = "Accommodation Start Date" Then
            Range("U1").EntireColumn.Delete
        End If
    If Range("V1").Value = "Accommodation note" Then
            Range("V1").EntireColumn.Delete
        End If
    If Range("W1").Value = "Iac Contact" Then
            Range("W1:Z1").EntireColumn.Delete
        End If
        
'Replacement code for inbound/outbound and female/male
        Range("A1").Value = "In/Out"
        Range("F1").Value = "M/F"
        Range("B1").Value = "Student ID"
        
    With Range("H:H")
        Cells.Replace What:="Female", Replacement:="F", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
    
    With Range("H:H")
        Cells.Replace What:="Male", Replacement:="M", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
    
    With Range("A:A")
        Cells.Replace What:="Inbound", Replacement:="I", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
    
     With Range("A:A")
        Cells.Replace What:="Outbound", Replacement:="O", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With

        
'Adjust height, width and formatting
    Dim Lastrow As Long
    Lastrow = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A2:Z" & Lastrow).RowHeight = 100
    Rows("1").RowHeight = 60
    Range("A1:Z" & Lastrow).Font.Size = 16
    
    Columns("A").EntireColumn.AutoFit
    Columns("B").ColumnWidth = 18.14
    Columns("C").ColumnWidth = 18.71
    Columns("D").ColumnWidth = 22
    Columns("E").ColumnWidth = 20.46
    Columns("F").EntireColumn.AutoFit
    Columns("G").ColumnWidth = 7.14
    Columns("L").ColumnWidth = 16.29
    Columns("J").ColumnWidth = 19.29
    Columns("K").ColumnWidth = 19.71
    Columns("L").ColumnWidth = 18.71
    Columns("U").ColumnWidth = 42.14
    Columns("V").ColumnWidth = 32
    
ActiveWindow.Zoom = 40

Application.ScreenUpdating = True
Range("A1").Select
End Sub

Selecting either option on DialogBox1 also opens a new dialogbox which offers the option to remove people with no travel information by running the sub RemoveBlanks.

Sub RemoveBlanks()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With ActiveSheet

        .Select
        
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        .DisplayPageBreaks = False

        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        
        For Lrow = Lastrow To Firstrow Step -1

        If Application.CountA(.Cells(Lrow, 1).Range("H1:S1")) = 0 Then .Rows(Lrow).Delete

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

All of this code is in the PERSONAL.XLSB so that it runs every time.

You'll need to fully close Excel to experience the error. Like I say if you put the code in and run it when excel is already open it will work completely fine. But if you open the file before excel is opened it will attempt to run the code (as I want it to) but it won't work.

Sorry, you've lost me.

There's no code in the workbook you attached.

If all this code is in your PERSONAL.XLSB that might be the problem - I would have expected the code to be in an add-in.

Can I ask exactly what the process is?

Are you downloading workbooks and then opening (with/without saving) them?

Then the code should be executed, and DialogButton1 should only be shown if the downloaded workbook's active sheet has "Transfer Bookings Report" in B3.

By the way, when did you start having problems with the code?

Have you tried saving the workbook(s) first?

I think the problem could be because the code is running on PERSONAL.XLSB.

You check if the workbook being opened is an add-in.

Now as far as I know PERSONAL.XLSB isn't an add-in so it doesn't get caught by that check and the code proceeds.

Since there are no worksheets in PERSONAL.XLSB you get the error.

That makes sense. Would there be a way to point it to the workbook which is being opened? Otherwise perhaps I can run something which opens up a sheet in PERSONAL.XLSB while the macro runs and then closes it again afterwards hopefully all in the background.

Edit: Actually it wouldn't sorry. Since the error occurs on

If Not Range("C1").Value = "Transfer Bookings Report" Then

and not at an earlier point.

Edit Again: Just looking into it more it is almost definitely a problem in the fact that the macro runs through before the workbook has had a chance to open. So I need to find a way to stop the macro running until the workbook is open. I could do this by instead making the personal.xlsb open a dialog box asking if I want to run the macro instead and then clicking yes or no to run everything I ran automatically every time before but this seems like a bad workaround so has anyone any other ideas?

I'm pretty sure the problem is that the code runs on PERSONAL.XLSB as that's actually the first workbook that's opened when you open Excel.

Pretty sure the last time I used a PERSONAL workbook that's what happened, I can even remember the splash screen showing a progress bar with Loading PERSONAL.XLSM..., or something similar.

Why not add a check to see if the workbook that's been opened is the PERSONAL.XLSB, if it is don't run the code and if it isn't run the code?

Private Sub App_WorkbookOpen(ByVal wb As Workbook)
    If wb.Name <> "PERSONAL.XLSB" Then
        CheckBook
    End If
End Sub