nigelandrewfoster
Well-known Member
- Joined
- May 27, 2009
- Messages
- 747
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?
Code:
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
Code:
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
Last edited: