 |
 | 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 / August 2008Transferring listbox values into document
Hi everyone, I have 4 combo boxes and 1 text box set up on one of the pages of a multipage form in Word. The user chooses values from the 4 combo boxes and enters something into the text box and presses an ³Add² button (see code below for the add button) to insert the values into a 5 column listbox. So 5 values are getting inserted into each row of the listbox and the user can enter as many rows as needed. In other parts of the userform I was using variables in the code to insert the values into locations in the main document where a corresponding DOCVARIABLE FIELD existed, eg. .Variables("Name").Value = txtName.Text .Variables("Title").Value = txtTitle.Text .Variables("Position").Value = txtPosition.Text I don¹t think this is possible for this scenario though. There are 5 values from this page that need to get inserted into the Word document once the finish button is pressed, as follows: cboMonth4.Value cboYear4.Value cboMonth5.Value cboYear5.Value txtProject_Name.Value The above 5 values represent 1 complete row in the listbox and would need to appear in the following order in the file, eg. January 03 to March 04 [these 4 values from the combo box, ie. January, 03, March, 04, would go on one line] Project Name [The project name would go on the line beneath] So the problem is that I can¹t just dump the values into DOCVARIABLE fields because there will be more than one row and I don¹t know how many rows there will be in advance. Is there a way to gather each of the rows in the list box and transfer the values into the document, one after the other? The values in the first line, eg. January 03 to March 04 would be need to take on a style and the second row would also need to take on its own style, so I don¹t know if this would have to be set in the code itself. Is it possible for someone to show me how I¹d set this up in the code? Any help is much appreciated. Gabrielle Private Sub cmdAddProject_Click() With lstMajor_Projects .AddItem cboMonth4.Value .Column(1, .ListCount - 1) = cboYear4.Value .Column(2, .ListCount - 1) = cboMonth5.Value .Column(3, .ListCount - 1) = cboYear5.Value .Column(4, .ListCount - 1) = txtProject_Name.Value End With cboMonth4.Value = "" cboYear4.Value = "" cboMonth5.Value = "" cboYear5.Value = "" txtProject_Name.Value = "" End Sub Transferring listbox values into documentWhat will separate each January 03 to March 04 [these 4 values from the combo box, ie. January, 03, March, 04, would go on one line] Project Name [The project name would go on the line beneath] from the next January 03 to March 04 [these 4 values from the combo box, ie. January, 03, March, 04, would go on one line] Project Name [The project name would go on the line beneath] You can iterate through each entry in the listbox obtaining the values from each column by use of the .BoundColumn property and insert the value into a range in the document, or create a string that contains each value with a vbCr before the Project Name value, and you could then insert another vbCr before getting the next set of data from the listbox. The easiest way to do the style thing would probably be to have the data inserted into the rows of a table and then you could iterate throught the table, applying the required style to each alternate row.  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 Hi everyone, I have 4 combo boxes and 1 text box set up on one of the pages of a multipage form in Word. The user chooses values from the 4 combo boxes and enters something into the text box and presses an "Add" button (see code below for the add button) to insert the values into a 5 column listbox. So 5 values are getting inserted into each row of the listbox and the user can enter as many rows as needed. In other parts of the userform I was using variables in the code to insert the values into locations in the main document where a corresponding DOCVARIABLE FIELD existed, eg. .Variables("Name").Value = txtName.Text .Variables("Title").Value = txtTitle.Text .Variables("Position").Value = txtPosition.Text I don't think this is possible for this scenario though. There are 5 values from this page that need to get inserted into the Word document once the finish button is pressed, as follows: cboMonth4.Value cboYear4.Value cboMonth5.Value cboYear5.Value txtProject_Name.Value The above 5 values represent 1 complete row in the listbox and would need to appear in the following order in the file, eg. January 03 to March 04 [these 4 values from the combo box, ie. January, 03, March, 04, would go on one line] Project Name [The project name would go on the line beneath] So the problem is that I can't just dump the values into DOCVARIABLE fields because there will be more than one row and I don't know how many rows there will be in advance. Is there a way to gather each of the rows in the list box and transfer the values into the document, one after the other? The values in the first line, eg. January 03 to March 04 would be need to take on a style and the second row would also need to take on its own style, so I don't know if this would have to be set in the code itself. Is it possible for someone to show me how I'd set this up in the code? Any help is much appreciated. Gabrielle Private Sub cmdAddProject_Click() With lstMajor_Projects .AddItem cboMonth4.Value .Column(1, .ListCount - 1) = cboYear4.Value .Column(2, .ListCount - 1) = cboMonth5.Value .Column(3, .ListCount - 1) = cboYear5.Value .Column(4, .ListCount - 1) = txtProject_Name.Value End With cboMonth4.Value = "" cboYear4.Value = "" cboMonth5.Value = "" cboYear5.Value = "" txtProject_Name.Value = "" End Sub Thanks for the reply, There will just be a carriage return separating each... January 03 to March 04 [these 4 values from the combo box, ie. January, 03, March, 04, would go on one line] Project Name [The project name would go on the line beneath] ...from the next. And there would be an n-dash separating the dates, eg. January 03 March 04, instead of ³to². I don¹t have the option to have the data in a table as I need to have the data land in an already styled template and there¹s no table in the template. I¹d really just like to have the data land in ordinary paragraphs if possible and have them somehow styled is this harder to do? Do you have any sample code I can use to begin to work with? I¹m still really just learning VBA and I haven¹t come across this type of situation yet so I can¹t adapt any of the code I have. > What will separate each > [quoted text clipped - 15 lines] > inserted into the rows of a table and then you could iterate throught the > table, applying the required style to each alternate row. Re: Transferring listbox values into documentI am not sure how you are going to specify where in the document the data is to be placed, but the following is how you would build the strings of data to be inserted: Dim i As Long Dim str1 As String Dim str2 As String With ListBox1 For i = 0 To .ListCount - 1 str1 = "" str2 = "" If .selected(i) Then str1 = .list(i, 1) & " " & .list(i, 2) & " - " & .list(i, 3) & " " & .list(i, 4) str2 = .list(i, 5) End If 'code to insert str1 and str2 into document Next i End With 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 Thanks for the reply, There will just be a carriage return separating each... January 03 to March 04 [these 4 values from the combo box, ie. January, 03, March, 04, would go on one line] Project Name [The project name would go on the line beneath] ...from the next. And there would be an n-dash separating the dates, eg. January 03 - March 04, instead of "to". I don't have the option to have the data in a table as I need to have the data land in an already styled template and there's no table in the template. I'd really just like to have the data land in ordinary paragraphs if possible and have them somehow styled - is this harder to do? Do you have any sample code I can use to begin to work with? I'm still really just learning VBA and I haven't come across this type of situation yet so I can't adapt any of the code I have. ------------------------------------------------------------------------------ in article ##Ui2xXpIHA.4884@TK2MSFTNGP06.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 24/4/08 5:58 AM: What will separate each January 03 to March 04 [these 4 values from the combo box, ie. January, 03, March, 04, would go on one line] Project Name [The project name would go on the line beneath] from the next January 03 to March 04 [these 4 values from the combo box, ie. January, 03, March, 04, would go on one line] Project Name [The project name would go on the line beneath] You can iterate through each entry in the listbox obtaining the values from each column by use of the .BoundColumn property and insert the value into a range in the document, or create a string that contains each value with a vbCr before the Project Name value, and you could then insert another vbCr before getting the next set of data from the listbox. The easiest way to do the style thing would probably be to have the data inserted into the rows of a table and then you could iterate throught the table, applying the required style to each alternate row. Okay great, but yeah I¹m not sure either how I¹m going to specifty where the data is placed. Could I use a DOCVARIABLE field as a marker for the landing point of the first string? If so, wouldn¹t I need to put vbLf in the str1 code line to force the next str2 string to the following line, and then have vbCr at the end of the str2 code line to force a paragraph return and then the next row in the listbox would get inserted? Does this make sense to you? But it doesn¹t take care of the styling I mean I could style the DOCVARIABLE but then all of the rows in the list would be styled with this same formatting whereas I need the first string styled one way and the second styled another. Do you have any other ideas/suggestions? > I am not sure how you are going to specify where in the document the data is > to be placed, but the following is how you would build the strings of data to [quoted text clipped - 15 lines] > Next i > End With Re: Transferring listbox values into documentIf you can put a docvariable field in the template, then you could insert a one row one column table and the code could be inserting the strings into rows of that table and then by iterating through the rows, you could set the Styles as required. The table need not have borders if that is the issue. 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 Okay great, but yeah I'm not sure either how I'm going to specifty where the data is placed. Could I use a DOCVARIABLE field as a marker for the landing point of the first string? If so, wouldn't I need to put vbLf in the str1 code line to force the next str2 string to the following line, and then have vbCr at the end of the str2 code line to force a paragraph return and then the next row in the listbox would get inserted? Does this make sense to you? But it doesn't take care of the styling - I mean I could style the DOCVARIABLE but then all of the rows in the list would be styled with this same formatting whereas I need the first string styled one way and the second styled another. Do you have any other ideas/suggestions? in article uORBBRbpIHA.4620@TK2MSFTNGP06.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 24/4/08 12:38 PM: I am not sure how you are going to specify where in the document the data is to be placed, but the following is how you would build the strings of data to be inserted: Dim i As Long Dim str1 As String Dim str2 As String With ListBox1 For i = 0 To .ListCount - 1 str1 = "" str2 = "" If .selected(i) Then str1 = .list(i, 1) & " " & .list(i, 2) & " - " & .list(i, 3) & " " & .list(i, 4) str2 = .list(i, 5) End If 'code to insert str1 and str2 into document Next i End With Okay will try and give the table method a go. Thanks > If you can put a docvariable field in the template, then you could insert a > one row one column table and the code could be inserting the strings into rows > of that table and then by iterating through the rows, you could set the Styles > as required. The table need not have borders if that is the issue. Hi, Sorry to come back to this post so long after it was started but I wondered if I could ask a question about the code that you provided to build the two strings (see below). The code, as it is now, assumes that the user will always select something from the listbox, whereas the listbox in this case is being used as a temporary container before the values are inserted into the the word document, ie. nothing will ever be selected. I wondered if you could help me revise the code so that it builds the strings but doesn¹t test for anything being selected? Thanks again > Okay great, but yeah I¹m not sure either how I¹m going to specifty where the > data is placed. Could I use a DOCVARIABLE field as a marker for the landing [quoted text clipped - 29 lines] >> Next i >> End With Re: Transferring listbox values into documentAssuming that in your listbox you have multiple datarows each with month day month day project and using the one column table approach that I recommend, you would use Dim i As Long Dim str1 As String Dim str2 As String Dim atable As Table Set atable = ActiveDocument.Tables(n) 'Index number of the table in the document With ListBox1 For i = 0 To .ListCount - 1 str1 = "" str2 = "" str1 = .list(i, 1) & " " & .list(i, 2) & " - " & .list(i, 3) & " " & .list(i, 4) str2 = .list(i, 5) With atable .Rows.Add .Rows(Rows.Count).Cells(1).Range.Text = str1 .Rows(Rows.Count).Cells(1).Range.Style = "style for this data" .Rows.Add .Rows(Rows.Count).Cells(1).Range.Text = str2 .Rows(Rows.Count).Cells(1).Range.Style = "style for this data" End With Next i End With 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 Hi, Sorry to come back to this post so long after it was started but I wondered if I could ask a question about the code that you provided to build the two strings (see below). The code, as it is now, assumes that the user will always select something from the listbox, whereas the listbox in this case is being used as a temporary container before the values are inserted into the the word document, ie. nothing will ever be selected. I wondered if you could help me revise the code so that it builds the strings but doesn't test for anything being selected? Thanks again ------------------------------------------------------------------------------ in article C4365C6E.472D%officelink@iinet.net.au, Officelink at officelink@iinet.net.au wrote on 24/4/08 3:49 PM: Okay great, but yeah I'm not sure either how I'm going to specifty where the data is placed. Could I use a DOCVARIABLE field as a marker for the landing point of the first string? If so, wouldn't I need to put vbLf in the str1 code line to force the next str2 string to the following line, and then have vbCr at the end of the str2 code line to force a paragraph return and then the next row in the listbox would get inserted? Does this make sense to you? But it doesn't take care of the styling - I mean I could style the DOCVARIABLE but then all of the rows in the list would be styled with this same formatting whereas I need the first string styled one way and the second styled another. Do you have any other ideas/suggestions? in article uORBBRbpIHA.4620@TK2MSFTNGP06.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 24/4/08 12:38 PM: I am not sure how you are going to specify where in the document the data is to be placed, but the following is how you would build the strings of data to be inserted: Dim i As Long Dim str1 As String Dim str2 As String With ListBox1 For i = 0 To .ListCount - 1 str1 = "" str2 = "" If .selected(i) Then str1 = .list(i, 1) & " " & .list(i, 2) & " - " & .list(i, 3) & " " & .list(i, 4) str2 = .list(i, 5) End If 'code to insert str1 and str2 into document Next i End With Thanks for this code. This table method will be useful for another project I¹m working which is great. For this situation however, I¹ve decided not to use the table and have come up with the following code that should insert/style the strings: [CODE] Option Explicit Public str1 As String Public str2 As String Public i As Long Private Sub cmdFinish_Click() Selection.HomeKey Unit:=wdStory Selection.GoTo What:=wdGoToBookmark, Name:="Major_Projects" With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) InsertProject Next i End With End Sub [/CODE] The following procedure is located in a separate module and is being called by the procedure above: [CODE] Sub InsertProject() 'type out first line Selection.TypeText Text:=str1 ' apply the style to the selected str1 paragraph Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" 'type out second line Selection.TypeText Text:=str2 ' apply the style to the selected str2 paragraph Selection.Range.Style = "Bullet Normal" 'put out a couple line breaks Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph End Sub [/CODE] When I test by entering two rows worth of information into the listbox, it all works as intended, ie. the insertion point, styling etc. but the strings aren't actually being inserted - the paragraphs are empty. I declared the variables as public, eg. Public str1 As String Public str2 As String Public i As Long ...so they could be accessed by the procedure that's being called but it doesn't seem to have helped. It looks like the strings that were built in the one module aren't being recognised by the InsertProject() procedure in the other module. Are you able to tell me why the contents of str1 and str2 aren't getting inserted, even though everything else works ok? Should I be using arguments instead of public variables to do this if so can you help? Again the code you¹ve provided will be very helpful for another project so it won¹t go to waste. > Assuming that in your listbox you have multiple datarows each with > [quoted text clipped - 27 lines] > End With > Re: Transferring listbox values into documentI cannot replicate your problem. When I set up such an arrangement, the strings were inserted and formatted. There is probably no real advantage in having the code that is in InsertProject is a separate module/routine. Try just replacing the InsertProject in the cmdFinish_Click event with the code that is presently in the InsertProject routine. 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 Thanks for this code. This table method will be useful for another project I'm working which is great. For this situation however, I've decided not to use the table and have come up with the following code that should insert/style the strings: [CODE] Option Explicit Public str1 As String Public str2 As String Public i As Long Private Sub cmdFinish_Click() Selection.HomeKey Unit:=wdStory Selection.GoTo What:=wdGoToBookmark, Name:="Major_Projects" With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) InsertProject Next i End With End Sub [/CODE] The following procedure is located in a separate module and is being called by the procedure above: [CODE] Sub InsertProject() 'type out first line Selection.TypeText Text:=str1 ' apply the style to the selected str1 paragraph Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" 'type out second line Selection.TypeText Text:=str2 ' apply the style to the selected str2 paragraph Selection.Range.Style = "Bullet Normal" 'put out a couple line breaks Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph End Sub [/CODE] When I test by entering two rows worth of information into the listbox, it all works as intended, ie. the insertion point, styling etc. but the strings aren't actually being inserted - the paragraphs are empty. I declared the variables as public, eg. Public str1 As String Public str2 As String Public i As Long ...so they could be accessed by the procedure that's being called but it doesn't seem to have helped. It looks like the strings that were built in the one module aren't being recognised by the InsertProject() procedure in the other module. Are you able to tell me why the contents of str1 and str2 aren't getting inserted, even though everything else works ok? Should I be using arguments instead of public variables to do this - if so can you help? Again the code you've provided will be very helpful for another project so it won't go to waste. ------------------------------------------------------------------------------ in article ubW$ih1sIHA.3680@TK2MSFTNGP05.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 11/5/08 9:15 PM: Assuming that in your listbox you have multiple datarows each with month day month day project and using the one column table approach that I recommend, you would use Dim i As Long Dim str1 As String Dim str2 As String Dim atable As Table Set atable = ActiveDocument.Tables(n) 'Index number of the table in the document With ListBox1 For i = 0 To .ListCount - 1 str1 = "" str2 = "" str1 = .list(i, 1) & " " & .list(i, 2) & " - " & .list(i, 3) & " " & .list(i, 4) str2 = .list(i, 5) With atable .Rows.Add .Rows(Rows.Count).Cells(1).Range.Text = str1 .Rows(Rows.Count).Cells(1).Range.Style = "style for this data" .Rows.Add .Rows(Rows.Count).Cells(1).Range.Text = str2 .Rows(Rows.Count).Cells(1).Range.Style = "style for this data" End With Next i End With Thanks, yeah that did the trick everything¹s populating really well. Just one thing though I¹m putting in paragraphs after each string pair but I don¹t want there to be any paragraphs after the last strings (row) is inserted. Can you tell me how to do this? Also just a quick question about the code you posted first (see below). Can you tell me why you make the strings empty before building the strings, ie. str1 = "" str2 = "" Just not sure why you did this. I cannot replicate your problem. When I set up such an arrangement, the strings were inserted and formatted. > > There is probably no real advantage in having the code that is in > InsertProject is a separate module/routine. Try just replacing the > InsertProject in the cmdFinish_Click event with the code that is presently in > the InsertProject routine. >> Dim i As Long >> Dim str1 As String [quoted text clipped - 16 lines] >> .Rows(Rows.Count).Cells(1).Range.Text = str2 >> .Rows(Rows.Count).Cells(1).Range.Style = "style for this data" End With Next i End With Re: Transferring listbox values into documentWith lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 2 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) InsertProject Next i str1 = .List(i + 1, 0) & " " & .List(i + 1, 1) & " - " & .List(i + 1, 2) & " " & .List(i, 3) str2 = .List(i + 1, 4) Selection.TypeText Text:=str1 Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" End With It is probably not necessary to set the strings so that they are empty, but it is something that I often do without stopping to think if it is really necessary. Sometimes however it is. 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 Thanks, yeah that did the trick - everything's populating really well. Just one thing though - I'm putting in paragraphs after each string pair but I don't want there to be any paragraphs after the last strings (row) is inserted. Can you tell me how to do this? Also just a quick question about the code you posted first (see below). Can you tell me why you make the strings empty before building the strings, ie. str1 = "" str2 = "" Just not sure why you did this. ------------------------------------------------------------------------------ I cannot replicate your problem. When I set up such an arrangement, the strings were inserted and formatted. There is probably no real advantage in having the code that is in InsertProject is a separate module/routine. Try just replacing the InsertProject in the cmdFinish_Click event with the code that is presently in the InsertProject routine. ------------------------------------------------------------------------------ Dim i As Long Dim str1 As String Dim str2 As String Dim atable As Table Set atable = ActiveDocument.Tables(n) 'Index number of the table in the document With ListBox1 For i = 0 To .ListCount - 1 str1 = "" str2 = "" str1 = .list(i, 1) & " " & .list(i, 2) & " - " & .list(i, 3) & " " & .list(i, 4) str2 = .list(i, 5) With atable .Rows.Add .Rows(Rows.Count).Cells(1).Range.Text = str1 .Rows(Rows.Count).Cells(1).Range.Style = "style for this data" .Rows.Add .Rows(Rows.Count).Cells(1).Range.Text = str2 .Rows(Rows.Count).Cells(1).Range.Style = "style for this data" End With Next i End With in article uYAuf$6sIHA.1436@TK2MSFTNGP05.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 12/5/08 7:42 AM: Thanks for the code. I had problems with it though. The code that I used originally, ie. before the last past, was as follows: Dim i As Long Dim str1 As String Dim str2 As String Selection.HomeKey Unit:=wdStory Selection.GoTo What:=wdGoToBookmark, Name:="Major_Projects" With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) 'type out first line Selection.TypeText Text:=str1 ' apply the style to the selected str1 paragraph Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" 'type out second line Selection.TypeText Text:=str2 ' apply the style to the selected str2 paragraph Selection.Range.Style = "Bullet Normal" 'put out a couple line breaks Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph Next i End With The above code worked, ie. inserted and styled everything. I then used the code you provided just now and tested but I got the following error: Run-time error 381¹: Could not get the List property. Invalid property array index. ...and it highlighted the following line: str1 = .List(i + 1, 0) & " " & .List(i + 1, 1) & " - " & .List(i + 1, 2) & " " & .List(i, 3) Do you know what¹s happening? I also noticed that you included the call to ³InsertProject². I wasn¹t using this module anymore did you leave this in by mistake? Just to clarify, I¹m just trying to prevent the code from inserting the blank paragraphs after the very last row is inserted as it¹s not needed at that point. I understand that the following code builds the strings: With lstMajor_Projects > For i = 0 To lstMajor_Projects.ListCount - 2 > str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & > .List(i, 3) > str2 = .List(i, 4) > Next i ...but I don¹t know what¹s happening in these lines of code to remove the paragraph can you explain: str1 = .List(i + 1, 0) & " " & .List(i + 1, 1) & " - " & .List(i + 1, 2) & " " & .List(i, 3) str2 = .List(i + 1, 4) Thanks again > With lstMajor_Projects > For i = 0 To lstMajor_Projects.ListCount - 2 [quoted text clipped - 17 lines] > is something that I often do without stopping to think if it is really > necessary. Sometimes however it is. Re: Transferring listbox values into documentDid you change the For i = 0 To lstMajor_Projects.ListCount - 1 to For i = 0 To lstMajor_Projects.ListCount - 2 I was assuming that when i got to .ListCount - 2, at that point, you could probably add 1 to i to get the next last "record" That is the record with the ListIndex of 1 less that the number of records. If you made that change and you still get the error, replace str1 = .List(i + 1, 0) & " " & .List(i + 1, 1) & " - " & .List(i + 1, 2) & " " & .List(i + 1, 3) str2 = .List(i + 1, 4) with i = .ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) 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 Thanks for the code. I had problems with it though. The code that I used originally, ie. before the last past, was as follows: Dim i As Long Dim str1 As String Dim str2 As String Selection.HomeKey Unit:=wdStory Selection.GoTo What:=wdGoToBookmark, Name:="Major_Projects" With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) 'type out first line Selection.TypeText Text:=str1 ' apply the style to the selected str1 paragraph Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" 'type out second line Selection.TypeText Text:=str2 ' apply the style to the selected str2 paragraph Selection.Range.Style = "Bullet Normal" 'put out a couple line breaks Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph Next i End With The above code worked, ie. inserted and styled everything. I then used the code you provided just now and tested but I got the following error: Run-time error '381': Could not get the List property. Invalid property array index. ...and it highlighted the following line: str1 = .List(i + 1, 0) & " " & .List(i + 1, 1) & " - " & .List(i + 1, 2) & " " & .List(i, 3) Do you know what's happening? I also noticed that you included the call to "InsertProject". I wasn't using this module anymore - did you leave this in by mistake? Just to clarify, I'm just trying to prevent the code from inserting the blank paragraphs after the very last row is inserted as it's not needed at that point. I understand that the following code builds the strings: With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 2 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) Next i ...but I don't know what's happening in these lines of code to remove the paragraph - can you explain: str1 = .List(i + 1, 0) & " " & .List(i + 1, 1) & " - " & .List(i + 1, 2) & " " & .List(i, 3) str2 = .List(i + 1, 4) Thanks again ------------------------------------------------------------------------------ in article O$Tlgz$sIHA.4492@TK2MSFTNGP02.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 12/5/08 4:53 PM: With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 2 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) InsertProject Next i str1 = .List(i + 1, 0) & " " & .List(i + 1, 1) & " - " & .List(i + 1, 2) & " " & .List(i, 3) str2 = .List(i + 1, 4) Selection.TypeText Text:=str1 Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" End With It is probably not necessary to set the strings so that they are empty, but it is something that I often do without stopping to think if it is really necessary. Sometimes however it is. I had already made that first change you mentioned. I then tried using the replacement code alternative you gave but when I tested it¹s only inserting the last row. Not sure why it¹s not working. I was just thinking that since I need to control whether the paragraph is inserted at a particular point in the code, doesn¹t that mean that I need to use an if statement at that line of code, eg. > 'put out a couple line breaks if [the loop has reached the last item in the listbox] then End else [continue to put in the paragraphs] > Selection.TypeParagraph > Selection.Range.Style = "Normal" > Selection.TypeParagraph Is this type of logic correct, ie. am I on the right track. Of course I¹m happy to continue with your code if you can help me solve it. > Did you change the > [quoted text clipped - 18 lines] > str2 = .List(i, 4) > Re: Transferring listbox values into documentShow us ALL of the code that you have now. 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 had already made that first change you mentioned. I then tried using the replacement code alternative you gave but when I tested it's only inserting the last row. Not sure why it's not working. I was just thinking that since I need to control whether the paragraph is inserted at a particular point in the code, doesn't that mean that I need to use an if statement at that line of code, eg. 'put out a couple line breaks if [the loop has reached the last item in the listbox] then End else [continue to put in the paragraphs] Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph Is this type of logic correct, ie. am I on the right track. Of course I'm happy to continue with your code if you can help me solve it. ------------------------------------------------------------------------------ in article e5adeSBtIHA.3680@TK2MSFTNGP05.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 12/5/08 7:43 PM: Did you change the For i = 0 To lstMajor_Projects.ListCount - 1 to For i = 0 To lstMajor_Projects.ListCount - 2 I was assuming that when i got to .ListCount - 2, at that point, you could probably add 1 to i to get the next last "record" That is the record with the ListIndex of 1 less that the number of records. If you made that change and you still get the error, replace str1 = .List(i + 1, 0) & " " & .List(i + 1, 1) & " - " & .List(i + 1, 2) & " " & .List(i + 1, 3) str2 = .List(i + 1, 4) with i = .ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) Okay, so I have the following code attached to the userform, and the code further on down is located in 4 separate modules: [CODE] Option Explicit Dim Counter As Long Const FirstYear As Long = 1984 Const FirstYearAbbrev As Long = 1984 Public mySelected As Long Private Sub MultiPage1_Change() cmdNext.Enabled = (MultiPage1.Value < MultiPage1.Pages.Count - 1) cmdPrevious.Enabled = (MultiPage1.Value > 0) End Sub Private Sub UserForm_Initialize() MultiPage1.Value = 0 cmdChangePersonalInterest.Enabled = False cmdDeletePersonalInterest.Enabled = False cmdChangeEducation.Enabled = False cmdDeleteEducation.Enabled = False ' ***** Job History 1 ***** cboMonth1.List() = Array("January", "February", "March", "April", "May", _ "June", "July", "August", "September", "October", "November", "December") With cboYear1 .Clear For Counter = Year(Now) To FirstYear Step -1 .AddItem Counter Next End With cboState1.List() = Array("New South Wales", "Victoria", "South Australia", "Western Australia", _ "Queensland", "Northern Territory", "Tasmania", "ACT") cboCompany1.List() = Array("Company Pty Limited") ' ***** Job History 2 ***** cboMonth2.List() = Array("January", "February", "March", "April", "May", _ "June", "July", "August", "September", "October", "November", "December") With cboYear2 .Clear For Counter = Year(Now) To FirstYear Step -1 .AddItem Counter Next End With cboState2.List() = Array("New South Wales", "Victoria", "South Australia", "Western Australia", _ "Queensland", "Northern Territory", "Tasmania", "ACT") ' ***** Job History 3 ***** cboMonth3.List() = Array("January", "February", "March", "April", "May", _ "June", "July", "August", "September", "October", "November", "December") With cboYear3 .Clear For Counter = Year(Now) To FirstYear Step -1 .AddItem Counter Next End With cboState3.List() = Array("New South Wales", "Victoria", "South Australia", "Western Australia", _ "Queensland", "Northern Territory", "Tasmania", "ACT") cboCompany1.Value = "Company Pty Limited" ' ***** Major Projects 1 ***** cboMonth4.List() = Array("January", "February", "March", "April", "May", _ "June", "July", "August", "September", "October", "November", "December") With cboYear4 .Clear For Counter = Year(Now) To FirstYear Step -1 .AddItem Right(Counter, 2) Next End With 'cboYear4.List() = Array("08", "07", "06", "05", "04", "03", _ '"02", "01", "00", "99", "98", "97", "96", "95", "94", _ '"93", "92", "91", "90", "89", "88", "87", "86", "85", "84") ' ***** Major Projects 2 ***** cboMonth5.List() = Array("January", "February", "March", "April", "May", _ "June", "July", "August", "September", "October", "November", "December") With cboYear5 .Clear For Counter = Year(Now) To FirstYear Step -1 .AddItem Right(Counter, 2) Next End With 'cboYear5.List() = Array("08", "07", "06", "05", "04", "03", _ '"02", "01", "00", "99", "98", "97", "96", "95", "94", _ '"93", "92", "91", "90", "89", "88", "87", "86", "85", "84") End Sub ' ***** Major Projects ***** Private Sub cmdChangeProject_Click() Dim var As Integer ReDim MyList(lstMajor_Projects.ListCount - 1, 4) For var = 0 To lstMajor_Projects.ListCount - 1 MyList(var, 0) = lstMajor_Projects.Column(0, var) MyList(var, 1) = lstMajor_Projects.Column(1, var) MyList(var, 2) = lstMajor_Projects.Column(2, var) MyList(var, 3) = lstMajor_Projects.Column(3, var) MyList(var, 4) = lstMajor_Projects.Column(4, var) Next MyList(mySelected, 0) = cboMonth4.Value MyList(mySelected, 1) = cboYear4.Value MyList(mySelected, 2) = cboMonth5.Value MyList(mySelected, 3) = cboYear5.Value MyList(mySelected, 4) = txtProject_Name.Text lstMajor_Projects.Clear lstMajor_Projects.List = MyList() cboMonth4.Value = "" cboYear4.Value = "" cboMonth5.Value = "" cboYear5.Value = "" txtProject_Name.Value = "" cboMonth4.SetFocus End Sub Private Sub lstMajor_Projects_Change() mySelected = lstMajor_Projects.ListIndex If mySelected > -1 Then cboMonth4.Value = lstMajor_Projects.Column(0, mySelected) cboYear4.Value = lstMajor_Projects.Column(1, mySelected) cboMonth5.Value = lstMajor_Projects.Column(2, mySelected) cboYear5.Value = lstMajor_Projects.Column(3, mySelected) txtProject_Name.Text = lstMajor_Projects.Column(4, mySelected) cboMonth4.SetFocus cmdChangeProject.Enabled = True Else cmdChangeProject.Enabled = False End If cmdDeleteProject.Enabled = cmdChangeProject.Enabled End Sub Private Sub cmdAddProject_Click() With lstMajor_Projects .AddItem cboMonth4.Value .Column(1, .ListCount - 1) = cboYear4.Value .Column(2, .ListCount - 1) = cboMonth5.Value .Column(3, .ListCount - 1) = cboYear5.Value .Column(4, .ListCount - 1) = txtProject_Name.Value End With cboMonth4.Value = "" cboYear4.Value = "" cboMonth5.Value = "" cboYear5.Value = "" txtProject_Name.Value = "" cboMonth4.SetFocus End Sub Private Sub cmdDeleteProject_Click() With lstMajor_Projects .RemoveItem (.ListIndex) End With End Sub ' ***** Personal Interest ***** Private Sub cmdChangePersonalInterest_Click() Dim var As Integer ReDim MyList(lstPersonal_Interest.ListCount - 1, 1) For var = 0 To lstPersonal_Interest.ListCount - 1 MyList(var, 0) = lstPersonal_Interest.Column(0, var) Next MyList(mySelected, 0) = txtPersonal_Interest.Text lstPersonal_Interest.Clear lstPersonal_Interest.List = MyList() txtPersonal_Interest.Value = "" txtPersonal_Interest.SetFocus End Sub Private Sub lstPersonal_Interest_Change() mySelected = lstPersonal_Interest.ListIndex If mySelected > -1 Then txtPersonal_Interest.Text = lstPersonal_Interest.Column(0, mySelected) txtPersonal_Interest.SetFocus cmdChangePersonalInterest.Enabled = True Else cmdChangePersonalInterest.Enabled = False End If cmdDeletePersonalInterest.Enabled = cmdChangePersonalInterest.Enabled End Sub Private Sub cmdAddPersonalInterest_Click() With lstPersonal_Interest .AddItem txtPersonal_Interest.Value End With txtPersonal_Interest.Value = "" txtPersonal_Interest.SetFocus End Sub Private Sub cmdDeletePersonalInterest_Click() With lstPersonal_Interest .RemoveItem (.ListIndex) End With End Sub ' ***** Education ***** Private Sub cmdChangeEducation_Click() Dim var As Integer ReDim MyList(lstEducation.ListCount - 1, 1) For var = 0 To lstEducation.ListCount - 1 MyList(var, 0) = lstEducation.Column(0, var) Next MyList(mySelected, 0) = txtEducation.Text lstEducation.Clear lstEducation.List = MyList() txtEducation.Value = "" txtEducation.SetFocus End Sub Private Sub lstEducation_Change() mySelected = lstEducation.ListIndex If mySelected > -1 Then txtEducation.Text = lstEducation.Column(0, mySelected) txtEducation.SetFocus cmdChangeEducation.Enabled = True Else cmdChangeEducation.Enabled = False End If cmdDeleteEducation.Enabled = cmdChangeEducation.Enabled End Sub Private Sub cmdAddEducation_Click() With lstEducation .AddItem txtEducation.Value End With txtEducation.Value = "" txtEducation.SetFocus End Sub Private Sub cmdDeleteEducation_Click() With lstEducation .RemoveItem (.ListIndex) End With End Sub Private Sub cmdFinish_Click() Application.ScreenUpdating = False With ActiveDocument .Variables("Name").Value = txtName.Text .Variables("Title").Value = txtTitle.Text .Variables("Position").Value = txtPosition.Text .Variables("Summary_of_Experience").Value = txtSummary_of_Experience.Text .Variables("Month1").Value = cboMonth1.Value .Variables("Year1").Value = cboYear1.Value .Variables("State1").Value = cboState1.Value .Variables("Company1").Value = cboCompany1.Value .Variables("Month2").Value = cboMonth2.Value .Variables("Year2").Value = cboYear2.Value .Variables("Company2").Value = txtCompany2.Text .Variables("State2").Value = cboState2.Value .Variables("Month3").Value = cboMonth3.Value .Variables("Year3").Value = cboYear3.Value .Variables("Company3").Value = txtCompany3.Text .Variables("State3").Value = cboState3.Value .Variables("Role1").Value = txtRole1.Text .Variables("Job_Summary1").Value = txtJob_Summary1.Text .Variables("Role2").Value = txtRole2.Text .Variables("Job_Summary2").Value = txtJob_Summary2.Text .Variables("Role3").Value = txtRole3.Text .Variables("Job_Summary3").Value = txtJob_Summary3.Text End With ' Application.ScreenUpdating = True ' ***** Personal Interests ***** Dim interest As String Dim var For var = 0 To lstPersonal_Interest.ListCount - 1 interest = interest & lstPersonal_Interest.List(var) & vbCrLf Next If Len(interest) > 1 Then Call InterestBookmark("Personal_Interest", Left(interest, _ Len(interest) - 2)) End If ' ***** Education ***** Dim education As String Dim edu For edu = 0 To lstEducation.ListCount - 1 education = education & lstEducation.List(edu) & vbCrLf Next If Len(education) > 1 Then Call EducationBookmark("Education", Left(education, _ Len(education) - 2)) End If ' ***** Major Projects ***** Dim i As Long Dim str1 As String Dim str2 As String Selection.HomeKey Unit:=wdStory Selection.GoTo What:=wdGoToBookmark, Name:="Major_Projects" ' *******THIS IS THE CODE YOU PROVIDED********* 'With lstMajor_Projects ' For i = 0 To lstMajor_Projects.ListCount - 2 ' str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) ' str2 = .List(i, 4) '' InsertProject ' Next i ' i = .ListCount - 1 ' str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) ' str2 = .List(i, 4) ' Selection.TypeText Text:=str1 ' Selection.Range.Style = "Heading 2" ' Selection.TypeParagraph ' Selection.Range.Style = "Normal" ' Selection.TypeText Text:=str2 ' Selection.Range.Style = "Bullet Normal" 'End With '********************************************* With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) 'type out first line Selection.TypeText Text:=str1 ' apply the style to the selected str1 paragraph Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" 'type out second line Selection.TypeText Text:=str2 ' apply the style to the selected str2 paragraph Selection.Range.Style = "Bullet Normal" 'put out a couple line breaks Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph Next i End With UpdateAll With ActiveDocument .PrintPreview .ClosePrintPreview End With Unload Me End Sub Private Sub cmdCancel_Click() If MsgBox("Are you sure you want to Cancel? You will lose all changes.", vbYesNo + vbQuestion) = vbYes Then Set frmCompanyCV = Nothing Unload Me End If End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then If MsgBox("Are you sure you want to Cancel? You will lose all changes.", vbYesNo + vbQuestion) = vbNo Then Cancel = True End If End If End Sub Private Sub cmdNext_Click() Dim i As Long i = MultiPage1.Value If i < MultiPage1.Pages.Count - 1 Then MultiPage1.Value = i + 1 End If End Sub Private Sub cmdPrevious_Click() Dim i As Long i = MultiPage1.Value If i > 0 Then MultiPage1.Value = i - 1 End If End Sub [/CODE] The following code is located in 4 separate modules: [CODE] Option Explicit Sub EducationBookmark(strBM As String, strText As String) Dim oRange As Word.Range Set oRange = ActiveDocument.Bookmarks(strBM).Range oRange.Text = strText With oRange .Collapse Direction:=wdCollapseStart .MoveEnd Unit:=wdCharacter, Count:=Len(strText) End With ActiveDocument.Bookmarks.Add strBM, Range:=oRange End Sub [/CODE] [CODE] Option Explicit Sub InterestBookmark(strBM As String, strText As String) Dim oRange As Word.Range Set oRange = ActiveDocument.Bookmarks(strBM).Range oRange.Text = strText With oRange .Collapse Direction:=wdCollapseStart .MoveEnd Unit:=wdCharacter, Count:=Len(strText) End With ActiveDocument.Bookmarks.Add strBM, Range:=oRange End Sub [/CODE] [CODE] Option Explicit Public MyList() As String [/CODE] [CODE] Option Explicit Sub UpdateAll() Dim sty As Range For Each sty In ActiveDocument.StoryRanges sty.Fields.Update Next sty End Sub [/CODE] > Show us ALL of the code that you have now. Re: Transferring listbox values into documentI did not need ALL of that. With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 2 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) Selection.TypeText Text:=str1 Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph Next i i = .ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) Selection.TypeText Text:=str1 Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" End With 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 Okay, so I have the following code attached to the userform, and the code further on down is located in 4 separate modules: [CODE] Option Explicit Dim Counter As Long Const FirstYear As Long = 1984 Const FirstYearAbbrev As Long = 1984 Public mySelected As Long Private Sub MultiPage1_Change() cmdNext.Enabled = (MultiPage1.Value < MultiPage1.Pages.Count - 1) cmdPrevious.Enabled = (MultiPage1.Value > 0) End Sub Private Sub UserForm_Initialize() MultiPage1.Value = 0 cmdChangePersonalInterest.Enabled = False cmdDeletePersonalInterest.Enabled = False cmdChangeEducation.Enabled = False cmdDeleteEducation.Enabled = False ' ***** Job History 1 ***** cboMonth1.List() = Array("January", "February", "March", "April", "May", _ "June", "July", "August", "September", "October", "November", "December") With cboYear1 .Clear For Counter = Year(Now) To FirstYear Step -1 .AddItem Counter Next End With cboState1.List() = Array("New South Wales", "Victoria", "South Australia", "Western Australia", _ "Queensland", "Northern Territory", "Tasmania", "ACT") cboCompany1.List() = Array("Company Pty Limited") ' ***** Job History 2 ***** cboMonth2.List() = Array("January", "February", "March", "April", "May", _ "June", "July", "August", "September", "October", "November", "December") With cboYear2 .Clear For Counter = Year(Now) To FirstYear Step -1 .AddItem Counter Next End With cboState2.List() = Array("New South Wales", "Victoria", "South Australia", "Western Australia", _ "Queensland", "Northern Territory", "Tasmania", "ACT") ' ***** Job History 3 ***** cboMonth3.List() = Array("January", "February", "March", "April", "May", _ "June", "July", "August", "September", "October", "November", "December") With cboYear3 .Clear For Counter = Year(Now) To FirstYear Step -1 .AddItem Counter Next End With cboState3.List() = Array("New South Wales", "Victoria", "South Australia", "Western Australia", _ "Queensland", "Northern Territory", "Tasmania", "ACT") cboCompany1.Value = "Company Pty Limited" ' ***** Major Projects 1 ***** cboMonth4.List() = Array("January", "February", "March", "April", "May", _ "June", "July", "August", "September", "October", "November", "December") With cboYear4 .Clear For Counter = Year(Now) To FirstYear Step -1 .AddItem Right(Counter, 2) Next End With 'cboYear4.List() = Array("08", "07", "06", "05", "04", "03", _ '"02", "01", "00", "99", "98", "97", "96", "95", "94", _ '"93", "92", "91", "90", "89", "88", "87", "86", "85", "84") ' ***** Major Projects 2 ***** cboMonth5.List() = Array("January", "February", "March", "April", "May", _ "June", "July", "August", "September", "October", "November", "December") With cboYear5 .Clear For Counter = Year(Now) To FirstYear Step -1 .AddItem Right(Counter, 2) Next End With 'cboYear5.List() = Array("08", "07", "06", "05", "04", "03", _ '"02", "01", "00", "99", "98", "97", "96", "95", "94", _ '"93", "92", "91", "90", "89", "88", "87", "86", "85", "84") End Sub ' ***** Major Projects ***** Private Sub cmdChangeProject_Click() Dim var As Integer ReDim MyList(lstMajor_Projects.ListCount - 1, 4) For var = 0 To lstMajor_Projects.ListCount - 1 MyList(var, 0) = lstMajor_Projects.Column(0, var) MyList(var, 1) = lstMajor_Projects.Column(1, var) MyList(var, 2) = lstMajor_Projects.Column(2, var) MyList(var, 3) = lstMajor_Projects.Column(3, var) MyList(var, 4) = lstMajor_Projects.Column(4, var) Next MyList(mySelected, 0) = cboMonth4.Value MyList(mySelected, 1) = cboYear4.Value MyList(mySelected, 2) = cboMonth5.Value MyList(mySelected, 3) = cboYear5.Value MyList(mySelected, 4) = txtProject_Name.Text lstMajor_Projects.Clear lstMajor_Projects.List = MyList() cboMonth4.Value = "" cboYear4.Value = "" cboMonth5.Value = "" cboYear5.Value = "" txtProject_Name.Value = "" cboMonth4.SetFocus End Sub Private Sub lstMajor_Projects_Change() mySelected = lstMajor_Projects.ListIndex If mySelected > -1 Then cboMonth4.Value = lstMajor_Projects.Column(0, mySelected) cboYear4.Value = lstMajor_Projects.Column(1, mySelected) cboMonth5.Value = lstMajor_Projects.Column(2, mySelected) cboYear5.Value = lstMajor_Projects.Column(3, mySelected) txtProject_Name.Text = lstMajor_Projects.Column(4, mySelected) cboMonth4.SetFocus cmdChangeProject.Enabled = True Else cmdChangeProject.Enabled = False End If cmdDeleteProject.Enabled = cmdChangeProject.Enabled End Sub Private Sub cmdAddProject_Click() With lstMajor_Projects .AddItem cboMonth4.Value .Column(1, .ListCount - 1) = cboYear4.Value .Column(2, .ListCount - 1) = cboMonth5.Value .Column(3, .ListCount - 1) = cboYear5.Value .Column(4, .ListCount - 1) = txtProject_Name.Value End With cboMonth4.Value = "" cboYear4.Value = "" cboMonth5.Value = "" cboYear5.Value = "" txtProject_Name.Value = "" cboMonth4.SetFocus End Sub Private Sub cmdDeleteProject_Click() With lstMajor_Projects .RemoveItem (.ListIndex) End With End Sub ' ***** Personal Interest ***** Private Sub cmdChangePersonalInterest_Click() Dim var As Integer ReDim MyList(lstPersonal_Interest.ListCount - 1, 1) For var = 0 To lstPersonal_Interest.ListCount - 1 MyList(var, 0) = lstPersonal_Interest.Column(0, var) Next MyList(mySelected, 0) = txtPersonal_Interest.Text lstPersonal_Interest.Clear lstPersonal_Interest.List = MyList() txtPersonal_Interest.Value = "" txtPersonal_Interest.SetFocus End Sub Private Sub lstPersonal_Interest_Change() mySelected = lstPersonal_Interest.ListIndex If mySelected > -1 Then txtPersonal_Interest.Text = lstPersonal_Interest.Column(0, mySelected) txtPersonal_Interest.SetFocus cmdChangePersonalInterest.Enabled = True Else cmdChangePersonalInterest.Enabled = False End If cmdDeletePersonalInterest.Enabled = cmdChangePersonalInterest.Enabled End Sub Private Sub cmdAddPersonalInterest_Click() With lstPersonal_Interest .AddItem txtPersonal_Interest.Value End With txtPersonal_Interest.Value = "" txtPersonal_Interest.SetFocus End Sub Private Sub cmdDeletePersonalInterest_Click() With lstPersonal_Interest .RemoveItem (.ListIndex) End With End Sub ' ***** Education ***** Private Sub cmdChangeEducation_Click() Dim var As Integer ReDim MyList(lstEducation.ListCount - 1, 1) For var = 0 To lstEducation.ListCount - 1 MyList(var, 0) = lstEducation.Column(0, var) Next MyList(mySelected, 0) = txtEducation.Text lstEducation.Clear lstEducation.List = MyList() txtEducation.Value = "" txtEducation.SetFocus End Sub Private Sub lstEducation_Change() mySelected = lstEducation.ListIndex If mySelected > -1 Then txtEducation.Text = lstEducation.Column(0, mySelected) txtEducation.SetFocus cmdChangeEducation.Enabled = True Else cmdChangeEducation.Enabled = False End If cmdDeleteEducation.Enabled = cmdChangeEducation.Enabled End Sub Private Sub cmdAddEducation_Click() With lstEducation .AddItem txtEducation.Value End With txtEducation.Value = "" txtEducation.SetFocus End Sub Private Sub cmdDeleteEducation_Click() With lstEducation .RemoveItem (.ListIndex) End With End Sub Private Sub cmdFinish_Click() Application.ScreenUpdating = False With ActiveDocument .Variables("Name").Value = txtName.Text .Variables("Title").Value = txtTitle.Text .Variables("Position").Value = txtPosition.Text .Variables("Summary_of_Experience").Value = txtSummary_of_Experience.Text .Variables("Month1").Value = cboMonth1.Value .Variables("Year1").Value = cboYear1.Value .Variables("State1").Value = cboState1.Value .Variables("Company1").Value = cboCompany1.Value .Variables("Month2").Value = cboMonth2.Value .Variables("Year2").Value = cboYear2.Value .Variables("Company2").Value = txtCompany2.Text .Variables("State2").Value = cboState2.Value .Variables("Month3").Value = cboMonth3.Value .Variables("Year3").Value = cboYear3.Value .Variables("Company3").Value = txtCompany3.Text .Variables("State3").Value = cboState3.Value .Variables("Role1").Value = txtRole1.Text .Variables("Job_Summary1").Value = txtJob_Summary1.Text .Variables("Role2").Value = txtRole2.Text .Variables("Job_Summary2").Value = txtJob_Summary2.Text .Variables("Role3").Value = txtRole3.Text .Variables("Job_Summary3").Value = txtJob_Summary3.Text End With ' Application.ScreenUpdating = True ' ***** Personal Interests ***** Dim interest As String Dim var For var = 0 To lstPersonal_Interest.ListCount - 1 interest = interest & lstPersonal_Interest.List(var) & vbCrLf Next If Len(interest) > 1 Then Call InterestBookmark("Personal_Interest", Left(interest, _ Len(interest) - 2)) End If ' ***** Education ***** Dim education As String Dim edu For edu = 0 To lstEducation.ListCount - 1 education = education & lstEducation.List(edu) & vbCrLf Next If Len(education) > 1 Then Call EducationBookmark("Education", Left(education, _ Len(education) - 2)) End If ' ***** Major Projects ***** Dim i As Long Dim str1 As String Dim str2 As String Selection.HomeKey Unit:=wdStory Selection.GoTo What:=wdGoToBookmark, Name:="Major_Projects" ' *******THIS IS THE CODE YOU PROVIDED********* 'With lstMajor_Projects ' For i = 0 To lstMajor_Projects.ListCount - 2 ' str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) ' str2 = .List(i, 4) '' InsertProject ' Next i ' i = .ListCount - 1 ' str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) ' str2 = .List(i, 4) ' Selection.TypeText Text:=str1 ' Selection.Range.Style = "Heading 2" ' Selection.TypeParagraph ' Selection.Range.Style = "Normal" ' Selection.TypeText Text:=str2 ' Selection.Range.Style = "Bullet Normal" 'End With '********************************************* With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) 'type out first line Selection.TypeText Text:=str1 ' apply the style to the selected str1 paragraph Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" 'type out second line Selection.TypeText Text:=str2 ' apply the style to the selected str2 paragraph Selection.Range.Style = "Bullet Normal" 'put out a couple line breaks Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph Next i End With UpdateAll With ActiveDocument .PrintPreview .ClosePrintPreview End With Unload Me End Sub Private Sub cmdCancel_Click() If MsgBox("Are you sure you want to Cancel? You will lose all changes.", vbYesNo + vbQuestion) = vbYes Then Set frmCompanyCV = Nothing Unload Me End If End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then If MsgBox("Are you sure you want to Cancel? You will lose all changes.", vbYesNo + vbQuestion) = vbNo Then Cancel = True End If End If End Sub Private Sub cmdNext_Click() Dim i As Long i = MultiPage1.Value If i < MultiPage1.Pages.Count - 1 Then MultiPage1.Value = i + 1 End If End Sub Private Sub cmdPrevious_Click() Dim i As Long i = MultiPage1.Value If i > 0 Then MultiPage1.Value = i - 1 End If End Sub [/CODE] The following code is located in 4 separate modules: [CODE] Option Explicit Sub EducationBookmark(strBM As String, strText As String) Dim oRange As Word.Range Set oRange = ActiveDocument.Bookmarks(strBM).Range oRange.Text = strText With oRange .Collapse Direction:=wdCollapseStart .MoveEnd Unit:=wdCharacter, Count:=Len(strText) End With ActiveDocument.Bookmarks.Add strBM, Range:=oRange End Sub [/CODE] [CODE] Option Explicit Sub InterestBookmark(strBM As String, strText As String) Dim oRange As Word.Range Set oRange = ActiveDocument.Bookmarks(strBM).Range oRange.Text = strText With oRange .Collapse Direction:=wdCollapseStart .MoveEnd Unit:=wdCharacter, Count:=Len(strText) End With ActiveDocument.Bookmarks.Add strBM, Range:=oRange End Sub [/CODE] [CODE] Option Explicit Public MyList() As String [/CODE] [CODE] Option Explicit Sub UpdateAll() Dim sty As Range For Each sty In ActiveDocument.StoryRanges sty.Fields.Update Next sty End Sub [/CODE] in article OQSwugGtIHA.3968@TK2MSFTNGP04.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 13/5/08 5:41 AM: Show us ALL of the code that you have now. Sorry about posting all that code I thought that¹s what you were asking for. Everything works fine so thanks for that. Just one though: If this page isn¹t filled out I get an error: Run-time error 94: Invalid use of Null ...and the second instance of the following line in your code is highlighted: str2 = .List(i, 4) Do you know why this error occurs if and only if this page isn¹t filled out, and is there a workaround? I know most likely it will be filled out but you never know. > I did not need ALL of that. > [quoted text clipped - 24 lines] > Selection.Range.Style = "Bullet Normal" > End With Re: Transferring listbox values into documentTry If .List(i, 4) <> "" then str2 = .List(i, 4) Else str2 = " " End If 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 Sorry about posting all that code - I thought that's what you were asking for. Everything works fine so thanks for that. Just one though: If this page isn't filled out I get an error: Run-time error 94: Invalid use of Null ...and the second instance of the following line in your code is highlighted: str2 = .List(i, 4) Do you know why this error occurs if and only if this page isn't filled out, and is there a workaround? I know most likely it will be filled out but you never know. ------------------------------------------------------------------------------ in article #VYKtrLtIHA.5472@TK2MSFTNGP06.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 13/5/08 3:33 PM: I did not need ALL of that. With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 2 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) Selection.TypeText Text:=str1 Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph Next i i = .ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " - " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) Selection.TypeText Text:=str1 Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" End With Thanks that actually inserts a dash on the one line and an empty bullet on the next. Is there a way for it to insert nothing if nothing is entered but without the error? > Try > [quoted text clipped - 3 lines] > str2 = " " > End If Re: Transferring listbox values into documentIf you are going to try and develop macros, you need to think a bit about what is happening and how you can control it. If str2 <> " " Then Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" End If 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 Thanks - that actually inserts a dash on the one line and an empty bullet on the next. Is there a way for it to insert nothing if nothing is entered but without the error? in article #cQ0VATtIHA.1220@TK2MSFTNGP04.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 14/5/08 5:32 AM: Try If .List(i, 4) <> "" then str2 = .List(i, 4) Else str2 = " " End If Yeah you¹re right about needing to understand the code. To tell you the truth when you gave me the code that eliminates the empty paras once the last row of the listbox was inserted, I didn¹t understand the code that followed ³Next i² - I mean I understand the basics of looping etc. but I don¹t understand why you set this: i = .ListCount - 1 ...and str1 and str2 following it. Wish you could have commented the code. So basically if I don¹t know how that bit works I can¹t really troubleshoot the current problem which is that after revising the code (see below) by adding the snippet from your last post, on testing I still get the same error, ie. Run-time error 94: Invalid use of Null ...and again the last occurrence of the following line in your code is highlighted: str2 = .List(i, 4) I know it¹s annoying working with a beginner but can you help me to understand it so I can solve it? With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 2 str1 = .List(i, 0) & " " & .List(i, 1) & " " & Chr(150) & " " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) Selection.TypeText Text:=str1 Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph Next i i = .ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " " & Chr(150) & " " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) If str2 <> " " Then Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" End If Selection.TypeText Text:=str1 Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" End With > If you are going to try and develop macros, you need to think a bit about what > is happening and how you can control it. [quoted text clipped - 4 lines] > Selection.Range.Style = "Bullet Normal" > End If Re: Transferring listbox values into documentIf you want commented code, you will have to pay for it. How about using F1 after something like .ListCount and you will learn why it is -1 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 Yeah you're right about needing to understand the code. To tell you the truth when you gave me the code that eliminates the empty paras once the last row of the listbox was inserted, I didn't understand the code that followed "Next i" - I mean I understand the basics of looping etc. but I don't understand why you set this: i = .ListCount - 1 ...and str1 and str2 following it. Wish you could have commented the code. So basically if I don't know how that bit works I can't really troubleshoot the current problem which is that after revising the code (see below) by adding the snippet from your last post, on testing I still get the same error, ie. Run-time error 94: Invalid use of Null ...and again the last occurrence of the following line in your code is highlighted: str2 = .List(i, 4) I know it's annoying working with a beginner but can you help me to understand it so I can solve it? With lstMajor_Projects For i = 0 To lstMajor_Projects.ListCount - 2 str1 = .List(i, 0) & " " & .List(i, 1) & " " & Chr(150) & " " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) Selection.TypeText Text:=str1 Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeParagraph Next i i = .ListCount - 1 str1 = .List(i, 0) & " " & .List(i, 1) & " " & Chr(150) & " " & .List(i, 2) & " " & .List(i, 3) str2 = .List(i, 4) If str2 <> " " Then Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" End If Selection.TypeText Text:=str1 Selection.Range.Style = "Heading 2" Selection.TypeParagraph Selection.Range.Style = "Normal" Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" End With ------------------------------------------------------------------------------ in article #2tVC8YtIHA.4260@TK2MSFTNGP05.phx.gbl, Doug Robbins - Word MVP at dkr@REMOVECAPSmvps.org wrote on 14/5/08 4:51 PM: If you are going to try and develop macros, you need to think a bit about what is happening and how you can control it. If str2 <> " " Then Selection.TypeText Text:=str2 Selection.Range.Style = "Bullet Normal" End If On 12/05/2008 04:30, in article C44DF6DF.4A56%officelink@iinet.net.au, > Thanks, yeah that did the trick everything¹s populating really well. Just > one thing though I¹m putting in paragraphs after each s tring pair but I [quoted text clipped - 40 lines] > Next i > End With Hi Doug, I'm trying to use the code you posted above to enter selections from a listbox into a once cell table, adding each selection into a new tabe row. My listbox is two columns by 44 rows. Here is the code I'm using to populate the listbox: With lst01 .ColumnCount = 2 .ColumnWidths = "25,100" End With For i = 0 To UBound(myArray1) lst01.AddItem lst01.List(i, 0) = myArray1(i) lst01.List(i, 1) = myArray2(i) Next i And this is your code, amended and commented on, so far: Private Sub btn01_Click() Dim i As Long Dim str1 As String 'Dim str2 As String Dim atable As Table Dim bProtected As Boolean Application.ScreenUpdating = False If ActiveDocument.ProtectionType <> wdNoProtection Then bProtected = True ActiveDocument.Unprotect Password:="" End If Set atable = ActiveDocument.Tables(4) 'Index number of the table in the document With lst01 For i = 0 To .ListCount - 1 str1 = "" 'str2 = "" ' I Commented out other columns, since the listbox is only 2 columns str1 = .List(i, 0) & " " & .List(i, 1) 'not sure if the second string is relevant to my solution 'str2 = .List(i, 5) With atable .Rows.Add .Rows(Rows.Count).Cells(1).Range.Text = str1 .Rows(Rows.Count).Cells(1).Range.Style = "style for this data" 'not sure if the second string is relevant to my solution '.Rows.Add '.Rows(Rows.Count).Cells(1).Range.Text = str2 '.Rows(Rows.Count).Cells(1).Range.Style = "style for this data" End With Next i End With 'ActiveDocument.Protect wdAllowOnlyFormFields, True 'Reprotect the document. If bProtected = True Then ActiveDocument.Protect _ Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="" End If Application.ScreenUpdating = True End Sub Note that I get an error at .Rows(Rows.Count).Cells(1).Range.Text = str1 Where the second "Rows" in .Rows(Rows.Count).... is highlighted and I get a compile error - variable not defined. I'm not too sure, does str1 and str2 allow multiple selections within the list to be added to new rows in the table? This is what I want to be able to to. If yes, will I add more strs up to the maximum selection (44 rows). hope you can help! Kind regards Dylan Dawson Doug/Officelink It's okay, I got my answer using If lst01.Selected(i) Then... to determine if the item in the listbox is selected and... Using... .Rows.Last.Cells(1).Range.Text = str1 rather than... .Rows(Rows.Count) Thanks anyway! Dylan |
Enable EMail Alerts
Start New Thread
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homep |