Passing response value between thisworkbook and standard module

[stapuff]

I am doing a simple xml webservice macro call to determine the users security clearance....which returns a GetRestWebServiceValue=true or GetRestWebServiceValue=false response. This call is being done at the thisworkbook level using a public sub workbook_open() macro. I can step through the macro and see all works as it should, but the true or false is not getting passed to the standard module Public GetRestWebServiceValue As String.

As soon as the workbook_open() hits a exit sub or end sub the value is lost.

Any suggestions would be greatly appreciated.

Public Sub Workbook_Open()

Dim url As String
User = Environ("username")
url = "http://ws.yahoo.com/ws/secured/authorization/" & User
 
On Error GoTo Err_SuperTrap
    GetRestWebServiceValue = ""
  
    Dim xmlhttp
    Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
    Call xmlhttp.Open("GET", url, False)
    
    Call xmlhttp.send
    
    Dim xmldoc As DOMDocument
    
    Set xmldoc = CreateObject("Microsoft.XMLDOM")
    xmldoc.async = False
    
    xmldoc.LoadXML (xmlhttp.responseText)
        
    GetRestWebServiceValue = xmldoc.Text

MsgBox GetRestWebServiceValue
Exit_SuperTrap:
Exit Sub

Err_SuperTrap:

    If Err.Number <> -2146697211 Then
        MsgBox Err.Description
    End If
    GetRestWebServiceValue = "Intranet Server Down"

End Sub

code in a standard module

Public GetRestWebServiceValue As String
Dim adoCN As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim ReturnField As String
Dim strSQL As String

 

[stapuff]

I am an idiot.


The macro needs to be in a standard module and the call to run it in the workbook open event.

Private Sub Workbook_Open()
Run "security_check"
End Sub

Public Sub security_check() 
Dim url As String
User = Environ("username")
url = "http://ws.yahoo.com/ws/secured/authorization/" & User
 
On Error GoTo Err_SuperTrap
    GetRestWebServiceValue = ""
  
    Dim xmlhttp
    Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
    Call xmlhttp.Open("GET", url, False)
    
    Call xmlhttp.send
    
    Dim xmldoc As DOMDocument
    
    Set xmldoc = CreateObject("Microsoft.XMLDOM")
    xmldoc.async = False
    
    xmldoc.LoadXML (xmlhttp.responseText)
        
    GetRestWebServiceValue = xmldoc.Text

MsgBox GetRestWebServiceValue
Exit_SuperTrap:
Exit Sub

Err_SuperTrap:

    If Err.Number <> -2146697211 Then
        MsgBox Err.Description
    End If
    GetRestWebServiceValue = "Intranet Server Down"
end sub