Userform - Duplicating text at bookmarks

[tallen]

Thanks to all of the advice I have received here, I have nearly completed my userform, however, I have put some message boxes in to advise that a field has not been populated - it allows the field to be populated before moving on. When I look at my document it has inserted the text two or three times, depending on how many times I have not populated either the text boxes or combo boxes ("so I could check that the error messages were working"). Is there a way that I can force to only insert text at the bookmarks once it gets to 'Unloadme'?? Thanks, code below

VBA
Private Sub CommandButton1_Click()

If ValidateTextInput(Me) = True Then
With ActiveDocument

.Bookmarks("FirstName").Range.Text = Me.TextBox6.Text
.Bookmarks("Surname").Range.Text = Me.TextBox7.Text
.Bookmarks("ContactTel").Range.Text = Me.TextBox8.Text
.Bookmarks("RequestedBy").Range.Text = Me.TextBox3.Text
.Bookmarks("Department").Range.Text = Me.TextBox4.Text
.Bookmarks("RequestTel").Range.Text = Me.TextBox5.Text

End With


Selection.GoTo What:=wdGoToBookmark, Name:="EPrescribing"

ActiveDocument.Bookmarks("EPrescribing").Range.Text = ComboBox1.Value

Selection.GoTo What:=wdGoToBookmark, Name:="ProgressNoteType"

If ComboBox2.Text = "Select an Item" Then
MsgBox "Nothing was selected from the Progress Note Type dropdown box!"
Exit Sub
Else

ActiveDocument.Bookmarks("ProgressNoteType").Range.Text = ComboBox2.Value

End If

Selection.GoTo What:=wdGoToBookmark, Name:="HomePage"

If ComboBox4.Text = "Select an Item" Then
MsgBox "Nothing was selected from the Home Page dropdown box!"
Exit Sub
Else

ActiveDocument.Bookmarks("HomePage").Range.Text = ComboBox4.Value

End If


Selection.GoTo What:=wdGoToBookmark, Name:="ValidateOwn"

If CheckBox3.Value = True Then
Selection.TypeText Text:="Yes"
Else
Selection.TypeText Text:=""
End If


Selection.GoTo What:=wdGoToBookmark, Name:="ValidateOther"
If CheckBox6.Value = True Then
Selection.TypeText Text:="Yes"
Else
Selection.TypeText Text:=""
End If

Selection.GoTo What:=wdGoToBookmark, Name:="AdministerMedication"

If CheckBox8.Value = True Then
Selection.TypeText Text:="Yes"
Else
Selection.TypeText Text:=""
End If

Unload Me
'load first, then show ----------------
Load UserForm6
UserForm6.Show

End If



End Sub
Function ValidateTextInput(ByRef oFrm As UserForm) As Boolean
ValidateTextInput = False
Dim oCtr As Control
For Each oCtr In oFrm.Controls
If TypeName(oCtr) = "TextBox" Then
If oFrm.Controls(oCtr.Name).Text = "" Then
MsgBox "One or more text fields were left blank, please complete."
oCtr.SetFocus
Exit Function
End If
End If
Next
ValidateTextInput = True


End Function

Private Sub CommandButton2_Click()
End
End Sub
Private Sub UserForm_Initialize()
With ComboBox1

.AddItem "Read Only"
.AddItem "No Access"
.AddItem "Administrator"

End With

With ComboBox2

.AddItem "None"
.AddItem "Administrative Worker"
.AddItem "Medical"
.AddItem "Non-Clinical"


End With


With ComboBox4

.AddItem "None"
.AddItem "Case Record"
.AddItem " Diary"
.AddItem "Shared services"


End With

End Sub

[gmaxey]

Again without access to your form it would take a bit of doing to fully test.

You basically need to do all of your validation upfront in the command button click event. You also don't need to be jumping around with the selection to each bookmark. Experiment with this a bit:

Private Sub CommandButton1_Click()
If Not ValidateTextInput(Me) Then Exit Sub
If Not ValidateComboBoxInput(Me) Then Exit Sub
'Text fields
WriteDataToBookmark "FirstName", Me.TextBox6.Text
WriteDataToBookmark "Surname", Me.TextBox6.Text
'.... so one
'Comboboxes
WriteDataToBookmark "EPrescribing", ComboBox1.Value
WriteDataToBookmark "ProgressNoteType", ComboBox2.Value
'Checkboxes
If CheckBox3.Value = True Then
WriteDataToBookmrk "ValidateOwn", "Yes"
Else
WriteDataToBookmrk "ValidateOwn", ""
End If
'... so on
Unload Me
UserForm6.Show
End Sub

Sub WriteDataToBookmark(ByRef pName As String, pText As String)
Dim oRng As Word.Range
Set oRng = ActiveDocument.Bookmarks(pName).Range
oRng.Rext = pText
ActiveDocument.Bookmarks.Add pName, oRng
End Sub

Function ValidateTextInput(ByRef oFrm As UserForm) As Boolean
Dim oCtr As Control
ValidateTextInput = False
For Each oCtr In oFrm.Controls
If TypeName(oCtr) = "TextBox" Then
If oFrm.Controls(oCtr.Name).Text = "" Then
MsgBox "One or more text fields were left blank, please complete."
oCtr.SetFocus
Exit Function
End If
End If
Next
ValidateTextInput = True
End Function

Function ValidateComboBoxInput(ByRef oFrm As UserForm) As Boolean
Dim oCtr As Control
ValidateComboBoxInput = False
For Each oCtr In oFrm.Controls
If TypeName(oCtr) = "ComboBox" Then
If oFrm.Controls(oCtr.Name).Value = "Select an Item" Then
MsgBox "One or more combbbox fields were left blank, please make a selection."
oCtr.SetFocus
Exit Function
End If
End If
Next
ValidateComboBoxInput = True
End Function

[fumei]

"Is there a way that I can force to only insert text at the bookmarks once it gets to 'Unloadme'??"

Nope. Once it is unloaded it does not, CAN not, doing anything. Unload means "I am finished."

That being said, put the instructions to put the text at the bookmarks (note again that the text deletes the bookmark) AFTER you have validated.

"When I look at my document it has inserted the text two or three times,"

Yes of course it does.

Sub CommandButton

' put the text in bookmarks
.Bookmarks("FirstName").Range.Text = Me.TextBox6.Text
.Bookmarks("Surname").Range.Text = Me.TextBox7.Text
.Bookmarks("ContactTel").Range.Text = Me.TextBox8.Text
.Bookmarks("RequestedBy").Range.Text = Me.TextBox3.Text
.Bookmarks("Department").Range.Text = Me.TextBox4.Text
.Bookmarks("RequestTel").Range.Text = Me.TextBox5.Text

' NOW you test something
If ComboBox2.Text = "Select an Item" Then
MsgBox "Nothing was selected from the Progress Note Type dropdown box!"
Exit Sub
Else

' ...other stuff
Sooooo, if ComboBox2.Text = "Select an Item" you give the messagebox and Exit the sub.

They fix things....and...click the commandbutton again...AND...yup

' put the text in bookmarks
.Bookmarks("FirstName").Range.Text = Me.TextBox6.Text
.Bookmarks("Surname").Range.Text = Me.TextBox7.Text

those instructions are executed again.

Do ALL of your validation logic before you actually do any insertion of text.

BTW: please use the VBA code tags when posting code.

[fumei]

Darn that Greg. He seems to get in seconds before me.

[gmaxey]

Yes he waits until just a second or two before you post to jump in. It is must more annoying that way ;-)

[gmaxey]

Tallen,

I took some time to illustrate how I would probably do something like this. As I have mentioned in other threads here, I often take a proactive step to make sure data fields are filled out. That is, I disable the command button until all the required parameters are met.

The following code shows how I would disable the command button with the initialize event and only enable it after all data fields are completed:

Private Sub UserForm_Initialize()
Me.CommandButton1.Enabled = False
With Me
With .ListBox1
.AddItem "Item 1"
.AddItem "Item 2"
End With
With .ListBox2
.AddItem "Item 1"
.AddItem "Item 2"
End With
With .ComboBox1
.AddItem "Item 1"
.AddItem "Item 2"
End With
With .ComboBox2
.AddItem "Item 1"
.AddItem "Item 2"
End With
End With
End Sub
Private Sub ComboBox1_Change()
ChkValidInputs Me
End Sub
Private Sub ComboBox2_Change()
ChkValidInputs Me
End Sub
Private Sub ListBox1_Change()
ChkValidInputs Me
End Sub
Private Sub ListBox2_Change()
ChkValidInputs Me
End Sub
Private Sub TextBox1_Change()
ChkValidInputs Me
End Sub
Private Sub TextBox2_Change()
ChkValidInputs Me
End Sub
Private Sub CommandButton1_Click()
WriteDataToBookmark "TextBox1", Me.TextBox1.Text
WriteDataToBookmark "TextBox2", Me.TextBox2.Text
WriteDataToBookmark "ListBox1", Me.ListBox1.Text
WriteDataToBookmark "ListBox2", Me.ListBox2.Text
WriteDataToBookmark "ComboBox1", Me.ComboBox1.Text
WriteDataToBookmark "ComboBox2", Me.ComboBox2.Text
End Sub
Sub ChkValidInputs(ByRef oFrm As UserForm)
Dim oCtr As Control
Me.CommandButton1.Enabled = False
For Each oCtr In oFrm.Controls
Select Case TypeName(oCtr)
Case "TextBox"
If oFrm.Controls(oCtr.Name).Text = "" Then Exit Sub
Case "ListBox"
If oFrm.Controls(oCtr.Name).ListIndex = -1 Then Exit Sub
Case "ComboBox"
If oFrm.Controls(oCtr.Name).Value = "" Then Exit Sub
End Select
Next oCtr
Me.CommandButton1.Enabled = True
End Sub
Sub WriteDataToBookmark(ByRef pName As String, pText As String)
Dim oRng As Word.Range
Set oRng = ActiveDocument.Bookmarks(pName).Range
oRng.Text = pText
ActiveDocument.Bookmarks.Add pName, oRng
End Sub


The demo consists of two TextBoxes, two ListBoxes, and tow ComboBoxes. The bookmark names in the documetn correspond to the Userform control names.

Good luck.

[fumei]

Well doesn't that look all shipshape. Bravo sailor.

tallen, I hope you caught Greg's "The bookmark names in the documetn [sic] correspond to the Userform control names."


(Sorry Greg, I could not resist...my apologies for the [sic])

That means the bookmark names AND the userform control names are identical.

.Bookmarks("FirstName").Range.Text = Me.TextBox6.Text

would be actioned as:

WriteDataToBookmark "TextBox6", Me.TextBox6.Text


Greg and I are going to have depart agreement here. I think this has extraordinary amount of processing. For example, if you type in "blah" into Textbox2, then:

Private Sub TextBox2_Change()
ChkValidInputs Me
End Sub
pushes execution to ChkValidInputs. Fair enough...but ChkValidInputs tests ALL controls. Soooooo.

type "b", ChkValidInputs is executed
TextBox1 is tested (even though Textbox2 is the control in question)
TextBox2 is tested
ListBox1 is tested (even though Textbox2 is the control in question)
ListBox2 is tested (even though Textbox2 is the control in question)
ComboBox1 is tested (even though Textbox2 is the control in question)
ComboBox2 is tested (even though Textbox2 is the control in question)

type "l", ChkValidInputs is executed
TextBox1 is tested (even though Textbox2 is the control in question)
TextBox2 is tested
ListBox1 is tested (even though Textbox2 is the control in question)
ListBox2 is tested (even though Textbox2 is the control in question)
ComboBox1 is tested (even though Textbox2 is the control in question)
ComboBox2 is tested (even though Textbox2 is the control in question)

type "a", ChkValidInputs is executed
TextBox1 is tested (even though Textbox2 is the control in question)
TextBox2 is tested
ListBox1 is tested (even though Textbox2 is the control in question)
ListBox2 is tested (even though Textbox2 is the control in question)
ComboBox1 is tested (even though Textbox2 is the control in question)
ComboBox2 is tested (even though Textbox2 is the control in question)

type "h", ChkValidInputs is executed
TextBox1 is tested (even though Textbox2 is the control in question)
TextBox2 is tested
ListBox1 is tested (even though Textbox2 is the control in question)
ListBox2 is tested (even though Textbox2 is the control in question)
ComboBox1 is tested (even though Textbox2 is the control in question)
ComboBox2 is tested (even though Textbox2 is the control in question)

a total of 24 instructions executed, of which only 4 are strictly relevant.

Further, say TextBox1 has a valid input - it is NOT "" - its execution of ChkValidInputs makes Me.CommandButton1.Enabled = True .

In other words, if TextBox1 passes with <> "", the commandbutton is enabled, and all other testing is meaningless. All other tests of the controls will make NO difference. The commandbutton is already enabled.

[fumei]

Perhaps the more important issue for you is there is NO testing of any real value of any textbox1.

"b" passes the test, and in terms of valdation IS valid. Therefore "b" would be accepted.

The listboxes and comboboxes are different as what you are testing is that it is NOT "Select an item" (ComboBox), or nothing selected (ListBox). From a logic point of view ANY value (other than "Select an item" or nothing) is valid. And that quite possibly is fine.

But once you type anything - a single character - into the textbox, it passes the testing.

Which begs the question: once a single character is entered into a textbox...why bother testing each character typed in?

"b"

and

"Harry Belefonte"

are both valid.

[fumei]

Are we having fun yet?

As you have discovered, validation/error-trapping is (IMO) THE hardest thing of all. In terms of complexity and difficulty it beats any object instructional coding hands down.

In my Word VBA course I spend two full days on this subject.

[gmaxey]

Gerry,

Good comments all. With a small userform limited to a few controls I don't feel the extra processing is too high a price to pay for complete control of the form. However, considering your comments I think the "Exit" event could serve the purpose just as well and cut down on some of the processing:

Option Explicit
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.TextBox1.Text = "" Then Cancel = True
ChkValidInputs Me, Me.TextBox1
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.TextBox2.Text = "" Then Cancel = True
ChkValidInputs Me, Me.TextBox2
End Sub
Private Sub ListBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ListBox1.ListIndex = -1 Then Cancel = True
ChkValidInputs Me, Me.ListBox1
End Sub
Private Sub ListBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ListBox2.ListIndex = -1 Then Cancel = True
ChkValidInputs Me, Me.ListBox2
End Sub
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ComboBox1.Value = "" Then Cancel = True
ChkValidInputs Me, Me.ComboBox1
End Sub
Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ComboBox2.Value = "" Then Cancel = True
ChkValidInputs Me, Me.ComboBox2
End Sub
Private Sub UserForm_Initialize()
Me.CommandButton1.Enabled = False
With Me
With .ListBox1
.AddItem "Item 1"
.AddItem "Item 2"
End With
With .ListBox2
.AddItem "Item 1"
.AddItem "Item 2"
End With
With .ComboBox1
.AddItem "Item 1"
.AddItem "Item 2"
End With
With .ComboBox2
.AddItem "Item 1"
.AddItem "Item 2"
End With
End With
End Sub
Private Sub CommandButton1_Click()
WriteDataToBookmark "TextBox1", Me.TextBox1.Text
WriteDataToBookmark "TextBox2", Me.TextBox2.Text
WriteDataToBookmark "ListBox1", Me.ListBox1.Text
WriteDataToBookmark "ListBox2", Me.ListBox2.Text
WriteDataToBookmark "ComboBox1", Me.ComboBox1.Text
WriteDataToBookmark "ComboBox2", Me.ComboBox2.Text
End Sub
Sub ChkValidInputs(ByRef oFrm As UserForm, oThisCtr As Control)
Dim oCtr As Control
Me.CommandButton1.Enabled = False
Select Case TypeName(oThisCtr)
Case "TextBox"
If oThisCtr.Text = "" Then Exit Sub
Case "ListBox"
If oThisCtr.ListIndex = -1 Then Exit Sub
Case "ComboBox"
If oThisCtr.Value = "" Then Exit Sub
End Select
For Each oCtr In oFrm.Controls
Select Case TypeName(oCtr)
Case "TextBox"
If oFrm.Controls(oCtr.Name).Text = "" Then Exit Sub
Case "ListBox"
If oFrm.Controls(oCtr.Name).ListIndex = -1 Then Exit Sub
Case "ComboBox"
If oFrm.Controls(oCtr.Name).Value = "" Then Exit Sub
End Select
Next oCtr
Me.CommandButton1.Enabled = True
End Sub
Sub WriteDataToBookmark(ByRef pName As String, pText As String)
Dim oRng As Word.Range
Set oRng = ActiveDocument.Bookmarks(pName).Range
oRng.Text = pText
ActiveDocument.Bookmarks.Add pName, oRng
End Sub


Using the Exit event we can also take advanage of "Cancel" and not let the user out of the control until an entry is made.

I certainly agree that there isn't much point in testing for "" in a Textbox when "H" or "Ha" or "Har" or "Harr" ... will satisfy the logic. A valid test might be for a phone number, or SSN or birthday where you could text IsDate, IsNumeric, string lenght, format, etc.

[fumei]

I agree. I was mostly jiving ya. I seriously doubt most systems would feel any strain whatsoever. It is, shall we say, the principle of it. I try to minimize any extraneous instructions.

Your use of Exit is much better.

[tallen]

Greg and Gerry

I thank you so much for the amount of time and effort you put into this site and it has helped me so much. Since I last checked in I have been working hard on the info that you have given me and now have one more question. I have revamped my code but when I run the form I get the following error......I did not want to ask you again so have spent some time working on it, but needless to say, here I am again!! The error message is Runtime error 5941, requested member of collection does not exist; ( strange thing is that the example that was added here works fine, I just cannot see where I have gone wrong so before I spend hours re-writing it all here it is!!)

Sub WriteDataToBookmark(ByRef pName As String, pText As String)
Dim oRng As Word.Range

This part is highlighted """" Set oRng = ActiveDocument.Bookmarks(pName).Range """


oRng.Text = pText
ActiveDocument.Bookmarks.Add pName, oRng
End Sub

I attach my document and would be grateful if you could highlight where I have gone wrong. Many thanks.. tallen

[gmaxey]

Your bookmark name in the document "RequestTel" did not match the bookmark named in the code "RequestedTel"

You don't need to goto anything (e.g., Checkboxes):

Private Sub CommandButton1_Click()
If Not ValidateTextInput(Me) Then Exit Sub
If Not ValidateComboBoxInput(Me) Then Exit Sub
WriteDataToBookmark "FirstName", Me.TextBox3.Text
WriteDataToBookmark "Surname", Me.TextBox4.Text
WriteDataToBookmark "RequestedBy", Me.TextBox6.Text
WriteDataToBookmark "Department", Me.TextBox7.Text
WriteDataToBookmark "RequestTel", Me.TextBox8.Text 'The bookmark name is "RequestTel" not "RequestedTel"
'Comboboxes
WriteDataToBookmark "EPrescribing", ComboBox1.Value
WriteDataToBookmark "ProgressNoteType", ComboBox2.Value
WriteDataToBookmark "HomePage", ComboBox4.Value
'Checkboxes
WriteDataToBookmark "ValidateOwn", "Yes"
WriteDataToBookmark "ValidateOther", "Yes"
WriteDataToBookmark "AdministerMedication", "Yes"
Unload Me
End Sub

[gmaxey]

Oops! You do need to check for the checkbox condition:

If .Checbox1 (or whatever).Value = True Then
WriteDataToBookmark "ValidationOwn, "Yes"
End If
and so on.

[tallen]

Thanks, I cannot believe I missed that!!!

I have changed the checkbox vb but I have now lost the prompt for when a person does not select the combo box (it just puts in the text "select an item") and I still get the error re pname??

Attach again for you. With thanks

[gmaxey]

You still hav a bookmark name mispelled in the code. It is ValidateOwn not ValidationOwn

You condition in the Validate ComboBox is "Select an item" not "Select an Item"

Private Sub CommandButton1_Click()
If Not ValidateTextInput(Me) Then Exit Sub
If Not ValidateComboBoxInput(Me) Then Exit Sub
WriteDataToBookmark "FirstName", Me.TextBox3.Text
WriteDataToBookmark "Surname", Me.TextBox4.Text
WriteDataToBookmark "RequestedBy", Me.TextBox6.Text
WriteDataToBookmark "Department", Me.TextBox7.Text
WriteDataToBookmark "RequestTel", Me.TextBox8.Text
'Comboboxes
WriteDataToBookmark "EPrescribing", ComboBox1.Value
WriteDataToBookmark "ProgressNoteType", ComboBox2.Value
WriteDataToBookmark "HomePage", ComboBox4.Value
'Checkboxes
If CheckBox3.Value = True Then WriteDataToBookmark "ValidateOwn", "Yes" 'Not ValidationOwn
If CheckBox6.Value = True Then WriteDataToBookmark "ValidateOther", "Yes"
If CheckBox8.Value = True Then WriteDataToBookmark "AdministerMedication", "Yes"
Unload Me
End Sub
Sub WriteDataToBookmark(ByRef pName As String, pText As String)
Dim oRng As Word.Range
Set oRng = ActiveDocument.Bookmarks(pName).Range
oRng.Text = pText
ActiveDocument.Bookmarks.Add pName, oRng
End Sub
Function ValidateComboBoxInput(ByRef oFrm As UserForm) As Boolean
Dim oCtr As Control
ValidateComboBoxInput = False
For Each oCtr In oFrm.Controls
If TypeName(oCtr) = "ComboBox" Then
If oFrm.Controls(oCtr.Name).Value = "Select an item" Then 'item not Item
MsgBox "One or more combbbox fields were left blank, please make a selection."
oCtr.SetFocus
Exit Function
End If
End If
Next
ValidateComboBoxInput = True
End Function
Function ValidateTextInput(ByRef oFrm As UserForm) As Boolean
Dim oCtr As Control
ValidateTextInput = False
For Each oCtr In oFrm.Controls
If TypeName(oCtr) = "TextBox" Then
If oFrm.Controls(oCtr.Name).Text = "" Then
MsgBox "One or more text fields were left blank, please complete."
oCtr.SetFocus
Exit Function
End If
End If
Next
ValidateTextInput = True
End Function
Private Sub CommandButton2_Click()
End
End Sub
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "Test 1"
.AddItem "Test 2"
.AddItem "Test 3"
End With
With ComboBox2
.AddItem "Test 1"
.AddItem "Test 2"
.AddItem "Test 3"
End With
With ComboBox4
.AddItem "Test 1"
.AddItem "Test 2"
.AddItem "Test 3"
End With
End Sub

[fumei]

You are NOT using Option Explicit!

You deserve all the syntax errors you get. I stronly recommend you use Option Explicit.