Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Populating drop down lists (Word XP)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Populating drop down lists (Word XP)

    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

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Re: Populating drop down lists (Word XP)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Populating drop down lists (Word XP)

    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

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Re: Populating drop down lists (Word XP)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Populating drop down lists (Word XP)

    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
    Attached Images Attached Images
    • File Type: gif x.gif (14.2 KB, 0 views)

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Re: Populating drop down lists (Word XP)

    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.

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Re: Populating drop down lists (Word XP)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating drop down lists (Word XP)

    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

  10. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Re: Populating drop down lists (Word XP)

    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?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating drop down lists (Word XP)

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

  12. #12
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Re: Populating drop down lists (Word XP)

    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.
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating drop down lists (Word XP)

    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.

  14. #14
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Re: Populating drop down lists (Word XP)

    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!
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating drop down lists (Word XP)

    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 <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    See the attached version.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •