Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Alphabetize controls before placing on form
Register FAQ

Reply
 
Thread Tools Display Modes
  #1  
Old 09-23-2019, 08:45 PM
hlock hlock is offline Alphabetize controls before placing on form Windows 7 64bit Alphabetize controls before placing on form Office 2010 64bit
Novice
Alphabetize controls before placing on form 		 
Join Date: Dec 2012
Posts: 6
hlock is on a distinguished road
Default Alphabetize controls before placing on form
I have a form that opens up when the user opens the document. The form lists each contact name and address. The contact information comes from an XML. The code runs through each name, adding the address then placing the controls on the form. I want to alphabetize the names when adding the controls. I've set up a collection for the names and sorted the collection by A-Z. That works. However, I am struggling with figuring out how to compare each node name to the next name in the collection.

[code]
Private Sub UserForm_Initialize()

On Error GoTo Err_UserForm_Initialize



Set objNode = ActiveDocument.CustomXMLParts.SelectByNamespace("http://schemas.rlicorp.net/ContentMa...ocumentElement



If objNode.SelectSingleNode("/ns0:Claim[1]/ns0:Contacts[1]") Is Nothing Then
Exit Sub
End If



Dim ctlTo As Control
Dim ctlCc As Control
Dim ctlName As Control
Dim ctlTempName As Control
Dim ctlAddress1 As Control
Dim ctlAddress2 As Control
Dim ctlCity As Control
Dim ctlState As Control
Dim ctlZIP As Control
Dim ctlRole As Control
Dim cName As Variant
Dim tempname As String
Dim coll As Collection



Set objNode = ActiveDocument.CustomXMLParts.SelectByNamespace("http://schemas.rlicorp.net/ContentMa...ocumentElement



nAddresses = 0



Set coll = nameCollection



For i = 1 To Int(objNode.SelectSingleNode("/ns0:Claim[1]/ns0:Contacts[1]").ChildNodes.Count)



Set objNode1 = objNode.SelectSingleNode("/ns0:Claim[1]/ns0:Contacts[1]").ChildNodes(i)


If (Not (objNode1.SelectSingleNode("ns0:Address1[1]") Is Nothing)) Or (Not (objNode1.SelectSingleNode("ns0:Name[1]") Is Nothing)) Then
nAddresses = nAddresses + 1

For Each cName In nameCollection



If Not (objNode1.SelectSingleNode("ns0:Name[1]") Is Nothing) Then
tempname = objNode1.SelectSingleNode("ns0:Name[1]").Text
End If

If cName <> tempname Then Exit For
Debug.Print cName

Set ctlTo = fraSelectContact.Controls.Add("Forms.Checkbox.1", "chkTo" & nAddresses)
ctlTo.Left = lblTo.Left
ctlTo.Top = lblTo.Top + 20 + (nAddresses - 1) * 30

Set ctlCc = fraSelectContact.Controls.Add("Forms.Checkbox.1", "chkCc" & nAddresses)
ctlCc.Left = lblCc.Left
ctlCc.Top = lblCc.Top + 20 + (nAddresses - 1) * 30



Set ctlName = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtName" & nAddresses)
ctlName.Left = lblName.Left
ctlName.Top = lblName.Top + 20 + (nAddresses - 1) * 30
ctlName.Width = 130
ctlName.Text = cName



Set ctlAddress1 = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtAddress1_" & nAddresses)
ctlAddress1.Left = lblAddress1.Left
ctlAddress1.Top = lblAddress1.Top + 20 + (nAddresses - 1) * 30
ctlAddress1.Width = 170
If Not (objNode1.SelectSingleNode("ns0:Employer[1]") Is Nothing) Then
ctlAddress1.Text = objNode1.SelectSingleNode("ns0:Employer[1]").Text & ";"
End If
If Not (objNode1.SelectSingleNode("ns0:Address1[1]") Is Nothing) Then
ctlAddress1.Text = ctlAddress1.Text & objNode1.SelectSingleNode("ns0:Address1[1]").Text
End If

Set ctlAddress2 = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtAddress2_" & nAddresses)
ctlAddress2.Left = lblAddress2.Left
ctlAddress2.Top = lblAddress2.Top + 20 + (nAddresses - 1) * 30
ctlAddress2.Width = 160
If Not (objNode1.SelectSingleNode("ns0:Address2[1]") Is Nothing) Then
ctlAddress2.Text = objNode1.SelectSingleNode("ns0:Address2[1]").Text
End If



Set ctlCity = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtCity" & nAddresses)
ctlCity.Left = lblCity.Left
ctlCity.Top = lblCity.Top + 20 + (nAddresses - 1) * 30
ctlCity.Width = 60
If Not (objNode1.SelectSingleNode("ns0:City[1]") Is Nothing) Then
ctlCity.Text = objNode1.SelectSingleNode("ns0:City[1]").Text
End If



Set ctlState = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtState" & nAddresses)
ctlState.Left = lblState.Left


ctlState.Top = lblState.Top + 20 + (nAddresses - 1) * 30
ctlState.Width = 30
If Not (objNode1.SelectSingleNode("ns0:State[1]") Is Nothing) Then
ctlState.Text = objNode1.SelectSingleNode("ns0:State[1]").Text
End If



Set ctlZIP = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtZIP" & nAddresses)
ctlZIP.Left = lblZIP.Left
ctlZIP.Top = lblZIP.Top + 20 + (nAddresses - 1) * 30
ctlZIP.Width = 50
Not (objNode1.SelectSingleNode("ns0:Zip[1]") Is Nothing) Then
ctlZIP.Text = objNode1.SelectSingleNode("ns0:Zip[1]").Text
End If

Next cName
End If
Next i



Exit_UserForm_Initialize:
Exit Sub



Err_UserForm_Initialize:
MsgBox Err.Number & " _ " & Err.Description & vbCrLf
Resume Exit_UserForm_Initialize



End Sub


Function nameCollection() As Collection



Dim coll As Collection



Set coll = New Collection



Set objNode = ActiveDocument.CustomXMLParts.SelectByNamespace("http://schemas.rlicorp.net/ContentMa...ocumentElement



For i = 1 To Int(objNode.SelectSingleNode("/ns0:Claim[1]/ns0:Contacts[1]").ChildNodes.Count)
Set objNode1 = objNode.SelectSingleNode("/ns0:Claim[1]/ns0:Contacts[1]").ChildNodes(i)
If Not (objNode1.SelectSingleNode("ns0:Name[1]") Is Nothing) Then
coll.Add Item:=objNode1.SelectSingleNode("ns0:Name[1]").Text
End If
Next I


Quicksort coll, 1, coll.Count


Set nameCollection = coll

End Function



Sub QuickSort(coll As Collection, first As Long, last As Long)

Dim vCentreVal As Variant, vTemp As Variant

Dim lTempLow As Long
Dim lTempHi As Long
lTempLow = first
lTempHi = last

vCentreVal = coll((first + last) \ 2)
Do While lTempLow <= lTempHi

Do While coll(lTempLow) < vCentreVal And lTempLow < last
lTempLow = lTempLow + 1
Loop

Do While vCentreVal < coll(lTempHi) And lTempHi > first
lTempHi = lTempHi - 1
Loop

If lTempLow <= lTempHi Then

vTemp = coll(lTempLow)

coll.Add coll(lTempHi), After:=lTempLow
coll.Remove lTempLow

coll.Add vTemp, Before:=lTempHi
coll.Remove lTempHi + 1

lTempLow = lTempLow + 1
lTempHi = lTempHi - 1

End If

Loop

If first < lTempHi Then QuickSort coll, first, lTempHi
If lTempLow < last Then QuickSort coll, lTempLow, last

End Sub
[\code]

I just don't seem to have everything in the correct order. When I run the code, I only get one row, the first name, and that is it.


I would appreciate any help. If I haven't been clear, please let me know.
Last edited by hlock; 09-24-2019 at 02:03 PM.
Reply With Quote
  #2  
Old 09-24-2019, 11:14 AM
gmaxey gmaxey is offline Alphabetize controls before placing on form Windows 10 Alphabetize controls before placing on form Office 2016
Expert
  
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,429
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default
well without your document and customXMLpart is is going to be hard to decipher that spaghetti mess you posted. Put your code in code tags.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 09-24-2019, 03:28 PM
hlock hlock is offline Alphabetize controls before placing on form Windows 7 64bit Alphabetize controls before placing on form Office 2010 64bit
Novice
Alphabetize controls before placing on form 		 
Join Date: Dec 2012
Posts: 6
hlock is on a distinguished road
Default
Well, I tried to edit the post and it disappeared! Let's try this again. I want to alphabetize the userform controls as they are placed on the form. I've got the collection alphabetized, however, I am struggling on the order of the code. The way that it is right now, only the first row is added.

This is how it should look:
Capture.PNG

Code:
Private Sub UserForm_Initialize()

On Error GoTo Err_UserForm_Initialize

Set objNode = ActiveDocument.CustomXMLParts.SelectByNamespace("http://schemas.rlicorp.net/ContentManagement.Claims").Item(1).DocumentElement

If objNode.SelectSingleNode("/ns0:Claim[1]/ns0:Contacts[1]") Is Nothing Then
    Exit Sub
End If

Dim ctlTo As Control
Dim ctlCc As Control
Dim ctlName As Control
Dim ctlTempName As Control
Dim ctlAddress1 As Control
Dim ctlAddress2 As Control
Dim ctlCity As Control
Dim ctlState As Control
Dim ctlZIP As Control
Dim ctlRole As Control
Dim cName As Variant
Dim tempname As String
Dim coll As Collection
Dim i As Long
Dim nAddresses As Integer

Set objNode = ActiveDocument.CustomXMLParts.SelectByNamespace("http://schemas.rlicorp.net/ContentManagement.Claims").Item(1).DocumentElement

nAddresses = 0

Set coll = nameCollection

For i = 1 To Int(objNode.SelectSingleNode("/ns0:Claim[1]/ns0:Contacts[1]").ChildNodes.Count)
    Set objNode1 = objNode.SelectSingleNode("/ns0:Claim[1]/ns0:Contacts[1]").ChildNodes(i)

    If (Not (objNode1.SelectSingleNode("ns0:Address1[1]") Is Nothing)) Or _
    (Not (objNode1.SelectSingleNode("ns0:Name[1]") Is Nothing)) Then
        nAddresses = nAddresses + 1

        For Each cName In nameCollection

            If Not (objNode1.SelectSingleNode("ns0:Name[1]") Is Nothing) Then
                tempname = objNode1.SelectSingleNode("ns0:Name[1]").Text
            End If

            If cName <> tempname Then Exit For
            Debug.Print cName

            Set ctlTo = fraSelectContact.Controls.Add("Forms.Checkbox.1", "chkTo" & nAddresses)
            ctlTo.Left = lblTo.Left
            ctlTo.Top = lblTo.Top + 20 + (nAddresses - 1) * 30

            Set ctlCc = fraSelectContact.Controls.Add("Forms.Checkbox.1", "chkCc" & nAddresses)
            ctlCc.Left = lblCc.Left
            ctlCc.Top = lblCc.Top + 20 + (nAddresses - 1) * 30

            Set ctlName = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtName" & nAddresses)
            ctlName.Left = lblName.Left
            ctlName.Top = lblName.Top + 20 + (nAddresses - 1) * 30
            ctlName.Width = 130
            ctlName.Text = cName

            Set ctlAddress1 = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtAddress1_" & nAddresses)
            ctlAddress1.Left = lblAddress1.Left
            ctlAddress1.Top = lblAddress1.Top + 20 + (nAddresses - 1) * 30
            ctlAddress1.Width = 170
            If Not (objNode1.SelectSingleNode("ns0:Employer[1]") Is Nothing) Then
                ctlAddress1.Text = objNode1.SelectSingleNode("ns0:Employer[1]").Text & ";"
            End If
            If Not (objNode1.SelectSingleNode("ns0:Address1[1]") Is Nothing) Then
                ctlAddress1.Text = ctlAddress1.Text & objNode1.SelectSingleNode("ns0:Address1[1]").Text
            End If

            Set ctlAddress2 = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtAddress2_" & nAddresses)
            ctlAddress2.Left = lblAddress2.Left
            ctlAddress2.Top = lblAddress2.Top + 20 + (nAddresses - 1) * 30
            ctlAddress2.Width = 160
            If Not (objNode1.SelectSingleNode("ns0:Address2[1]") Is Nothing) Then
                ctlAddress2.Text = objNode1.SelectSingleNode("ns0:Address2[1]").Text
            End If

            Set ctlCity = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtCity" & nAddresses)
            ctlCity.Left = lblCity.Left
            ctlCity.Top = lblCity.Top + 20 + (nAddresses - 1) * 30
            ctlCity.Width = 60
            If Not (objNode1.SelectSingleNode("ns0:City[1]") Is Nothing) Then
                ctlCity.Text = objNode1.SelectSingleNode("ns0:City[1]").Text
            End If

            Set ctlState = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtState" & nAddresses)
            ctlState.Left = lblState.Left
            ctlState.Top = lblState.Top + 20 + (nAddresses - 1) * 30
            ctlState.Width = 30
            If Not (objNode1.SelectSingleNode("ns0:State[1]") Is Nothing) Then
                ctlState.Text = objNode1.SelectSingleNode("ns0:State[1]").Text
            End If

            Set ctlZIP = fraSelectContact.Controls.Add("Forms.TextBox.1", "txtZIP" & nAddresses)
            ctlZIP.Left = lblZIP.Left
            ctlZIP.Top = lblZIP.Top + 20 + (nAddresses - 1) * 30
            ctlZIP.Width = 50
            If Not (objNode1.SelectSingleNode("ns0:Zip[1]") Is Nothing) Then
                ctlZIP.Text = objNode1.SelectSingleNode("ns0:Zip[1]").Text
            End If

        Next cName
    End If
Next i

Exit_UserForm_Initialize:
Exit Sub

Err_UserForm_Initialize:
MsgBox Err.Number & " _ " & Err.Description & vbCrLf & "Contact claimcenterhelp@rlicorp.com"
Resume Exit_UserForm_Initialize

End Sub

unction nameCollection() As Collection

Dim coll As Collection
Set coll = New Collection

Set objNode = ActiveDocument.CustomXMLParts.SelectByNamespace("http://schemas.rlicorp.net/ContentManagement.Claims").Item(1).DocumentElement

For i = 1 To Int(objNode.SelectSingleNode("/ns0:Claim[1]/ns0:Contacts[1]").ChildNodes.Count)
    Set objNode1 = objNode.SelectSingleNode("/ns0:Claim[1]/ns0:Contacts[1]").ChildNodes(i)

    If Not (objNode1.SelectSingleNode("ns0:Name[1]") Is Nothing) Then
        coll.Add Item:=objNode1.SelectSingleNode("ns0:Name[1]").Text
    End If

Next i

QuickSort coll, 1, coll.Count

Set nameCollection = coll

End Function
Sub QuickSort(coll As Collection, first As Long, last As Long)

Dim vCentreVal As Variant, vTemp As Variant
Dim lTempLow As Long
Dim lTempHi As Long

lTempLow = first
lTempHi = last

vCentreVal = coll((first + last) \ 2)

Do While lTempLow <= lTempHi

Do While coll(lTempLow) < vCentreVal And lTempLow < last
    lTempLow = lTempLow + 1
Loop

Do While vCentreVal < coll(lTempHi) And lTempHi > first
    lTempHi = lTempHi - 1
Loop

If lTempLow <= lTempHi Then
    vTemp = coll(lTempLow)

    coll.Add coll(lTempHi), After:=lTempLow
    coll.Remove lTempLow

    coll.Add vTemp, Before:=lTempHi
    coll.Remove lTempHi + 1

    lTempLow = lTempLow + 1
    lTempHi = lTempHi - 1

End If

Loop

If first < lTempHi Then QuickSort coll, first, lTempHi
If lTempLow < last Then QuickSort coll, lTempLow, last

End Sub
I don't get any errors when I run this, but this is what the form ends up looking liked:
AfterCode.jpg

Here is a shortened version of the XML:
Code:
<?xml version="1.0" encoding="utf-8" ?> 
- <Claim xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ID>00989876</ID> 
  <Description>General Liability</Description> 
  <ContractID>A78656789</ContractID> 
+ <Contract>
- <Contacts>
- <ClaimContact>
  <Name>John Doe</Name> 
  <Address1>PO Box 712</Address1> 
  <City>Jacksonville</City> 
  <State>FL</State> 
  <Zip>98631</Zip> 
  </ClaimContact>
- <ClaimContact>
  <Name>Alton Carpeting</Name> 
  <Address1>4567 Watchworks Ave</Address1> 
 <City>Detroit</City> 
  <State>MI</State> 
  <Zip>98631</Zip> 
  </ClaimContact>
+ <ClaimContact>
+ <ClaimContact>
  </Contacts>
  <ClaimUser /> 
  </Claim>
Thank you in advance for any suggestions.
Reply With Quote
  #4  
Old 09-26-2019, 08:27 AM
hlock hlock is offline Alphabetize controls before placing on form Windows 7 64bit Alphabetize controls before placing on form Office 2010 64bit
Novice
Alphabetize controls before placing on form 		 
Join Date: Dec 2012
Posts: 6
hlock is on a distinguished road
Default
Finally figured it out. Just had to put the statements in the correct order.
Reply With Quote
Reply

Tags
userform popup input, word vba, xml in ms word

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
Content Controls: Whole field selecting upon clicking vs only placing cursor inside the field nicoledey Word 4 06-16-2018 12:37 AM
Form with content controls - expands but at the bottom of the form louiseword Word 3 05-27-2016 12:47 AM
Alphabetize controls before placing on form I'm having trouble creating a Merge using label form and placing a shape in the text blockie Mail Merge 8 11-13-2013 11:28 PM
Adding controls to a form. supercrewed Outlook 0 02-14-2013 02:12 PM
Content Controls Form Programming beachdog Word VBA 4 09-20-2011 10:26 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:14 AM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft