Setting global vars in Workbook Open not working

[missingsc]

Looking for some help on setting global variables when a workbook is opened. I have a set of configurations I would like to read in from a worksheet when the workbook is opened and set them as global variables. In Module1 I have a variable declared as Global. Then, in ThisWorkbook in the Workbook_Open function I attempt to read in the correct cell from the worksheet named "Configs". However, it always reads in as zero. If I hardcode the value, it sets the global var correctly, but when I try to read it from the worksheet it appears as if the worksheet data hasn't loaded in yet and it gets set to zero.

Any thoughts on what I'm doing wrong?

Thanks in advance!

 

[RoryA]

What is the code to set the variable?

 

[missingsc]

Simple line:

configOne = Worksheets("Configs").Cells(11,2).Value

 

[RoryA]

That should work assuming you mean B11, and you haven't also declared configOne in the same routine. What is in B11 - a literal value or a formula?

 

[Jaafar Tribak]

How about using the workbook activate event instead of the open event:

Private Sub Workbook_Activate()
    Static bFlag As Boolean
    If bFlag = False Then
        bFlag = True
        configOne = Worksheets("Configs").Cells(11, 2).Value
    End If
End Sub

 

[missingsc]

Ugh, this is user error it turns out. With your help and me reinspecting my code, I realize I transposed the row and column number in the Cells function. Ugh. Switched them around and all is well now.

Thank you for your help, but sorry for the wild goose chase!