Add In help needed

[tuktuk]

hey there,

i have an add in that logs me into a essbase database which rquired a log in. occasionally, my password changes so my goal is to be prompted to input the new password, if needed, when you select the add in from the add in list box.

so in my .xla (view in Visual Basic) under the ThisWorkBook for the file here is my code:

Private Sub Workbook_AddinInstall()

Dim strMsg, Style, Response
Dim strPSWD As String

strMsg = "Have you changed your passord?"
Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
Response = MsgBox(strMsg, Style)

If Response = vbYes Then
    'add code for a prompt box
    strPSWD = InputBox("Please type in your essbase password.")
    Call AddMenus

Else
Call AddMenus

End If
End Sub

Here is the sub for my Add Menus (which also resides in ThisWorkbook of the file:

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

    '(1)Delete any existing one.We must use On Error Resume next _
        in case it does not exist.
    On Error Resume Next
    application.CommandBars("Worksheet Menu Bar").Controls("&Essbase Auto Log In").Delete

    '(2)Set a CommandBar variable to Worksheet menu bar
    Set cbMainMenuBar = application.CommandBars("Worksheet Menu Bar")
    
    '(3)Return the Index number of the Help menu. We can then use _
        this to place a custom menu before.
    iHelpMenu = cbMainMenuBar.Controls("Help").Index

    '(4)Add a Control to the "Worksheet Menu Bar" before Help
    'Set a CommandBarControl variable to it
    Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)

    '(5)Give the control a caption
    cbcCutomMenu.Caption = "&Essbase Auto Log In"

    '(6)Working with our new Control, add a sub control and _
        give it a Caption and tell it which macro to run (OnAction).
            With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "Log in to RTX"
                .OnAction = "AutoLogInEssbaseRTX"
            End With
On Error GoTo 0

End Sub

from my understanding for the addin to work the .OnAction = "AutoLogInEssbaseRTX" calls the Sub in the Modules section of the save file.

Here is the code for the "AutoLogInEssbaseRTX"

Sub AutoLogInEssbaseRTX()
'
' AutoLogInEssbase Macro
' Macro recorded 9/1/2010 by tgannon
    

    a = "LOGIN"
    
    b = "PASSWORD"

    'this is for testing the input box string
    'b = strPSWD

    Dim Sh As Worksheet
    Dim strSheetName As String
    application.ScreenUpdating = False
    strSheetName = ActiveSheet.Name
    'disconnect from any actice connection
    X = EssVDisconnect(strSheetName)
    X = EssVConnect(strSheetName, a, b, "rtx.wuintranet.net", "RTX", "RevTrx")
    application.ScreenUpdating = True
    MsgBox ("You are now logged into RTX on sheet: " & strSheetName & ".")

End Sub

is there a way to string the Input Value from ThisWorkbook to the Module of the same file?

Thanks
TukTuk

 

[Andrew Poulsom]

You could declare strPSWD as Public in a General module (before any procedures) and remove any other declarations.

Or yo could save the password in a Name in the Add-In and assign what the name RefersTo (excluding the = sign) to your b variable.

 

[tuktuk]

hmmm i get the concept but not sure how to write that. i leaning to the second. actually i'm leaning toward which ever may be easier...hahaha.

what does "save the password in a Name mean?

tuk

 

[Andrew Poulsom]

It means Insert|Name|Define. In VBA:

Private Sub Test()
    Dim strPSWD As String
    strPSWD = InputBox("Please type in your essbase password.")
    ThisWorkbook.Names.Add "PSWD", strPSWD
    MsgBox Replace(Mid(ThisWorkbook.Names("PSWD").RefersTo, 2), """", "")
End Sub

The MsgBox uses the Mid function to remove the = sign and the Replace function to remove the double quotes.

 

[tuktuk]

bomb....we got it. you da man

i truely appreciate your help.

T