Visual Basic Help Needed!!!

[MichellePV]

I completed a form for my supervisor using VB via Microsoft Word. It worked beautifully on my end...I could fill it out, save it, etc. I password-protected the document and only allowed people access to fill-in the form, which consists of drop-down menurs and text boxes. However, when another user opens it, Visual Basic opens up and they can't complete the form. WHAT THE HECK AM I DOING WRONG????? Thank you so much for your time.

[AndreRet]

When you say VB (I take it that you are talking about VBA) opens up, is it the macros ? What exactly opens up?

[MichellePV]

Oh, Andre, thank God it's you!!! :) OK, what happens is, the form opens up, the minute they click on the drop down menu it automatically opens up to all my VB coding! I just don't understand! THANK YOU FOR ANY INSIGHT!!!

[AndreRet]

Oh, Andre, thank God it's you!!!

:D. Thanks...

Lets retrace our steps here please.

VBA or VB6?
Paste the code you have under the combo box (drop down box), for that matter all the code in your form.
The form that opens, is it a Word document?

I'll be online for the next 3 hours or so. If you can send the above I can still try and sort your problem today.:)

[MichellePV]

Hi Andre, sorry it took me this long to reply, and sorry for the confusion! It is VB6 - I started it in Word and used VB for the form. This site is blocked from my work computer, so I have to write you from home and am unable to send you the code. Interestingly enough, it opens on some users computers just fine and the form works beautifully. On others, the minute they click on the box it opens up in VB and you can see my coding! Do I need to compile, save it as an xml, do you think? I am thinking some computers at work do not run VB. THANK YOU, and sorry this was sooo long.

[AndreRet]

Hi Michelle,

Sorry but now I'm confused.:-/

If the code opens up on another users pc, it means they have VB6 installed on their system, which is very unlikely, unless it is an IT department that is involved in developing software...:)

Soooo, it seems like you are using VBA which is shipped with MS Office. If you have word installed on your system, you will have VBA automatically installed.

Now we need to find out why it is loading and showing the macros on some desktops and not on others. For that I will unfortunately need the code. Surely you have email? Send me a private message and I will reply with my email address. You can then send me the code and we can solve this!;)

Just go into my profile and select "Send AndreRet a private message" at the bottom right.:)

[MichellePV]

Hi Andre! I am leaving for work now...I will get the code to you somehow...thanks for your patience on this! :D

[AndreRet]

Geez, and I am almost going home soon. Its 14:00 by me.;)

No problem, when you can send it...:)

[MichellePV]

Hi Andre, it's on its way!!!

[AndreRet]

Cool, I'll have a look as soon as time permits. You will still be sleeping, so hopefully by the time you read this, its solved.;)

[AndreRet]

Okie Dokie,

The code window opens because "makros enabled" is not enabled on all users desktops. When the form opens, it is trying to read from your Doc_Open event. If makros id disabled, it can not open the document, hence the code window to enable the makros.

You can do two things here -

1) Enable makros on all users pc's or try the following to open your document -

Private Sub oApp_DocumentOpen(ByVal Doc As Document)
    combo.AddItem "Jan." 
End Sub

2)Do it automatically, which is a bit more advanced, but the code is set at public level, which means that ANY document that is opened by the user will be opened with makros enabled.

If you create a DocumentChange event and store it in an Addin, it will be triggered when any document is created, opened, closed or the focus changes from one document to another. You can take advantage of this to simulate AutoNew, AutoOpen, AutoClose macros (as well as being able to monitor when the focus changes). Unlike the application event DocumentChange, AutoNew, AutoOpen and AutoClose macros only behave globally if stored in Normal.dot.

It will also auto load the document with all the code read perfectly. Have a look at the code below -

In a Module: (Name the module what you like)

Option Explicit

Dim oAppClass As New ThisApplication
Public oldNoOfOpenDocs As Long
Public FirstNewDoc As Boolean

Public Sub AutoExec()
    Set oAppClass.oApp = Word.Application 
    oldNoOfOpenDocs = 0
    FirstNewDoc = True
End Sub

In a Class Module (name this ThisApplication)!!!!!

Option Explicit

Public WithEvents oApp As Word.Application

Private Sub oApp_DocumentChange()

On Error GoTo ExitCode

Dim newNoOfOpenDocs As Long
Dim docAdded As Boolean
Dim docClosed As Boolean

newNoOfOpenDocs = Application.Documents.Count

If newNoOfOpenDocs > oldNoOfOpenDocs Then
    docAdded = True

    If ActiveDocument.Name = "Document1" And FirstNewDoc Then
        FirstNewDoc = True
    Else
        FirstNewDoc = False
    End If
    oldNoOfOpenDocs = oldNoOfOpenDocs + 1

ElseIf oldNoOfOpenDocs > newNoOfOpenDocs Then
    docClosed = True
    FirstNewDoc = False
    oldNoOfOpenDocs = oldNoOfOpenDocs - 1
End If

If docAdded Then
    If Len(ActiveDocument.Path) = 0 Then
    Call PsuedoAutoNew
Else
    Call PsuedoAutoOpen
End If

ElseIf docClosed Then
    Call PsuedoAutoClose

ElseIf FirstNewDoc Then
    If Len(ActiveDocument.Path) = 0 Then
        Call PsuedoAutoNew
    Else
        Call PsuedoAutoOpen
    End If

Else
    Call DocChangedFocus
End If

Exit Sub

ExitCode:

End Sub

Private Sub PsuedoAutoNew() 
     'Your code here 
End Sub

Private Sub PsuedoAutoOpen() 
     combo.AddItem "Jan." 'and so on... 
End Sub

Private Sub PsuedoAutoClose() 
    'Your code here 
End Sub

Private Sub DocChangedFocus() 
    'Your code here 
End Sub

In one important respect, the PsuedoAutoNew and PsuedoAutoOpen macros work better than the Word specific NewDocument and DocumentOpen events.

When you start Word, a new blank document is created; the PsuedoAutoNew macro runs, but neither AutoNew nor the NewDocument events are triggered.

Similarly, if you start Word with an AutoExec macro that opens a document, or that allows the user to open a document, for example:

Public Sub AutoExec()

Dim myFile As String
myFile = Application.RecentFiles(1).Path & "\" _
  & Application.RecentFiles(1).Name
Documents.Open myFile

End Sub

... AutoOpen and the PsuedoAutoOpen macro both fire.

I hope this is helping...;)

[MichellePV]

You should run for president of the United States! You are brilliant!!! Thanks, I am going to try this at work!!!!

[AndreRet]

No chance, I have enough stress as it is, thank you.:D

Let me know how it went. I have sent you a test mail if you want to go that route.:)