Solved: Writing to VBE crashes Excel

[Croeg]

Hello,

With help from this forum, I've created a form that automatically enters a username and password in a secure website to pull data. Passwords need to be updated every 90 days. I created a simple form for the user to update their user name and password which writes back to the VBE. Everything works, but when the program is loaded, Excel crashes. Here's my code below.



Private Declare Function FindWindow Lib "USER32" Alias "FindWindowA" _
(ByVal ClassName As String, ByVal WindowName As String) As Long
Private Declare Function LockWindowUpdate Lib "USER32" _
(ByVal hWndLock As Long) As Long

Sub UpdatePswd()
Application.VBE.MainWindow.Visible = False
Dim VBEHwnd As Long
On Error GoTo ErrH:
Application.VBE.MainWindow.Visible = False
VBEHwnd = FindWindow("wndclass_desked_gsk", _
Application.VBE.MainWindow.Caption)
If VBEHwnd Then
LockWindowUpdate VBEHwnd
End If
' your code to write code
'-----------------------------------
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Rings").CodeModule
With VBCodeMod

.ReplaceLine 22, "Private Const UserName As String = """ & Sheets("Sheet1").Range("I1").Value & """"
.ReplaceLine 23, "Private Const PassWord As String = """ & Sheets("Sheet1").Range("J1").Value & """"
End With
'-----------------------------------
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub



Thanks,

Creog

[Norie]

Why do you need to do this?

Can't you just use the values from the worksheet in the original code?

Why hard code them as constants?

I mean they're not really constant if they change every 90 days.:)

[Croeg]

Hi Norie,

If it were a snake, it would have bit me !!! Here's the code I was working with:


Private Const UserName As String = "Croeg"
Private Const PassWord As String = "11223344"
Private Attempts As Integer

Private Sub WB_DocumentComplete(ByVal pDisp As Object, URL As Variant)
Set Doc = Nothing
If Not wb.document Is Nothing Then
Set Doc = wb.document
Set BodyOb = Doc.Body '-- can also set BODY object if desired
End If
If TypeOf pDisp Is WebBrowser Then
If InStr(pDisp.document.Title, "Global Logon") <> 0 Then
If Attempts < 3 Then
pDisp.document.all("userid").Value = UserName
pDisp.document.all("password").Value = PassWord
pDisp.document.all("btnSubmit").Click
Attempts = Attempts + 1
End If
End If
End If
End Sub



And here's the change I made:



pDisp.document.all("userid").Value = Sheets("Sheet1").Range("I1").Value
pDisp.document.all("password").Value = Sheets("Sheet1").Range("J1").Value




Thanks Norie :thumb