Populating drop down lists (Word XP)

Topic

New Reply

I would be very grateful for some help, as I am not a programmer:

I have a form that comprises a series of text boxes, many of which I would lke to replace with drop-down lists. To test this, I put a combo box on my form, but I got stuck on how to populate it, so I searched the previous postings on the Lounge and came up with the perfect solution, posted by Hans, (who has often helped me in the past), but I clearly don’t understand how to exactly use it.

The solution Hans posted is:

You could store the information in a text file, for example in a .ini file. Word can read .ini files using System.PrivateProfileString.

An ini file consists of one or more sections denoted by [SectionName] and entries of the form EntryName=Value. For example:

[Employees]
Count=3
Item1=John
Item2=Mary
Item3=David

Code to populate a combo box could look like this:

Dim i As Integer
Dim n As Integer
Const strPath = “C:Settings.ini”
n = System.PrivateProfileString(strPath, “Employees”, “Count”)
For i = 1 To n
Me.cboEmployees.AddItem System.PrivateProfileString(strPath, “Employees”, “Item” & i)
Next i

The ini file can be created and edited in any text editor, for example Notepad.

Regards,
Hans

I set up a test ‘settings.ini’ file that contains:

[Size]
Count=2
Item1=10
Item2=12

I then double-clicked on the combo box on my form to display the relevant code and added a modified version of the code written by Hans, as follows:

Private Sub CboxSize_Change()
Dim i As Integer
Dim n As Integer
Const strPath = “C:Settings.ini”
n = System.PrivateProfileString(strPath, “Size”, “Count”)
For i = 1 To n
Me.CBoxSize.AddItem System.PrivateProfileString(strPath, “Size”, “Item” & i)
Next i
End Sub

I must have done something wrong, as the combo box appears empty when I run the AutoNew macro.

Your help would really be appreciated.

Reply | Quote

Replies

You have just put the code in the wrong place.

CboxSize_Change() will run whenever a user changes their selection in the control called CboxSize
You should put your code in the UserForm_Activate or UserForm_Load procedure (where UserForm is the name of the form that the combo control is on).

StuartR

Reply | Quote

Thank you for your prompt response.

I must still be doing something wrong, as it is still not working. This time, my code is at the beginning and it is:

Private Sub UserForm_Load()
Dim i As Integer
Dim n As Integer
Const strPath = “C:Settings.ini”
n = System.PrivateProfileString(strPath, “Size”, “Count”)
For i = 1 To n
Me.CBoxSize.AddItem System.PrivateProfileString(strPath, “Size”, “Item” & i)
Next i
End Sub

(where UserForm is the name of the form).

Your help is appreciated.

Reply | Quote

What does the code to display the userform look like?
Can you set a breakpoint in this code and single step it to see what happens.
My guess is that you are never executing the code for some reason.

StuartR

Reply | Quote

The problem is that I’m not really a programmer, so although I tried to interpret your advice, the code is probably in the wrong place.

The code for displaying the form is as follows:

Sub MAIN()

‘ AutoNew.MAIN Macro

frmUserForm.Show

End Sub

This is the only code in AutoNew.

The combo box code is the first procedure on the form, followed by code such as:

Private Sub cmdCancel_Click()
ActiveDocument.Close
Unload Me
End Sub

I hope this helps.

Reply | Quote

If you double click the form then that should take you into the Userform_Click procedure.
At the top left of the window you will see UserForm, at the top right you will see Click.
Select the dropdown next to Click and select Activate
Put the code in the UserForm_Activate() procedure that this creates.

StuartR

Reply | Quote

Thank you very much for the clear explanation. It makes a bit of a difference if you do it right!!

It (of course) now works.

Reply | Quote

Thanks to your help, I have been able to make some progress, but I have come up against a few more issues I would love some help with.

1. I realised that in one instance (Colours) I have to use a List Box. As the list is fairly long, I need to list the items in 2 columns.

The way I am displaying the List Box is:

n = System.PrivateProfileString(strPath, “Colours”, “Count”)
For i = 1 To n
Me.LBoxColours.AddItem System.PrivateProfileString(strPath, “Colours”, “Item” & i)
Next i

I would be very grateful for the code to insert, to convert the display from a single column into 2 columns.

2. Once I have all the selected items in the List Box ticked, I have no idea what the command is to print just those selected items. Ideally, I would like to print them with a comma and space separating each selection.

3. To print the contents of the selected item in a drop-down Combo Box (say it’s called Size), is it as simple as Selection.TypeText Text:=CBoxSize.Text?

I really appreciate your help.

Reply | Quote

1. When you set the ColumnCount property of a list box to 2, the data will not be displayed in two independent columns. The second column is used to display additional information about the item in the first column. For example, the first column could contain last names, and the second column the corresponding first names. So you can’t split a long list of colors over two columns. the user will just have to scroll down the single column to see all colors.

2. You can use code like this:

Dim i As Integer
Dim strList As String
For i = 0 To Me.LBoxColours.ListCount – 1
If Me.LBoxColours.Selected(i) Then
strList = strList & “, ” & Me.LBoxColours.List(i)
End If
Next i
If Not strList = “” Then
Selection.TypeText Text:=Mid(strList, 3)
End If

3. Simply use

Selection.TypeText Text:=Me.CBoxSize

Reply | Quote

Thank you Hans for your help once again. I am very grateful, as always.

I have implemented your suggestion and those of Stuart and have finished the code. I ran it for the first time and I am starting to get ‘duplicate’ errors I don’t fully understand, but guess that it may be because I am using those statements you suggested such as Dim i as Integer a few times … I have replaced i with j and k so as not to duplicate, which is probably what it is complaining about, but then other errors appear that I am a little lost on and have no idea how to fix.

As I am sure you would spot the mistakes in a minute, is it possible for you to have a quick look at this code? If so, what should I do to make it as easy as possible for you?

Reply | Quote

You could attach the document with the code, or copy the code into a text file (in Notepad) and attach the text file.

Reply | Quote

Thank you in advance Hans. You are wonderful.

By way of brief explanation, the previous occasion you helped me with this a number of years ago (you will recognize your code!), I was dealing with two rows of 4 labels each butted up against the other with no separators and the document was Landscape. This time, I am dealing with 4 rows of 3 labels with two separators on each row (hence I have to move 2 cells horizontally to get to the next label) and it is Portrait.

You will see from the comments I added to yours that there are sections I don’t really understand, so I may not have interpreted your code correctly to apply to these labels.

The code is attached in a text file.

I am very grateful.

Reply | Quote

Bits of code appear to be repeated in your code, but without seeing the document it’s too difficult to try and guess what it does / should do.

Reply | Quote

Thank you for looking at the code Hans.

It might make a lot more sense if I sent you the document plus the .ini file so you could run it and see the errors. I was just trying the easiest way for you, because you are doing me a huge favour (again) and I didn’t want to burden you with the whole thing. It seems though that it is the only way … I am very grateful for your help!

Reply | Quote

I have radically streamlined the code; in the process, I had to remove all code for formatting the text, and I don’t have enough time to add it again, so I’ll leave that for you as an excercise

See the attached version.

Reply | Quote

Hans, you are truly amazing – you really radically streamlined the code.

Leaving me to add the formatting was only fair (you did the hard stuff, I do the cosmetics) and I thought it would take me no time. I started by copying the formatting from the old code, but needless to say this failed miserably. Firstly, everything still prints in CAPITALS (even though I can’t see where this is set, so I can turn it off) and after spending most of the day on it without success, I am timidly and respectfully asking for your help again …

All I am trying to achieve with the formatting is as follows:

1. An 8 point blank line in Times New Roman, not Bold, to start with, so that the first line is not jammed up to the top of the label.
2. “Style No: ” in Times New Roman ,12 point, Bold and the style entered in the same font and size but not bold
3. A 5 point blank line separator in Times New Roman, not Bold
4. “Size: ” in Arial, 10 point, Bold and the size selected in Times New Roman, 12 point
5. A 5 point blank line separator in Times New Roman, not Bold
6. The description in Arial, 12 point, Bold
7. A 5 point blank line separator in Times New Roman, not Bold
8. The Composition selected in Arial, 10 point, not Bold
9. A 5 point blank line separator in Times New Roman, not Bold
10. “Colours: ” in Arial, 8 point, Bold
11. The colours selected in Arial, 8 point, not Bold with a comma and space separating each colour
12. A 5 point blank line separator in Times New Roman, not Bold
13. “Size Range: ” in Arial, 10 point, Bold
14. An 8 point blank line separator in Arial, not Bold
15. “Fashion Story: ” in Arial, 10 point, Bold
16. The story selected in Arial, 10 point, not Bold
17. A 6 point blank line separator in Arial, not Bold
18. “Delivery: ” in Arial, 10 point, Bold
19. The delivery selected in Arial, 10 point, not Bold
20. A 6 point blank line separator in Arial, not Bold
21. “PRICE: ” in Times New Roman, 12 point, Bold
22. The prices entered in Arial, 10 point, Bold

For reasons I am unable to understand, the code I used for this (text file attached) does not work.

Your assistance would be much appreciated.

Reply | Quote

I’ll look into it, but it’ll have to be later today (which means it might be tomorrow for you).

Reply | Quote

I’m very grateful that you are looking at it Hans. I will wait patiently.

Reply | Quote

Try the attached version. You’ll notice that the code doesn’t try to extend selections. It just sets the formatting as it goes.

Reply | Quote

Hans you are a GENIUS!

Your code works like a charm … you have saved my sanity!! A mere thank you in this case seems not enough!!! I will have to come up with something else ….

Reply | Quote

I tried running your code to see what it does, and it came very close to doing what you described. the only edits I had to make were to replace all your references to controls with a text string so I could see what would be output.

The only places where the output document was not as you describe were

• You said that you want one blank line (of varying sizes) but you consistently insert two blank lines. You can fix this by looking for everywhere that you have two consecutive lines that say Selection.TypeParagraph and deleting one of them
• You say that the the list of colors should not be bold, but you have not changed the bold setting after Selection.TypeText Text:="Colours: " and before Selection.TypeText Text:=strList
• You say that the prices should be in Arial 10 point bold, but you don’t change the font size after entering “Prices:” and before you add the prices.
  [/list]As a matter of style, it would be better to replace each Selection.Font.Bold = wdToggle with either Selection.Font.Bold = True or Selection.Font.Bold = False

  StuartR

Reply | Quote

Thank you Stuart. Your suggestions make a lot of sense and produce cleaner and easier to follow code. As I’m not a programmer, my clumsy coding comes from recording macros and copying the code it generates. I have now removed the double paragraphs and changed the bold selection to True and False, adding one for Colours, as you suggested, but it still doesn’t work.

Reply | Quote