Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / October 2006

Tip: Looking for answers? Try searching our database.

Newbie needs help implementing combo box, please help!

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Kamitsukenu - 20 Oct 2006 15:30 GMT
Hi there,

I’m having a lot of trouble and I don’t really know where to start, but here
it goes.

I’m creating a sales quotation form and at the top, it has a table with a
load of ‘form’ fields and such like.  

The next bit is to be descriptions of a product or service, plus a price.  
However, I want to be able to have a combo box drop down with an option to
put in a choice (plus the knowledge of how to keep adding additional choices
in the future), followed by the option to enter a price in, then perhaps an
option to either add another product./service and price or stop there.  

I would then like to have a second combo box form where I can add terms and
conditions, and in the same way as before, be able to add the conditions
until I am satisfied I have them all (this would be preferably bullet pointed)

I know I’m asking for a lot, but having read some of the topics, I realise
that I haven’t got the vba/macro skills to even begin this, and my only
alternative would be just to manually type then in every time I have a quote.


If you could give me some help, I‘d really appreciate it!

John
Dawn Crosier, MVP - 20 Oct 2006 23:00 GMT
One way is to have the combo box look at a separate Word document which you
could maintain.  When the UserForm Initializes it goes out to your separate
Word document and gets the necessary information.

Here is some information that will help you build a client list type
document - once you see how it works, you can then adapt it to fit your
needs, you will need to modify the code to tell it where you have your
source document containing your information such as c:\Documents and
Settings\John\Desktop\My Clients.doc:

This routine loads a listbox (or a combobox) 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
Suppliers.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

UserForm.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.

To make it easy for you, the code has been written so that it will deal with
any number of clients and any number of details about each client.  It
assumes that the first row of the table containing the client details is a
header row

(My Thanks to Greg Chapman for this example.)
Signature

Dawn Crosier
Microsoft MVP
"Education Lasts a Lifetime"

This message was posted to a newsgroup,  Please post replies and questions
to the group so that others can learn as well.

> Hi there,
>
[quoted text clipped - 27 lines]
>
> John
Kamitsukenu - 23 Oct 2006 08:50 GMT
Thank you for this Dawn, I'll have a look at this when I have a spare moment
or 10!

> One way is to have the combo box look at a separate Word document which you
> could maintain.  When the UserForm Initializes it goes out to your separate
[quoted text clipped - 140 lines]
> >
> > John
Doug Robbins - Word MVP - 23 Oct 2006 19:04 GMT
Actually, that is mine.

Signature

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

> One way is to have the combo box look at a separate Word document which
> you could maintain.  When the UserForm Initializes it goes out to your
[quoted text clipped - 142 lines]
>>
>> John
Dawn Crosier, MVP - 23 Oct 2006 23:48 GMT
> Actually, that is mine.

Sorry, Doug!  I had no idea!  I got it from Greg and have kept it around in
my Code Library with his name associated.  I hate to take credit for someone
else's work, but I don't mind using it.

I'll switch my notes!

Signature

Dawn Crosier
Microsoft MVP
"Education Lasts a Lifetime"

This message was posted to a newsgroup,  Please post replies and questions
to the group so that others can learn as well.

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.