Re: text box info depending on what is selected in a dropdown by Jules305
Jules305
Mon Sep 10 03:43:23 CDT 2007
On 8 Sep, 02:01, "Doug Robbins - Word MVP" <d...@REMOVECAPSmvps.org>
wrote:
> This is not exactly what you want, but should show you how to go about it.
> The same process can be used for loading data into a combobox or a listbox.
> In your case, you are probably going to have the names in column 1 and the
> phone number in column 2, so you would be setting the .BoundColumn property
> to 2 to get the data out of the second column of the combobox.
>
> This routine loads a listbox with client details stored in a table in a
> separate
> document (which makes it easy to maintain with additions, deletions etc.),
> that document being saved as Clients.Doc for the following code.
>
> On the UserForm, have a list box (ListBox1) and a Command Button
> (CommandButton1) and use the following code in the UserForm_Initialize() and
> the CommandButton1_Click() routines
>
> Private Sub UserForm_Initialize()
> Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
> m As Long, n As Long
> ' Modify the path in the following line so that it matches where you
> saved Clients.doc
> Application.ScreenUpdating = False
> ' Open the file containing the client details
> Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
> ' Get the number or clients = number of rows in the table of client
> details less one
> i = sourcedoc.Tables(1).Rows.Count - 1
> ' Get the number of columns in the table of client details
> j = sourcedoc.Tables(1).Columns.Count
> ' Set the number of columns in the Listbox to match
> ' the number of columns in the table of client details
> ListBox1.ColumnCount = j
> ' Define an array to be loaded with the client data
> Dim MyArray() As Variant
> 'Load client data into MyArray
> ReDim MyArray(i, j)
> For n = 0 To j - 1
> For m = 0 To i - 1
> Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
> myitem.End = myitem.End - 1
> MyArray(m, n) = myitem.Text
> Next m
> Next n
> ' Load data into ListBox1
> ListBox1.List() = MyArray
> ' Close the file containing the client details
> sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
> End Sub
>
> Private Sub CommandButton1_Click()
> Dim i As Integer, Addressee As String
> Addressee = ""
> For i = 1 To ListBox1.ColumnCount
> ListBox1.BoundColumn = i
> Addressee = Addressee & ListBox1.Value & vbCr
> Next i
> ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
> UserForm2.Hide
> End Sub
>
> The Initialize statement will populate the listbox with the data from the
> table and then when a client is selected in from the list and the command
> button is clicked, the information for that client will be inserted into a
> bookmark in the document. You may want to vary the manner in which it is
> inserted to suit your exact requirements, but hopefully this will get you
> started.
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP
>
> "Jules305" <j_m_t_...@yahoo.co.uk> wrote in message
>
> news:1189086256.573907.205990@g4g2000hsf.goo,glegroups.com...
>
>
>
> > Hi there, this is my first post.
>
> > I have to change my company's letterhead. At the moment it is on a
> > basic word template so the secretary just has to type her boss' name
> > next to the "please ask for" but my boss wants to change it so that
> > each manager's direct dial number is shown underneath their particular
> > name, so i've started a userform but am at a loss for what to do
> > now!!
>
> > I was wondering if i could create a dropdown or combo box that shows
> > each manager and then depending on whoever gets selected, their
> > individual direct dial will show up in a text box? I have had a scout
> > round the groups but to be honest, i am really not sure how to go
> > about this.
>
> > Sorry if this has already been asked - as i said, i have searched the
> > groups but cant really see anything.
>
> > Any help would be most appreciated- Hide quoted text -
>
> - Show quoted text -
Hi its me again - i've just tried out the code and i cant actually get
the user form to show itself, i added in this code:-
Private Sub Document_New()
Dim oForm As UserForm1
Set oForm = New UserForm1
oForm.Show
before the code you kindly gave me but still nothing comes up when i
base the letter on the template i have created. Help!