UNCO-OPERATIVE USERFORM

[Greg]

Hi,

I have a Userform into which I have added some code to make it expand when a command button is depressed.

However, I when I go into the project to add listboxes and so on, the expanded part of the form is not visible. Yet, if I exit and return to the document and press "expand" the additional canvas is added at the bottom of the form.

Having seen how this should work I know that my form is faulty but I can't see what's missing.

I hope you can help. My code is shown below.

Regards,

Greg.

Option Explicit
Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
Me.Controls(ctl.Name).Text = ""
ElseIf TypeOf ctl Is CheckBox Then
Me.Controls(ctl.Name).Value = False
ElseIf TypeOf ctl Is ComboBox Then
Me.Controls(ctl.Name).ListIndex = 0
End If
Next
End Sub
Private Sub ClickToFinish_Click()
Dim oControl As Control
Dim strBM_Name As String
Dim strBM_Text As String
For Each oControl In frmDataInput.Controls
If Left(oControl.Name, 3) = "txt" Then
strBM_Name = Right(oControl.Name, Len(oControl.Name) - 3)
strBM_Text = frmDataInput.Controls(oControl.Name).Text
Call FillABookmark(strBM_Name, strBM_Text)
End If
Next
ActiveDocument.Fields.Update
Unload Me
End Sub
Sub CheckIt()
ActiveDocument.AttachedTemplate.AutoTextEntries _
("Checked Box").Insert Where:=Selection.Range
End Sub
Sub UncheckIt()
ActiveDocument.AttachedTemplate.AutoTextEntries _
("Unchecked Box").Insert Where:=Selection.Range
End Sub
Option Explicit
Private Sub UserForm_Initialize()
Dim oBM As Word.Bookmarks
Set oBM = ActiveDocument.Bookmarks
If oBM("Applicant").Range.Text <> "" Then
Me.txtApplicant.Text = oBM("Applicant").Range.Text
End If
If oBM("ApplicantAddress").Range.Text <> "" Then
Me.txtApplicantAddress.Text = oBM("ApplicantAddress").Range.Text
End If
If oBM("Respondent").Range.Text <> "" Then
Me.txtRespondent.Text = oBM("Respondent").Range.Text
End If
If oBM("RespondentAddress").Range.Text <> "" Then
Me.txtRespondentAddress.Text = oBM("RespondentAddress").Range.Text
End If
If oBM("CaseNumber").Range.Text <> "" Then
Me.txtCaseNumber.Text = oBM("CaseNumber").Range.Text
End If
If oBM("Amount").Range.Text <> "" Then
Me.txtAmount.Text = oBM("Amount").Range.Text
End If
Set oBM = Nothing

End Sub
Sub AutoOpen()
' Set macro button response to one click rather than double-click
' Macro written by Charles Kenyon based on help from Bill Coan on newsgroup
'
Options.ButtonFieldClicks = 1
'
End Sub
Sub AutoNew()
AutoOpen ' so new document responds the same as opened document
End Sub
Sub CopyCheckBoxMacro()
' Macro written by Charles Kenyon
' 21 December 2001 modified 11 January 2002
'
' Declare variables
Dim sCheck As String
Dim bCheck As Boolean
Dim sChecka As String
Dim sCheckb As String
'
' Get name and value of formfield which was just used and
' set variable for dependent formfield name
sCheck = Selection.FormFields.Item(1).Name
sChecka = sCheck & "a"
sCheckb = sCheck & "b"
bCheck = ActiveDocument.FormFields(sCheck).CheckBox.Value
'
' Unprotect document
ActiveDocument.Unprotect
'
' Set value for dependent checkboxes and reprotect document
ActiveDocument.FormFields(sChecka).CheckBox.Value = bCheck
ActiveDocument.FormFields(sCheckb).CheckBox.Value = bCheck
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End Sub
Option Explicit
Private Sub CommandButton2_Click()
With CommandButton2
If .Caption = "Expand" Then
Do While Me.Height < 250
Me.Height = Me.Height + 0.1
DoEvents
Loop
.Caption = "Contract"
Else
Do While Me.Height > 170
Me.Height = Me.Height - 0.1
DoEvents
Loop
.Caption = "Expand"
End If
End With
End Sub
Private Sub UserForm1_Initialize()
With Me
.Width = 200
.Height = 170
End With
With CommandButton1
.Top = 100
.Left = 100
.Width = 75
.Height = 25
.Caption = "Expand"
End With
End Sub

[lucas]

Hi Greg,
It would be much easier if you posted the workbook instead of everyone having to reproduce your workbook and form. Sanitize it of personal info and use the manage attachements button below where you post your reply.

[lucas]

Are you saying you can't see all of the form in the VB Editor? Just click on the form and select the little square at the bottom center and drag the form down.....

[fumei]

And please, when posting here, use the VBA tags for code. Thanks.

This is simple though.

The VBE will show the userform size as it is currently defined. Yes, you can resize it and see the expanded part. However, unless you explicitly size it on _Initialize, then when you Show it, it will be whatever is the size currently showing in the VBE.

Solution? Explicitly size by code. Define, explicitly, the starting sizes in Userform_Initialize. Then when you use the very nicely named procedure....

Sub CommandButton2_Click()

do your sizing...again explicitly.

I have mentioned this before, but it is a VERY good habit to get into. Do everything, name everything explicitly.

CommandButton2_Click() would be much better as cmdResize_Click().

Just to review....

In the VBE, make the userform the size you want it to initially be. Write down the Height and Width.

Expand the userform to whatever it takes to see the entire thing.

In Userform_Initialize (and you should of course have the userform explicitly named as well...) make the userform the smaller size by explicitly declaring the size to be the size you want - using the number for Height and Width you wrote down.

VBA does exactly what you tell it do (uh...most of the time...), so tell it what to do.

[fumei]

It is not the userform that being uncoperative...it is doing exactly what you tell it do.

[Greg]

The criticism is justified and I will have a go at remedying the problem. In the meantime my document is attached just in case the problem is different than anticipated.

Thanks,

Greg.

[lucas]

I copied this from your userform1 initialize statement which wasn't being used since there is no userform1:

With Me
.Width = 200
.Height = 170
End With

I changed the 200 to a 400 and this is what the actual initialize statement looks like now:

Private Sub UserForm_Initialize()
Dim oBM As Word.Bookmarks
With Me
.Width = 400
.Height = 170
End With
Set oBM = ActiveDocument.Bookmarks
If oBM("Applicant").Range.Text <> "" Then
Me.txtApplicant.Text = oBM("Applicant").Range.Text
End If
If oBM("ApplicantAddress").Range.Text <> "" Then
Me.txtApplicantAddress.Text = oBM("ApplicantAddress").Range.Text
End If
If oBM("Respondent").Range.Text <> "" Then
Me.txtRespondent.Text = oBM("Respondent").Range.Text
End If
If oBM("RespondentAddress").Range.Text <> "" Then
Me.txtRespondentAddress.Text = oBM("RespondentAddress").Range.Text
End If
If oBM("CaseNumber").Range.Text <> "" Then
Me.txtCaseNumber.Text = oBM("CaseNumber").Range.Text
End If
If oBM("Amount").Range.Text <> "" Then
Me.txtAmount.Text = oBM("Amount").Range.Text
End If
Set oBM = Nothing

End Sub


I also pulled your form down so you can see the expanded part in the vbe...hope this helps. see attached. If this doesn't help then I don't understand the problem.

[Greg]

Thanks Lucas and fumei. Your help is greatly appreciated.

Greg.

[fumei]

And sorry, I was not really intending to be critical. I was trying to emphasize that (usually) things in VBA will do exactly what you tell them to do. It is a matter of thinking it through. If there is a problem, the first thing to actually THINK about is...what I am really telling the code to do?

[Greg]

No need to be sorry fumei. You are right to encourage people to solve their problems.

[Greg]

Hi again fumei,

There is one more thing that has me stumped. After altering this userform for another purpose I could not get the macro to run. I got a meesage "Run-time error '5941'.

I have visted the Microsoft resource site but still can't pin this down. I have obviously done something during the conversion process but can't see what.

However, the debugger (the bugger?) directs me to the following code:

[Sub DemoForm()
frmDataInput.Show
End Sub]

Where the words "frmData.show" are highlighted in yellow.

Are you able to point me in the right direction on this one?

greg.

[fumei]

I can hardly say...since I have no idea what "altering" you did, nor what it was like before you altered it.

You are giving zero information. I have no idea what "conversion process" you are talking about, nor what that means.

[Greg]

Yes, I thought as much. What I should have said is that the document and userform in the attachement above worked bvery well.

However, after I copied the userform and module into another document and adapted it for use in that document, the macro would not run. Trying to run the macro produced a dialog box with the message Run-time error '5941'. Then when I tried to debug, the offending code I previously referred to was highlighted.

I have had this happen before and somehow managed to solve the problem but on this occasion I seem unable to.

Anyway, in the expectation that you will want to see the document a zipped copy is attached.

Regards,

Greg.

[lucas]

Hi Greg,
I didn't have the time to go completely through your file but I did find a couple of things...You had "Option Explicit" in multiple locations in the code for the userform..you can only have it once at the top of your code.

The reason the form would not run lies in the initialize statement. I commented out most of it and set the size for your userform but you will have to look closely at your if statements in the initialize statement to see what the problem might be. I will revisit this if I am able. Hopefully you can sort through it but if not post your questions here for more help.

[fumei]

Hi Greg,

Ok, a number of things. Please do not take any of this as criticism...you are doing well with this. Ready?

1. I simply can NOT stress this enough. Please, please, please stop using default names for things - especially Commandbuttons. NAME them. I know it is a bit of a pain, but if you are going to continue doing this stuff, in the long run it will be much better for you.

Commandbutton3_Click says nothing.
cmdClearTextboxes_Click does.

Use the standard prefixes as well.

ClickToFinish_Click is a start, but all controls have a _Click event. Textboxes have _Click events, etc.

cmdFinish_Click identifies the procedure as the Click event of a commandbutton (cmd prefix).

2. BE CONSISTENT! You have procedures where you use oControl to declare a Control object. You have procedures where you use ctl to declare a Control object.

You have procedures that use Me.Controls. You have procedures that use the explicit name of the form (frmDataInput) - frmDataInput.Controls. Technically speaking, in this case, they are indeed equivalent, but it is a good habit to be consistent. I certainly in favor of being explicit, but Me is fine. Just be consistent.

3. You are basing a great deal of logic in matching names of the userform textboxes with the document bookmarks. This is fine, and a handy logic tool. Userform textbox - txtClaimant; bookmark = Claimant. You are using the matching logic by stripping off "txt" from the control name and either putting stuff into the bookmark; or checking existing bookmark values and putting it into the control.

That is, you have logic going both ways...and this is perfectly fine.

BUT...you MUST make sure they DO match. I found a few that did not.

Bookmark - ReminderLetterDate Userform - ReminderDate
Bookmark - DeponentName Userform - Deponent
Bookmark - DepondentOccuptation Userform - Occupation

Again, this is perfectly good logic structure...but you MUST check that they match. It is a pain, but write one side down (doesn't matter which way) - that is, write down all the bookmark names you used. Then go through and make sure the textboxes on the userform match.

4. All that stuff in _Initialize? That: Set oBM = ActiveDocument.Bookmarks
If oBM("Claimant").Range.Text <> "" Then
Me.txtClaimant.Text = oBM("Claimant").Range.Text
End If
If oBM("Defendant").Range.Text <> "" Then
Me.txtDefendant.Text = oBM("Defendant").Range.Text
End If
If oBM("CaseNumber").Range.Text <> "" Then
Me.txtCaseNumber.Text = oBM("CaseNumber").Range.Text
End If
' etc etc etcFirst of all, it is better to move that kind of logic OUT of _Initialize. Make it a separate procedure, and then Call it from _Initialize.

It is easier to debug procedures if they are small, tidy and keep to themselves. It seems the sizing is a little off to me, but I will ignore that for a bit. So, here is _Initialize:Public Sub UserForm_Initialize()
With Me
.Width = 400
.Height = 230
End With
Call MatchCurrentBookmarks
End Sub The initializing event sizes the form, and calls a procedure to check and import existing bookmarks if they are not "". This is what you want to do, right? OK then.

In THAT procedure (MatchCurrentBookmarks), instead of tediously going through the document bookmarks by name: If oBM("Claimant").Range.Text <> "" Then
Me.txtClaimant.Text = oBM("Claimant").Range.Text
End If
' yadda yadda yadda
' etc etc etcuse the same kind of logic - using objects - that you are, in fact, going the other way. Here is MatchCurrentBookmarks:Sub MatchCurrentBookmarks()
Dim oBM As Bookmarks
Dim oCtl As Control
Set oBM = ActiveDocument.Bookmarks
For Each oCtl In Me.Controls
If TypeOf oCtl Is TextBox Then
If oBM(Right(oCtl.Name, Len(oCtl.Name) - 3)). _
Range.Text <> "" Then
Me.Controls(oCtl.Name).Text = _
oBM(Right(oCtl.Name, Len(oCtl.Name) - 3)).Range.Text
End If
End If
Next
End SubIt goes through the controls, if it is textbox it strips off "txt" from the name, uses that in the bookmarks collection to see if the matching bookmarks is "" (or not), and if it is not "", takes the bookmark text and dumps it into the userform textbox.

It...Match(es)CurrentBookmark....

5. Always be careful when copying userforms (or anything else for that matter) from one file. You can do it of course, but you always make sure things are correct. Like the duplicate Option Explicits that Steve mentioned.

6. If you doing dynamic resizing of userforms, unless everyone who is using the file has the same screen resolution, you may want to do some checking with various resolutions to make sure the resized userform fits nicely. There is nothing wrong here, except for me, when you Expand - oh, right...when you Commandbutton2....the userform bottom is not visible.

I changed the _Initialize sizing to be a wee bit smaller. The tops of the not visible textboxes were in fact visible. I made the Height 220, instead of 230.

Anyway, I got your form working fine. You can still fire with the Data Form item on the top toolbar.

[mdmackillop]

A wee bit code to help adjust form size to suit screen size. (Johnske KB item)

Option Explicit
Private ScrWidth&, ScrHeight&
Private Declare Function GetSystemMetrics32 Lib "User32" _
Alias "GetSystemMetrics" (ByVal nIndex&) As Long

Private Sub UserForm_Initialize()
FormResize
End Sub
Sub FormResize()
Dim Wd As Long, Ht As Long
Wd = GetSystemMetrics32(0) * 0.3
Ht = GetSystemMetrics32(1) * 0.3
'Reverse/Adjust these parameters as required
If Wd > 400 Then Wd = 400
If Ht > 230 Then Ht = 230
Me.Width = Wd
Me.Height = Ht
End Sub

[fumei]

Thanks Malcolm.

[Greg]

Well,

With the help of you all I have got this thing working pretty well. However, I do have a problem with the text in the textboxes in the expanded part of the userform.

If I type text into the userform and press ok, then bring up the userform again (e.g. to make changes), the text already entered into those textboxes is distorted and virtually unreadable.

I find this to be very odd because the text typed into the textboxes of the main part of the userform remains perfect.

Do you know how to solve this issue?

Greg.

[lucas]

Hi Greg,
You'll have to post it as we could work for weeks trying to reproduce the problem......