 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / November 2007Input box based upon a list
I would like to use an INPUT BOX that rather than having a user key in something, they would choose from a list. Is this possible? If so how would you code? A userform is not what I am looking for as this is not part of the document Thanks, Bryan Does an autotextlist do what you want? http://www.word.mvps.org/FAQs/TblsFldsFms/AutoTextList.htm > I would like to use an INPUT BOX that rather than having a user key in > something, they would choose from a list. Is this possible? [quoted text clipped - 3 lines] > Thanks, > Bryan  SignatureRuss drsmN0SPAMikleAThotmailD0Tcom.INVALID Autotextlist is not what I'm looking for. Upon saving a doc, I wanted an Input Box for the user to supply info. Then I thought rather than having them type, I would supply a drop-down of options. This is not inside the document. Thanks, Bryan > Does an autotextlist do what you want? > http://www.word.mvps.org/FAQs/TblsFldsFms/AutoTextList.htm [quoted text clipped - 6 lines] > > Thanks, > > Bryan Then it sounds like what you need is a userform and I think that you perhaps have the wrong idea about what that actually is. See the article "How to create a Userform" at: http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm You would have a combobox on the userform that is loaded with the items from which you want the user to make a selection. SignatureHope 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 > Autotextlist is not what I'm looking for. > Upon saving a doc, I wanted an Input Box for the user to supply info. [quoted text clipped - 16 lines] >> > Thanks, >> > Bryan In reading the userform article, 2 things 1) I would like the box to appear after I have selected my 'Save to IR' button 2) I don't need or want this info on the document Maybe I leave as an input box? Thanks, Bryan > Then it sounds like what you need is a userform and I think that you perhaps > have the wrong idea about what that actually is. [quoted text clipped - 26 lines] > >> > Thanks, > >> > Bryan Bryan, See inline. > In reading the userform article, 2 things > 1) I would like the box to appear after I have selected my 'Save to IR' button The UserForm1.Show command can be issued from any module, the example at the website just talks about using an autoexec subroutine, but you could issue the command from your 'Save to IR' macro code. > 2) I don't need or want this info on the document Like a popup input box, a Userform is the Cadillac of information retrieval dialogs and can be made simple or extravagant. The information gathered from the user must be injected in the document by code, it doesn't automatically appear into the document. http://gregmaxey.mvps.org/Repeating_Data.htm > Maybe I leave as an input box? > [quoted text clipped - 31 lines] >>>>> Thanks, >>>>> Bryan SignatureRuss drsmN0SPAMikleAThotmailD0Tcom.INVALID I see now that I can use the UserForm inside another macro, although as mentioned I do not need the info on the document, I will only use it in my string of building the file name. If it needs to be on the form can I hide it? Thanks, Bryan > Bryan, > See inline. [quoted text clipped - 46 lines] > >>>>> Thanks, > >>>>> Bryan Bryan, I not sure what you mean by "If it needs to be on the form can I hide it?" The UserForm popup gathers information from the user, you store in a variable and manipulate it as you please. You decide if and where you would display it in the main document, if at all. If you just need the user to pick an item from a list to help develop a pathname to store the document information, that is OK. Information can also be stored in document variables and aren't normally seen by the user. Storing Values When a Macro Ends http://snipurl.com/swu3 > I see now that I can use the UserForm inside another macro, although as > mentioned I do not need the info on the document, I will only use it in my [quoted text clipped - 56 lines] >>>>>>> Thanks, >>>>>>> Bryan SignatureRuss drsmN0SPAMikleAThotmailD0Tcom.INVALID I created my userform and inserted a combo box. Being a novice at this: 1) How do I populate the combo box with info for selection 2) How do I call or invoke from my macro 'SaveIR' 3) How is the variable selected from the combo box passed back for me to use in my macro? Thanks for all the info, Bryan > Bryan, > I not sure what you mean by "If it needs to be on the form can I hide it?" [quoted text clipped - 68 lines] > >>>>>>> Thanks, > >>>>>>> Bryan To populate a combobox (or listbox, you use the .AddItem command. If the information that is to be loaded into the combobox will not change, you can have the items to be loaded hard coded into the routine (usually the Initialize Event of the userform) If the information to be loaded is likely to change, it is more convenient to store it outside of the procedure. This routine loads a listbox (or 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 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 our 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. To get hold of the information for the item that is selected in the combobox, you use the .Value attribute of the combobox (if there is only one column, you do not need to worry about setting the .BoundColumn attribute) To cause the used form to be displayed, you use the .Show command in the routine from which you want to cause it to be displayed. SignatureHope 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 >I created my userform and inserted a combo box. > Being a novice at this: [quoted text clipped - 94 lines] >> >>>>>>> Thanks, >> >>>>>>> Bryan I appreciate all the help and the detailed example. Thanks, Bryan > To populate a combobox (or listbox, you use the .AddItem command. If the > information that is to be loaded into the combobox will not change, you can [quoted text clipped - 174 lines] > >> >>>>>>> Thanks, > >> >>>>>>> Bryan Doug, I initially used a combobox and used the additem at run time but, thought I better use a file as info may change. I changed to a listbox but, I am unable to save "disk is full" message. Usually get this with an error. My code: Private Sub CommandButton1_Click() Dim i As Integer, Addressee As String Addressee = "" For i = 1 To ListBox1.ColumnCount ListBox1.BoundColumn = i Addressee = ListBox1.Value Next i UserForm1.Hide End Sub 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:="u:\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 I used the code as described below and removed the word wrap where needed. I invoke the userform using: Do Until strDocType <> "" UserForm1.Show strDocType = UserForm1.ListBox1.Value Loop Thanks, Bryan > I appreciate all the help and the detailed example. > [quoted text clipped - 179 lines] > > >> >>>>>>> Thanks, > > >> >>>>>>> Bryan I like the combobox although getting a run-time error 'Could not set value' Here is my Combobox : Private Sub CommandButton1_Click() Dim i As Integer, Addressee As String Addressee = "" For i = 1 To ComboBox1.ColumnCount ComboBox1.BoundColumn = i Addressee = ComboBox1.Value Next i UserForm1.Hide End Sub """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" 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:="u:\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 ComboBox1.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 ComboBox1.Value() = MyArray ' Close the file containing the client details sourcedoc.Close SaveChanges:=wdDoNotSaveChanges End Sub Invoked from: Do Until strDocType <> "" UserForm1.Show 'strDocType = UserForm1.ComboBox1.text MsgBox strDocType Loop I would like to get the above piece working as well, can't save with strdoctype = Userform1.combobox1.text Thanks, Bryan > > I appreciate all the help and the detailed example. > > [quoted text clipped - 179 lines] > > > >> >>>>>>> Thanks, > > > >> >>>>>>> Bryan With that code, you would be overwriting the value of Addressee with the data from each column of the selected record in the combobox. Also, you are not doing anything with what is finally stored in the variable Addressee. SignatureHope 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 >I like the combobox although getting a run-time error 'Could not set value' > Here is my Combobox : [quoted text clipped - 284 lines] >> > > >> >>>>>>> Thanks, >> > > >> >>>>>>> Bryan I am using the same snippet of code that you provided before. I want only one selection. I am returning addressee from where invoked Userform1.Show 'strdoctype' Thanks, Bryan > With that code, you would be overwriting the value of Addressee with the > data from each column of the selected record in the combobox. Also, you are [quoted text clipped - 252 lines] > >> > > >> >>>> > >> > > >> >>>> http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm Here is the relevant part of the code that I provided: 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 and here is your version: Private Sub CommandButton1_Click() Dim i As Integer, Addressee As String Addressee = "" For i = 1 To ComboBox1.ColumnCount ComboBox1.BoundColumn = i Addressee = ComboBox1.Value Next i UserForm1.Hide End Sub They are not the same, and the comments that I made about your code are based on the deficiencies in it. SignatureHope 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 >I am using the same snippet of code that you provided before. I want only >one [quoted text clipped - 291 lines] >> >> > > >> >>>> >> >> > > >> >>>> http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm Doug, I apologize if my question was misconstrued. The code you supplied helped tremendously. I am only trying to tweak it a bit to use a combobox rather than a listbox which I cannot get to work. My use for the userform is to get one value from a selection to be used in naming the document saved only. In taking your advice I created a work doc to supply my list rather than 'hard-coding' in the macro. When I do this with the userform displays but the document is grayed, rather than the userform overlaying the document. Is this because of 2 instances of Word? Thanks, Bryan > Here is the relevant part of the code that I provided: > [quoted text clipped - 247 lines] > >> >> > > >> variable and manipulate it as you please. You decide if and > >> >> > > >> where Bryan, When you work on a Word document, it should be opened from the hard drive and not from any removable media (floppy drive, etc.). I don't know how you could have gotten a "Disk Full" message from that piece of code you showed since it wasn't doing any disk writing, was it? Normally you should work with one instance of the Word application that can have multiple documents open because it is easier to refer to a particular document when needed. You are not supplying enough information about what you are using to populate the combo box, whether is populates OK and works. What works, what doesn't. If there are errors, we need to know the exact line and context (surrounding code) and the exact error message verbiage. Are you using Option Explicit? You should. It helps to point out typos and undeclared variables, etc. http://www.cpearson.com/excel/DeclaringVariables.aspx http://www.cpearson.com/excel/variables.htm > Doug, > I apologize if my question was misconstrued. The code you supplied helped [quoted text clipped - 262 lines] >>>>>>>>>> variable and manipulate it as you please. You decide if and >>>>>>>>>> where SignatureRuss drsmN0SPAMikleAThotmailD0Tcom.INVALID I used the adressee = addressee & listbox1.value & vbcr That solved the gray issue (not overlaying properly) This works as expected now. Only question remaining is why I cannot get it to work with a combobox. I get this run-time error message: Could not set the value property. Type mismatch. When I click debug it points to the userform.show line. My clients.doc is this in a table: Endorsement Renewal Summary Misc Note Payment Here is all my code. Macro: Sub ir() ' ' ir Macro ' Macro created 10/3/2007 by bjsorens ' ' Dim strpol, strco, strprfx, strpolz Dim Response As String Dim msg As String Dim Style As String Dim ans Dim strDocType strpol = ActiveDocument.FormFields("Text1").Result strco = Mid(strpol, 1, 2) Dim USERID Dim myTemp, myTempName Set myTemp = ActiveDocument.AttachedTemplate myTempName = myTemp.Name USERID = GetUserName Dim myTemp1, myTempName1 Set myTemp1 = ActiveDocument myTempName1 = myTemp1.Name MsgBox "Doc Name is" & myTempName1 MsgBox myTempName MsgBox USERID If IsNumeric(Mid(myTempName1, 1, 3)) = True Then MsgBox "true" & Mid(myTempName1, 1, 3) Else MsgBox "false" & Mid(myTempName1, 1, 3) End If 'Dim MyDoc 'MyDoc = InputBox("Enter Doc", "Doc") 'MsgBox MyDoc Do Until strDocType <> "" UserForm1.Show strDocType = UserForm1.ComboBox1.text MsgBox strDocType Loop ''''' msg = "Are you sure you want to Save Document to ImageRight?" Style = vbYesNo + vbInformation + vbDefaultButton1 Response = MsgBox(msg, Style) ''''''' If strco = "01" Or strco = "02" Or strco = "03" Then strprfx = Mid(strpol, 4, 4) MsgBox (Len(strprfx)) If Mid(strprfx, 4, 1) <> " " Then strprfx = Mid(strprfx, 1, 3) + " " strpolz = Mid(strpol, 7, 9) Else strpolz = Mid(strpol, 8, 9) End If strpx = UCase(strprfx) If Len(strpolz) = 9 Then If Mid(strpolz, 1, 2) = "00" Then strpolz = Mid(strpolz, 3, 7) End If End If Else End If strpx = UCase(strprfx) If Len(strpolz) = 9 Then If Mid(strpolz, 1, 2) = "00" Then strpolz = Mid(strpolz, 3, 7) End If End If strusr = UCase(USERID) If Len(strpol) < 10 Then sline = sline & String(10 - Len(Trim(strpol)), "0") & strpol MsgBox sline End If If Len(strpol) >= 10 Then sline = sline & Right(strpol, 10) MsgBox sline End If 'PLUW_01 PAP 0012345_19040_ENDT__________28_184_MXLEE____C_Z.doc If Response = vbYes Then newname = "u:\PLUW_" + strco + " " + strpx + strpolz + "_" + "19040_" + "ENDT" + "__________" + "28" + "_" + "184" + "_" + strusr + "____" + "C_Z.doc" 'newname = "u:\PLUW_" + strco + " " + strpx + strpolz + "_" + "19040_" + "ENDT" + "________________" + "C_Z.doc" ActiveDocument.SaveAs FileName:=newname, FileFormat:= _ wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ SaveAsAOCELetter:=False 'To close application ActiveDocument.Close 'next 2 are to close word application 'Application.Quit 'Application.StatusBar = "Application Closing." 'ActiveDocument.FormFields("Text23").Result = "" Else ActiveDocument.Activate End If End Sub ////////// BREAK ////////////////////////// My userform code: Private Sub CommandButton1_Click() Dim i As Integer, Addressee As String Addressee = "" For i = 1 To ComboBox1.ColumnCount ComboBox1.BoundColumn = i Addressee = Addressee & ComboBox1.Value & vbCr MsgBox "<<" & Addressee & ">>" & " column: " & i Next i UserForm1.Hide End Sub 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:="u:\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 ComboBox1.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 ComboBox1.Value() = MyArray ' Close the file containing the client details sourcedoc.Close SaveChanges:=wdDoNotSaveChanges End Sub Hope this helps solve a mystery on my part, Bryan > Bryan, > When you work on a Word document, it should be opened from the hard drive [quoted text clipped - 256 lines] > >>>>>>>> the > >>>>>>>> routine from which you want to cause it to be displayed. Copy ALL of the code from the form into a message so that we can see it. A combobox can be used just as well as a listbox, but the problem is probably in the code that you are using to populate the combobox and nothing to do with the code that you now have apparently sorted out to get the result. SignatureHope 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 >I used the adressee = addressee & listbox1.value & vbcr > That solved the gray issue (not overlaying properly) [quoted text clipped - 472 lines] >> >>>>>>>> the >> >>>>>>>> routine from which you want to cause it to be displayed. I spoke too soon regarding my listbox as well. When entering my macro the first time, the userform shows with a grayed out document behind. When I click my macro the second time the userform shows with the document behind it. My clients.doc for the userform is 1 column and 6 row, nothing else on the document. Ideas? Here is my userform code: 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 'Addressee = ListBox1.Value Next i UserForm1.Hide End Sub 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:="u:\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 I would gladly send the documents if you'd like to see them. Regarding adding a break point. How? Thanks again, Bryan > Copy ALL of the code from the form into a message so that we can see it. A > combobox can be used just as well as a listbox, but the problem is probably [quoted text clipped - 259 lines] > >> >> are > >> >> based on the deficiencies in it. Bryan, The Debug menu is your friend. ;-) A breakpoint is just a place where you want to pause program execution. I think you have three ways to toggle a breakpoint. Use ALT/F11 to show your vba code. Put your cursor in the line where you want a breakpoint and press F9 to toggle a breakpoint. Or go the Debug menu and select breakpoint. Or left click in left margin of code window next to the line you want to toggle a breakpoint. Some lines won't accept a breakpoint, i.e., where you declare a variable. Just repeat what you did to undo (toggle) a breakpoint or go to the Debug menu and select clear all breakpoints or use the shortcut key combination shown next to that menu item. When execution is paused at a breakpoint, you can single step through the code with the F8 key. To resume running at 'full speed', if you want, you can go to the code screen and in one of the toolbars above you have buttons that look like play, pause, and stop buttons on a DVD player. Hit the play button to resume. Hit the stop button to completely reset the running of the macro. > I spoke too soon regarding my listbox as well. > When entering my macro the first time, the userform shows with a grayed out [quoted text clipped - 317 lines] >>>>>> are >>>>>> based on the deficiencies in it. SignatureRuss drsmN0SPAMikleAThotmailD0Tcom.INVALID The Initialize code assumed that you had more than one column of data. The modified code below should deal with the case where you only have one column or more: 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:="u:\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 If j > 1 then 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 Else ReDim MyArray(i) For m = 0 To i - 1 Set myitem = sourcedoc.Tables(1).Cell(m + 2, 1).Range myitem.End = myitem.End - 1 MyArray(m) = myitem.Text Next m End If ' Load data into ListBox1 ListBox1.List() = MyArray ' Close the file containing the client details sourcedoc.Close SaveChanges:=wdDoNotSaveChanges End Sub SignatureHope 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 >I spoke too soon regarding my listbox as well. > When entering my macro the first time, the userform shows with a grayed [quoted text clipped - 331 lines] >> >> >> are >> >> >> based on the deficiencies in it. I changed the code in my template (let's call it X1) as below, however the same thing occurs. Doc1 is created from the Template (X1). When my macro runs the first time and the userform is presented, the Doc1 document is greyed out (cannot see Doc1). When I select one of the entries in the list and continue and then run my macro again the userform is presented with the Doc1 behind it (Doc1 is visible). Does this make sense? Any ideas? > The Initialize code assumed that you had more than one column of data. The > modified code below should deal with the case where you only have one column [quoted text clipped - 261 lines] > >> > > >> > Private Sub UserForm_Initialize() Just exactly how do you intend this thing to be used? SignatureHope 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 >I changed the code in my template (let's call it X1) as below, however the > same thing occurs. [quoted text clipped - 286 lines] >> >> > >> >> > Private Sub UserForm_Initialize() The userform info will be used in the file name as below (strDocType): newname = "u:\PLUW_" + strco + " " + strpx + strpolz + "_" + "19040_" + strDocType + "__________" + "28" + "_" + "184" + "_" + strusr + "____" + "C_Z.doc" I got it to where it works except that when the userform shows, the document does not show. If I hard code the values using .AddItem then it's fine. By using a document to load the userform, which is preferred as values can change, the document does not show. Thanks, Bryan > Just exactly how do you intend this thing to be used? > [quoted text clipped - 253 lines] > >> >> > "19040_" > >> >> > + Bryan, You open Word and open a document, Doc1, based on a template. Click on your toolbar button. Userform pops up. It has focus and tries to load clients doc in its initialization routine to populate combo box. Doc1 doc is grayed out because userform made clients doc active for combo box data retrieval, keeps focus, and won't allow background graphics to update. Combo box gets populated and you make a choice and userform gets *hidden*. Doc1 doc or clients doc is now not grayed out because it now receives focus and graphics can update. Now you click on your toolbar button again. Userform pops up again above Doc1 doc or clients doc and is not gray out because perhaps userform does not run initialization routine again to use clients doc because it was hidden only, not unloaded from memory and so combo box is already populated from last time. If you single step through code, you can see how the first time the userform uses the initialization routine and if only hidden and told to show again, it doesn't use the initialization routine. Or you could put a msgbox in certain routines to popup and show when certain routines are being used. Does this sound like what is happening in your situation? > The userform info will be used in the file name as below (strDocType): > newname = "u:\PLUW_" + strco + " " + strpx + strpolz + "_" + "19040_" + [quoted text clipped - 266 lines] >>>>>>> "19040_" >>>>>>> + SignatureRuss drsmN0SPAMikleAThotmailD0Tcom.INVALID Russ, That is exactly what is happening. The doc1 loses focus the first time. Any idea on what to do so this does not happen other than coding my choices in the macro ? Thanks, Bryan > Bryan, > You open Word and open a document, Doc1, based on a template. [quoted text clipped - 254 lines] > >>>>>>> strpolz = Mid(strpolz, 3, 7) > >>>>>>> End If Bryan, Put a breakpoint on the line above the line that debug pointed to. When the macro stops at the breakpoint, start single stepping through the code by using the F8 key until you see the **actual** line in the Userform1 code that produces the error. I agree with Doug that it is probably the code that populates your combo box with data. I'm not sure, whether, if any of your table cells are empty, that may cause problems populating the combo box 'uniformly' so to speak so that each address has the same number of array elements. You might have to use "dummy data" like a space character to pad or make sure each cell has data. Doug may be able to speak to that issue. Yes, you may have to post all your UserForm1 code. > I used the adressee = addressee & listbox1.value & vbcr > That solved the gray issue (not overlaying properly) [quoted text clipped - 444 lines] >>>>>>>>>> the >>>>>>>>>> routine from which you want to cause it to be displayed. SignatureRuss drsmN0SPAMikleAThotmailD0Tcom.INVALID Bryan, Doug's code nicely created a multi-line address and neatly sent it to a bookmark in the document named Addressee. Why don't you use his method? Also see one debugging tip below. > I like the combobox although getting a run-time error 'Could not set value' > Here is my Combobox : [quoted text clipped - 5 lines] > ComboBox1.BoundColumn = i > Addressee = ComboBox1.Value For debugging purposes temporarily use here: Msgbox "<<" & Addressee & ">>" & " column: " & i > Next i > UserForm1.Hide [quoted text clipped - 38 lines] > Do Until strDocType <> "" > UserForm1.Show > 'strDocType = UserForm1.ComboBox1.text > [quoted text clipped - 193 lines] >>>>>>>>>>>> Thanks, >>>>>>>>>>>> Bryan SignatureRuss drsmN0SPAMikleAThotmailD0Tcom.INVALID
|
Reply to this Message |
 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.