Solved: Word Userform, processing time slow ...

[Kim75]

Hello everybody !

I have an userform word (2002), the code is a little bit slow,
can someone help me to improve a little the processing time ?

Code of userform :
Option Explicit
Private Sub UserForm_Initialize()
' Code chargement UserForm : remplissage des listes déroulantes
With Me.ComboBox1
'with 50 items
End With
With Me.ComboBox2
'with 50 items
End With
With Me.ComboBox3
'with 50 items
End With
With Me.ComboBox4
'with 50 items
End With
With Me.ComboBox5
'with 50 items
End With
With Me.ComboBox6
'with 50 items
End With
End Sub

Private Sub Ok_Click()
' Les données de la UserForm :
Dim A As String ' NomDemandeur
Dim B As String ' Telephone
Dim C As String ' IntituleReunion
Dim D As String ' DateReunion
Dim E As String ' DebutReunion
Dim F As String ' FinReunion
Dim G As String ' NomAnimateur
Dim H As String ' NombreParticipants
'Dim I As String ' TypeSalle
Dim J As String ' Site1
Dim K As String ' Site2
Dim L As String ' Site3
Dim M As String ' Site4
Dim N As String ' Site5
Dim O As String ' Site6
'Dim P As String ' Equipement
'Dim Q As String ' Dejeuner
Dim R As String ' SectionAnalytique
Dim S As String ' PersonneContact
Dim T As String ' Commentaire
' 1 - Récupérer les données
A = Me.TextBox1.Text
B = Me.TextBox2.Text
C = Me.TextBox3.Text
D = Me.TextBox4.Text
E = Me.TextBox5.Text
F = Me.TextBox6.Text
G = Me.TextBox7.Text
H = Me.TextBox8.Text
J = Me.ComboBox1.Value
K = Me.ComboBox2.Value
L = Me.ComboBox3.Value
M = Me.ComboBox4.Value
N = Me.ComboBox5.Value
O = Me.ComboBox6.Value
R = Me.TextBox9.Text
S = Me.TextBox10.Text
T = Me.TextBox11.Text
' 3 - Placer les données dans le document
RemplirSignet "NomDemandeur", A
RemplirSignet "Telephone", B
RemplirSignet "IntituleReunion", C
RemplirSignet "DateReunion", D
RemplirSignet "DebutReunion", E
RemplirSignet "FinReunion", F
RemplirSignet "NomAnimateur", G
RemplirSignet "NombreParticipants", H
RemplirSignet "Site1", J
RemplirSignet "Site2", K
RemplirSignet "Site3", L
RemplirSignet "Site4", M
RemplirSignet "Site5", N
RemplirSignet "Site6", O
RemplirSignet "SectionAnalytique", R
RemplirSignet "PersonneContact", S
RemplirSignet "Commentaire", T
With ActiveDocument
If Me.CheckBox1.Value = True Then
ActiveDocument.FormFields("CacReunSimple").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacReunSimple").CheckBox.Value = False
End If
If Me.CheckBox2.Value = True Then
ActiveDocument.FormFields("CacVisio").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacVisio").CheckBox.Value = False
End If
If Me.CheckBox3.Value = True Then
ActiveDocument.FormFields("CacPieuvreAudio").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacPieuvreAudio").CheckBox.Value = False
End If
If Me.CheckBox4.Value = True Then
ActiveDocument.FormFields("CacVideoProject").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacVideoProject").CheckBox.Value = False
End If
If Me.CheckBox5.Value = True Then
ActiveDocument.FormFields("CacDejOui").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacDejOui").CheckBox.Value = False
End If
If Me.CheckBox6.Value = True Then
ActiveDocument.FormFields("CacDejNon").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacDejNon").CheckBox.Value = False
End If
End With
ActiveDocument.Fields.Update
' 4 - Fermer la Userform
Me.Hide
End Sub
Code of module :
Public Sub RemplirSignet(S As String, T As String)
' Remplit le signet S avec le texte T sans détruire S
On Error GoTo rien
Dim Place As Long
ActiveDocument.Unprotect Password:="toto"
Place = ActiveDocument.Bookmarks(S).Range.Start
ActiveDocument.Bookmarks(S).Range.Text = T
ActiveDocument.Bookmarks.Add Name:=S, Range:=ActiveDocument.Range(Place, Place + Len(T))
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
rien:
End Sub
vbaexpress.com don't authorize to post the link of the file online, because the post count must be 5 or greater, my post count is 1 momentarily.

Thanks, Kim.

[fumei]

No, not as it stands. You are doing a LOT of loading with:
With Me.ComboBox1
'with 50 items
End With
that is fair amount of upfront loading. How, exactly, are you loading these items? A previously built array? From a data table? A hard coded array in the Initialize vent?

Th eonly other thing I can suggest is regarding your use of your Sub RemplirSignet. You make 17 separate Calls to it. I would take out any instruction that is repeatative, such as the Unprotect.

Unprotect the document THEN
RemplirSignet
RemplirSignet
RemplirSignet
etc.

BTW: why are you Hiding the userform?

[Tinbendr]

I can only offer small improvements.

As Gerry asked, "How are you loading the items?"
This example loads them one at a time.


With Me
With .ComboBox1
.AddItem "CBox 1 Item 1"
End With

With ComboBox2
.AddItem "CBox 2 Item 1"
End With

'....rest of code

Why Dim A-Z, then assign variable to item?. Just pass the item value directly.


RemplirSignet "NomDemandeur", TextBox1
‘…
RemplirSignet "Site1", ComboBox1

Also in the Sub, use With ActiveDocument.


Public Sub RemplirSignet(S As String, T As String)
' Remplit le signet S avec le texte T sans détruire S
On Error Goto rien
Dim Place As Long
With Activedocument
Place = .Bookmarks(S).Range.Start
.Bookmarks(S).Range.Text = T
.Bookmarks.Add Name:=S, Range:=.Range(Place, Place + Len(T))
End With
rien:
End Sub

I would use this instead.

Sub RemplirSignet(BookmarkToUpdate As String, TextToUse As String)
Dim BMRange As Range
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange.Text = TextToUse
ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Sub

In this section, you setup With, but you don’t use it.

With ActiveDocument
If Me.CheckBox1.Value = True Then
ActiveDocument.FormFields("CacReunSimple").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacReunSimple").CheckBox.Value = False
End If

Do this instead.

With ActiveDocument
If Me.CheckBox1.Value = True Then
.FormFields("CacReunSimple").CheckBox.Value = True
Else
.FormFields("CacReunSimple").CheckBox.Value = False
End If

[fumei]

Totally agree with NOT declaring varaibles and setting them re: the controls. If you used:
RemplirSignet "NomDemandeur", TextBox1


Then you would NOT have to use any of:
Dim A As String ' NomDemandeur
Dim B As String ' Telephone
Dim C As String ' IntituleReunion
Dim D As String ' DateReunion
Dim E As String ' DebutReunion
Dim F As String ' FinReunion
Dim G As String ' NomAnimateur
Dim H As String ' NombreParticipants
'Dim I As String ' TypeSalle
Dim J As String ' Site1
Dim K As String ' Site2
Dim L As String ' Site3
Dim M As String ' Site4
Dim N As String ' Site5
Dim O As String ' Site6
'Dim P As String ' Equipement
'Dim Q As String ' Dejeuner
Dim R As String ' SectionAnalytique
Dim S As String ' PersonneContact
Dim T As String ' Commentaire
' 1 - Récupérer les données
A = Me.TextBox1.Text
B = Me.TextBox2.Text
C = Me.TextBox3.Text
D = Me.TextBox4.Text
E = Me.TextBox5.Text
F = Me.TextBox6.Text
G = Me.TextBox7.Text
H = Me.TextBox8.Text
J = Me.ComboBox1.Value
K = Me.ComboBox2.Value
L = Me.ComboBox3.Value
M = Me.ComboBox4.Value
N = Me.ComboBox5.Value
O = Me.ComboBox6.Value
R = Me.TextBox9.Text
S = Me.TextBox10.Text
T = Me.TextBox11.Text

Not having the overhead of declaring (and thus assigning memory address to them) for all those variables, and then also having to set values to those memory address. yup, that could improve things a little. probably not all that much, but it is definitely a cleaner way of coding. Generally speaking, only use variables you actually need as variables. In this case,
Dim A As String ' NomDemandeur
A = Me.TextBox1.Text
NONE of those variables are actually needed. As Tinbendr points out:
RemplirSignet "NomDemandeur", TextBox1.Text
works fine (better actually). Although I would encourage you to be explicit - use TextBox1.Text. Technically, TextBox1 will work, as well as TextBox1.Value...but only because they internally default to .Text. It is a good habit to be explicit. If you mean .Text, then use .Text.

[Kim75]

good evening fumei, Tinbedr, the forum,

thank you very much for your responses, I go try of abortd understand (technically:)) your remarks,

meanwhile, I join the file so that you have an idea of the slowness of the macro enregistrer_click,

thank you thank you dear sirs :beerchug:

kim

[Kim75]

No, not as it stands. You are doing a LOT of loading with:
With Me.ComboBox1
'with 50 items
End With
that is fair amount of upfront loading. How, exactly, are you loading these items? A previously built array? From a data table? A hard coded array in the Initialize vent?

Th eonly other thing I can suggest is regarding your use of your Sub RemplirSignet. You make 17 separate Calls to it. I would take out any instruction that is repeatative, such as the Unprotect.

Unprotect the document THEN
RemplirSignet
RemplirSignet
RemplirSignet
etc.

BTW: why are you Hiding the userform?

hi Gerry,

I don’t hide the userform, I just let to the user the choice to use it or not, In fact, the meeting can take place in video conference between 6 various places at the same time, and there is 50 different places in all,

Kim

[fumei]

Ummmmmm....YIKES!!

1. You are using a WebBrowser control.

2. You are streaming text across the WebBrowser control

Do you not think this is going to take some load up time? It was not too bad for me, but I have a fairly fast machine.

I would definitely load your controls via some sort of an array.

I have to say, I see this as excessive eye-candy. Yes, it is "cool", and yes it "can be done", but functionally...the price you are going to pay for this kind of cute is precisely what you ARE paying. It can be slow. Functionally, from a useability perspective I see no need for such prettiness. In my opinion it adds nothing to the useability...but yes...it does look "cool".

Myself, I would never do something like this. But then I like things to work cleanly, work efficiently, with minimal "stuff".

[fumei]

"I don’t hide the userform, "

Au contraire. You do. You have TWO instances of:
Me.Hide


....and ZERO instances of:
Unload Me
You never properly unload the userform, which is not good practice.

[fumei]

I have to say though, in your favour, it is a rather slick use of a WebBrowser control. I am taking it apart, as I think I can learn something.

So...........

1. load your controls with their .AddItems via an array, not one at a time. EACH .AddItem requires a parsing of a separate instruction.

2. actually, there is no real #2. Doing #1 may speed things up a bit. Using Tinbendr's suggestions will speed things up - maybe - a little as well, but bottom line is that you have a LOT of things happening and loading, and you are using a control that takes more resources than a normal userform.

[fumei]

But get rid of all those variables and setting their values. You do not need to do this.

[fumei]

Please explain this:


In fact, the meeting can take place in video conference between 6 various places at the same time, and there is 50 different places in all,

Meeting? Video conference? What has this to do with ONE userform in a Word document?

[Kim75]

hi Gerry,

thanks, I am going to think about all your remarks, it is true that I am not obliged by excess in form’s looking,

otherwise, I do not believe that the webbrowser increased the slowness, because the form was already slow before placing it,

kim,

[Paul_Hossler]

Some minor suggestions

1. Instead of Me.TextBox1.Text it'd be easier to follow if you renamed the controls to something meaningful, Me.NomDemandeur.Text


That way you wouldn't need all those DIM-ed vaialbles, and the code would be more self-documenting

2. The redundant With ActiveDocument and overly-compliacted If/Then


With ActiveDocument
If Me.CheckBox1.Value = True Then
ActiveDocument.FormFields("CacReunSimple").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacReunSimple").CheckBox.Value = False
End If
If Me.CheckBox2.Value = True Then
ActiveDocument.FormFields("CacVisio").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacVisio").CheckBox.Value = False
End If
If Me.CheckBox3.Value = True Then
ActiveDocument.FormFields("CacPieuvreAudio").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacPieuvreAudio").CheckBox.Value = False
End If
If Me.CheckBox4.Value = True Then
ActiveDocument.FormFields("CacVideoProject").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacVideoProject").CheckBox.Value = False
End If
If Me.CheckBox5.Value = True Then
ActiveDocument.FormFields("CacDejOui").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacDejOui").CheckBox.Value = False
End If
If Me.CheckBox6.Value = True Then
ActiveDocument.FormFields("CacDejNon").CheckBox.Value = True
Else
ActiveDocument.FormFields("CacDejNon").CheckBox.Value = False
End If
End With


can be simplified


With ActiveDocument
.FormFields("CacReunSimple").CheckBox.Value = Me.CheckBox1.Value
.FormFields("CacVisio").CheckBox.Value = Me.CheckBox2.Value
.FormFields("CacPieuvreAudio").CheckBox.Value = Me.CheckBox3.Value
.FormFields("CacVideoProject").CheckBox.Value = Me.CheckBox4.Value
.FormFields("CacDejOui").CheckBox.Value = Me.CheckBox5.Value
.FormFields("CacDejNon").CheckBox.Value = Me.CheckBox6.Value
End With


Paul

[fumei]

Paul, nice catch on the matching of values. Yes, this is decidely an improvement.

However, as it is an action after the userform loads, it does not affect the OP main issue.

Kim, try removing all those variables and setting of their values. It may help, and it will definitely not make it worse.

[Paul_Hossler]

I didn't do all the typing, but if all 6 ComboBoxes are the same, you could build one array and put it into all six, like Gerry said in #9.

You'll have to try it and see


Private Sub UserForm_Initialize()

Dim aList(1 To 12) As String
aList(1) = ""
aList(2) = "a"
aList(3) = "b"
aList(4) = "c"
aList(5) = "d"
aList(6) = "e"
aList(7) = "f"
aList(8) = "g"
aList(9) = "h"
aList(10) = "i"
aList(11) = "j"
aList(12) = "k"


Me.ComboBox1.List = aList
Me.ComboBox2.List = aList
Me.ComboBox3.List = aList
Me.ComboBox4.List = aList
Me.ComboBox5.List = aList
Me.ComboBox6.List = aList
' Code WebBrowser
LeTexte = "Attention, veuillez nous prévenir le plus tôt possible d'une éventuelle annulation de reservation !"
LaCouleur = "#000099"
ParametresHtml
' Code chargement UserForm : remplissage des listes déroulantes
End Sub


Paul

[fumei]

Or use Split.
Private Sub UserForm_Initialize()
Dim aList() As String

aList() = Split("a,b,c,d,e, f,g,h,i,j,k", ",")

ComboBox1.List = aList
ComboBox2.List = aList
ComboBox3.List = aList
ComboBox4.List = aList
ComboBox5.List = aList
ComboBox6.List = aList
End Sub
Technically, you do not need the Me for the combobox controls. The code is in UserForm_Initialize, therefore it is in the userform code module, therefore Me is default.

[Kim75]

Hi Gerry, Paul, the forum

following yours corrections, my form is now more clean, I thank you infinitely :jsmile:

as I could not have columnheadings on listbox, I opted for a second listbox where put headers if possible to synchronize horizontal scrollbars,

this document is not modifiable, so having filled the form, we have to register the document somewhere under a new name, I put a macro to remove all userforms and modules, in order that registered demands don't take too much space on hard disk,

the concern is that when we give a new name to the demand, register it somewhere, and want to close the initial document, windows ask us if we want save as the new named demand, and we must naturally tell yes if we want removing macro does its work,

I would like to find a solution to avoid this window (save-as or not) so that the yes is automatic

thanks, Kim

[fumei]

"this document is not modifiable, so having filled the form, we have to register the document somewhere under a new name, I put a macro to remove all userforms and modules, in order that registered demands don't take too much space on hard disk,"

I have NO idea what you are talking about. Registered demands? What do you mean the document is not modifiable? Huh?

[Kim75]

Hi Gerry,

regrettably I am not virtuoso in vba, I am humbly an admirer of this art :thumb , ok here we are :

I call the filing USF, I fill it, I validate, the word document is filled, I click the button "enregister", situated on the document, the dialog box saveas is shown, I enter "xxxxx" as file name and confirme, the file is saved with the name in the chosen directory,

now when I click the cross which closes word application, a small window is shown, it asks « do you want save modifications given to "xxxxx" ?»

if I press "no", only the filling of the form is saved, the userforms and module are not removed from "xxxxx"
if I press "yes", the filling of the form is saved, and the userforms and module are removed from "xxxxx"

so I wanted to make so that this small window of saving confirmation does not appear, and the the "yes" is activated by vba

http://sd-1.archive-host.com/membres/up/779983057/aa.jpg

in ThisDocument I have :
Private Sub Enregistrer_Click()
With Application.Dialogs(wdDialogFileSaveAs)
.Show
End With
Call SupprimeToutesLesMacros
End Sub

Private Sub Document_Close()
ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
ActiveDocument.Saved = True
End Sub
in the Module I have :Sub SupprimeToutesLesMacros()
Dim VbComp
For Each VbComp In ActiveDocument.VBProject.VBComponents
Select Case VbComp.Type
Case 1 To 3
ActiveDocument.VBProject.VBComponents.Remove VbComp
Case Else
With VbComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VbComp
End Subfriendly, Kim