Strange I know, but a few versions of Excel I am developing for do not seem to be working as expected. A user already has workbook A open. She opens workbook B, it runs the Workbook_Open code but bombs at the first range reference it encounters (in bold on second macro, below) because it thinks workbook A is still the active workbook and the range or worksheet does not appear in A. Well, I thought strange enough but ok - I'll put ThisWorkbook.Activate in the Workbook_Open macro. It STILL thinks workbook A is the active workbook at the point the excution stops with the error. Any suggestions? A workaround?
Private Sub Workbook_Open() Dim MetaUpdate As Boolean Application.ScreenUpdating = False Application.CellDragAndDrop = False If MsgBox("Update Metadata?", vbYesNo) = vbYes Then MetaUpdate = Update_Metadata() ThisWorkbook.Activate Call Update_Type_Dropdowns ** Routine bombs as soon as it runs this macro (below) Call All_But_Jobs Range("I_Dropdown_Refresh") = False Call Protect_All Application.ScreenUpdating = True End Sub
Sub Update_Type_Dropdowns() ' This changes EVENT DATA sheet. Which ACTIVITIES are allowed for each STORE TYPE? Dim rEvent As Range, col As Integer [B] With Worksheets("Event Data")[/B] .Range("Dropdown_Lists").Offset(1).Resize(250, 11).ClearContents For Each rEvent In .Range(.Range("Event").Offset(1), .Range("Event").Offset(65535).End(xlUp)) If rEvent <> "" And rEvent.Offset(, -1) <> "" Then If RangingValid(rEvent.Offset(, 2).Value) = True Then ' Check Event Ranging against Overview Ranging For col = 0 To 10 ' Go through each Event and add to permitted Activity Dropdowns If (rEvent.Offset(, 1).Value2 And 2 ^ col) = 2 ^ col Then Range("Dropdown_Lists").Offset(65535, col).End(xlUp).Offset(1) = rEvent End If Next End If End If Next End With End Sub