Setting global vars in Workbook Open not working

missingsc

New Member
Joined
Mar 14, 2024
Messages
19
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
What is the code to set the variable?
 
Upvote 0
Simple line:
VBA Code:
configOne = Worksheets("Configs").Cells(11,2).Value
 
Upvote 0
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?
 
Upvote 0
How about using the workbook activate event instead of the open event:
VBA Code:
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top