Global Variables - grrr!

I've got some code in Excel VBA and can't get it to run properly. Here's the situation:

There are two bits of code, one is part of a worksheet (embedded) and the other is a seperate module. There are two procedures in the module and I've got a macro set up on a button in the worksheet which calls one or the other of the procedures depending on the value of a Global Variable. The value of the global variable is set by choosing between 1 of 2 option buttons on the worksheet.

The problem I have is that I can't get the computer to run either of the two procedures. The code in the module basically goes like this:

Code:

---

If Global_Variable = 1 Then
run this piece of code

ElseIf Global Variable = 2 Then
run this piece of code

End If
End If

---

I know the problem is not with the run this piece of code and I've tracked it down to a problem with the global variable. I know that the correct values are been passed to it from the worksheet (depending on which option button is pressed) but the computer seems to completely ignore this.

I've tried declaring the variable as a Public variable all over: in the worksheet code, in the module declarations, in the ThisWorkbook section. But it still doesn't seem to want to recognise the variable when I try to make a decision based on it. However, it must know the value of the variable because I can write it to a cell! :mad:

What I am doing wrong?!?! :confused:

Cheers
-Rob

*Edit*

One thing I should mention is that I got it to work in a rather unelegant way. I made a cell on the worksheet equal the global variable, so if one option button is ticked it equals 1, if the other is ticked it equals 2, then set the code up to look like this:

Code:

---

If Range("A1") = 1 Then
run this piece of code

ElseIf Range("A1") = 2 Then
run this piece of code

End If
End If

---

That's how I know the run this piece of code works as I can make it do what I want by referencing a cell, but when I try to reference a global variable which the cell also depends upon it just doesn't work. :confused:

Here is a small demo of two option buttons triggering a public
function in a standard module. The function evaluates which
option button is selected and displays a msgbox of the selected
option.

Note: set your macro security level to medium so it can run.
Nothing else will run until an option is selected.

HTH

VB Code:

'This is all thats in there.
Public Function SomePublicFunction()
    If ActiveWorkbook.Sheets(ActiveWorkbook.ActiveSheet.Name).OptionButton1.Value = -1 Then
        MsgBox "Opt 1"
    ElseIf ActiveWorkbook.Sheets(ActiveWorkbook.ActiveSheet.Name).OptionButton2.Value = -1 Then
        MsgBox "Opt 2"
    Else
        'Etc
    End If
End Function

Excel debugging:
Put a break point in front of code in question
If break point is hit, step through (hope excel doesn't crash)
Use watch and locals windows to make sure variable has correct value
Use debug.print and msgbox to MAKE SURE variable has correct value

If variable has correct value on line before code, but not during line THEN:
Excel is playing games with you. See section 2.

Section 2:
-Comment and Uncomment the line. This has LITERALLY WORKED for me. Who knows why, some kind of twisted excel logic.
-Restart your computer to give you time to calm down and maybe fix some random problem. Consider buying a new computer w/o excel
-Move the function around, rename the variable, try random things
-Explode in anger