Word UserForm ScreenUpdating Slow jkbourland[ at ]att.net 12.07.2006 21:30:28 I am currently running a VBA UserForm in Word 2000 and 2003. In the userform initialize statement, the template pulls in data from a saved word document that is set up as a table. This table has 7 columns and 66 rows. This list the employees, phone...and other data to be pulled into the document. I recently copied the document that stores employee data and renamed it. I then updated the table by modifying text in two of the columns. The userform works fine on 74 of 75 computers. On one computer it takes 20 seconds (which seems like an hour) for the userform to display and you see it opening the word document behind the sceens...looks like it is going to hang but then it shows the form and works fine. If I change my code to use the original document it is fast again. I don't understand why only one computer is having issues. Things I've tried: Uninstalled and reinstalled MS Word 2003 - installed all updates Word Document - Table: Stripped the table of formatting and recreated table in new doc - just incase there was some code causing the problems. ScreenUpdating = False I added this in the initialize stmt....did nothing. (then removed) I added = True before the form is loaded then = False before it runs the code that opens the Word table. (didn't work) I put the Initialize code below. Thank you in advance for you time. Kerri CODE: go down to '========================== Private Sub UserForm_Initialize() Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range, m As Long, n As Long Application.ScreenUpdating = True 'This populates the combo box for Office Location cmbOfficeLocation.AddItem "Phoenix" cmbOfficeLocation.AddItem "Scottsdale" 'This populates the combo box for Letter type. cmbLtrType.AddItem "Individual" cmbLtrType.AddItem "Firm" 'This populates the combo box for Delivery Instructions cmbDeliveryInstructions.AddItem "ATTORNEY/CLIENT PRIVILEGED" cmbDeliveryInstructions.AddItem "PERSONAL AND CONFIDENTIAL" cmbDeliveryInstructions.AddItem "VIA FACSIMILE (xxx) xxx-xxxx" 'This Populates the combo box for Closing cmbClosing.AddItem "Very truly yours," cmbClosing.AddItem "Sincerely," cmbClosing.AddItem "Sincerely yours," cmbClosing.AddItem "Best regards," Application.ScreenUpdating = False '========================== 'Steps provided by Doug Robbins - Word MVP ' Open the file containing the Author details ' Modify the path in the following line so that it matches where you Saved Authors.doc Set sourcedoc = Documents.Open(FileName:="S:\JW Temp Authors\JWAuthor List.doc") ' Get the number of Authors = number of rows in the table of Author details less one, 'assuming that the first row is a header row i = sourcedoc.Tables(1).Rows.Count - 1 ' Get the number of columns in the table of Author details j = sourcedoc.Tables(1).Columns.Count ' Set the number of columns in the Combobox to match ' the number of columns in the table of Author details cmbAuthorsInitials.ColumnCount = j ' Define an array to be loaded with the Author 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 cmbAuthoursInitials cmbAuthorsInitials.List() = MyArray ' Close the file containing the client details sourcedoc.Close SaveChanges:=wdDoNotSaveChanges '========================== 'Inserts Current Date txtDate.Text = Format$(Date$, "mmmm d, yyyy") End Sub

Re: Word UserForm ScreenUpdating Slow "Jonathan West" <jwest[ at ]mvps.org> 13.07.2006 11:21:23 Hi Kerri There seems to be some problem concerning the combination of that computer and that document. There is nothing obvious in your code that would cause this. Other things you might look at - check the network connection for that computer - try replacing normal.dot on the computer However, as a longer-term solution, you might like to look at formats other than Word documents for the information you want, since Word documents are comparatively slow to open. Any of the following would probably enable the data to be loaded much faster on all PCs. 1. Plain text files, comma separated perhaps, opened using the Open command and then read using the Input or Line Input command 2. An Excel spreadsheet. The following two articles will help you Load a ListBox from a Named Range in Excel using DAO http://www.word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm Retrieving Data from a Named Range in Excel using DAO http://www.word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm 3. An INI file. Use the code on Karl Peterson's website to retrieve values from the INI file http://vb.mvps.org/samples/project.asp?id=kpIni 4. An XML file. Set a reference to a version of the MSXML library in Tools References to access the XML object model to read the file. -- Regards Jonathan West - Word MVP www.intelligentdocuments.co.uk Please reply to the newsgroup Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org <jkbourland[ at ]att.net> wrote in message news:1152739828.454190.314820[ at ]m79g2000cwm.googlegroups.com... [Quoted Text] >I am currently running a VBA UserForm in Word 2000 and 2003. > > In the userform initialize statement, the template pulls in data from a > saved word document that is set up as a table. This table has 7 > columns and 66 rows. This list the employees, phone...and other data > to be pulled into the document. I recently copied the document that > stores employee data and renamed it. I then updated the table by > modifying text in two of the columns. > > The userform works fine on 74 of 75 computers. On one computer it > takes 20 seconds (which seems like an hour) for the userform to display > and you see it opening the word document behind the sceens...looks like > it is going to hang but then it shows the form and works fine. If I > change my code to use the original document it is fast again. I > don't understand why only one computer is having issues. > > > Things I've tried: > Uninstalled and reinstalled MS Word 2003 - installed all updates > > Word Document - Table: > Stripped the table of formatting and recreated table in new doc - > just incase there was some code causing the problems. > > > ScreenUpdating = False > I added this in the initialize stmt....did nothing. (then removed) > I added = True before the form is loaded then = False before it runs > the code that opens the Word table. (didn't work) > > I put the Initialize code below. Thank you in advance for you time. > Kerri > > > CODE: go down to '========================== > > Private Sub UserForm_Initialize() > Dim sourcedoc As Document, i As Integer, j As Integer, myitem As > Range, m As Long, n As Long > Application.ScreenUpdating = True > 'This populates the combo box for Office Location > cmbOfficeLocation.AddItem "Phoenix" > cmbOfficeLocation.AddItem "Scottsdale" > 'This populates the combo box for Letter type. > cmbLtrType.AddItem "Individual" > cmbLtrType.AddItem "Firm" > 'This populates the combo box for Delivery Instructions > cmbDeliveryInstructions.AddItem "ATTORNEY/CLIENT PRIVILEGED" > cmbDeliveryInstructions.AddItem "PERSONAL AND CONFIDENTIAL" > cmbDeliveryInstructions.AddItem "VIA FACSIMILE (xxx) xxx-xxxx" > 'This Populates the combo box for Closing > cmbClosing.AddItem "Very truly yours," > cmbClosing.AddItem "Sincerely," > cmbClosing.AddItem "Sincerely yours," > cmbClosing.AddItem "Best regards," > > Application.ScreenUpdating = False > > '========================== > 'Steps provided by Doug Robbins - Word MVP > ' Open the file containing the Author details > ' Modify the path in the following line so that it matches where > you Saved Authors.doc > Set sourcedoc = Documents.Open(FileName:="S:\JW Temp > Authors\JWAuthor List.doc") > > ' Get the number of Authors = number of rows in the table of Author > details less one, > 'assuming that the first row is a header row > i = sourcedoc.Tables(1).Rows.Count - 1 > > ' Get the number of columns in the table of Author details > j = sourcedoc.Tables(1).Columns.Count > > ' Set the number of columns in the Combobox to match > ' the number of columns in the table of Author details > cmbAuthorsInitials.ColumnCount = j > > ' Define an array to be loaded with the Author 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 cmbAuthoursInitials > cmbAuthorsInitials.List() = MyArray > > ' Close the file containing the client details > sourcedoc.Close SaveChanges:=wdDoNotSaveChanges > '========================== > > 'Inserts Current Date > txtDate.Text = Format$(Date$, "mmmm d, yyyy") > End Sub >

Re: Word UserForm ScreenUpdating Slow jkbourland[ at ]att.net 13.07.2006 17:32:51 Hi Jonathan, Thank you for replying. I tried replacing the Normal.dot first, unfortunately that didn't work either. I'll check the network connections today and see if that will resolve it. If not, I'll try the Excel DAO options you listed. Hopefully I can figure out how to integrate them properly. If not, you'll probably see another post from me. =) Thank you so much for taking the time to respond and for getting me the resources I need to try something new. Thanks again! Kerri Jonathan West wrote: [Quoted Text] > Hi Kerri > > There seems to be some problem concerning the combination of that computer > and that document. There is nothing obvious in your code that would cause > this. Other things you might look at > > - check the network connection for that computer > - try replacing normal.dot on the computer > > However, as a longer-term solution, you might like to look at formats other > than Word documents for the information you want, since Word documents are > comparatively slow to open. Any of the following would probably enable the > data to be loaded much faster on all PCs. > > 1. Plain text files, comma separated perhaps, opened using the Open command > and then read using the Input or Line Input command > > 2. An Excel spreadsheet. The following two articles will help you > > Load a ListBox from a Named Range in Excel using DAO > http://www.word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm > > Retrieving Data from a Named Range in Excel using DAO > http://www.word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm > > 3. An INI file. Use the code on Karl Peterson's website to retrieve values > from the INI file > http://vb.mvps.org/samples/project.asp?id=kpIni > > 4. An XML file. Set a reference to a version of the MSXML library in Tools > References to access the XML object model to read the file. > > > -- > Regards > Jonathan West - Word MVP > www.intelligentdocuments.co.uk > Please reply to the newsgroup > Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org > > <jkbourland[ at ]att.net> wrote in message > news:1152739828.454190.314820[ at ]m79g2000cwm.googlegroups.com... > >I am currently running a VBA UserForm in Word 2000 and 2003. > > > > In the userform initialize statement, the template pulls in data from a > > saved word document that is set up as a table. This table has 7 > > columns and 66 rows. This list the employees, phone...and other data > > to be pulled into the document. I recently copied the document that > > stores employee data and renamed it. I then updated the table by > > modifying text in two of the columns. > > > > The userform works fine on 74 of 75 computers. On one computer it > > takes 20 seconds (which seems like an hour) for the userform to display > > and you see it opening the word document behind the sceens...looks like > > it is going to hang but then it shows the form and works fine. If I > > change my code to use the original document it is fast again. I > > don't understand why only one computer is having issues. > > > > > > Things I've tried: > > Uninstalled and reinstalled MS Word 2003 - installed all updates > > > > Word Document - Table: > > Stripped the table of formatting and recreated table in new doc - > > just incase there was some code causing the problems. > > > > > > ScreenUpdating = False > > I added this in the initialize stmt....did nothing. (then removed) > > I added = True before the form is loaded then = False before it runs > > the code that opens the Word table. (didn't work) > > > > I put the Initialize code below. Thank you in advance for you time. > > Kerri > > > > > > CODE: go down to '========================== > > > > Private Sub UserForm_Initialize() > > Dim sourcedoc As Document, i As Integer, j As Integer, myitem As > > Range, m As Long, n As Long > > Application.ScreenUpdating = True > > 'This populates the combo box for Office Location > > cmbOfficeLocation.AddItem "Phoenix" > > cmbOfficeLocation.AddItem "Scottsdale" > > 'This populates the combo box for Letter type. > > cmbLtrType.AddItem "Individual" > > cmbLtrType.AddItem "Firm" > > 'This populates the combo box for Delivery Instructions > > cmbDeliveryInstructions.AddItem "ATTORNEY/CLIENT PRIVILEGED" > > cmbDeliveryInstructions.AddItem "PERSONAL AND CONFIDENTIAL" > > cmbDeliveryInstructions.AddItem "VIA FACSIMILE (xxx) xxx-xxxx" > > 'This Populates the combo box for Closing > > cmbClosing.AddItem "Very truly yours," > > cmbClosing.AddItem "Sincerely," > > cmbClosing.AddItem "Sincerely yours," > > cmbClosing.AddItem "Best regards," > > > > Application.ScreenUpdating = False > > > > '========================== > > 'Steps provided by Doug Robbins - Word MVP > > ' Open the file containing the Author details > > ' Modify the path in the following line so that it matches where > > you Saved Authors.doc > > Set sourcedoc = Documents.Open(FileName:="S:\JW Temp > > Authors\JWAuthor List.doc") > > > > ' Get the number of Authors = number of rows in the table of Author > > details less one, > > 'assuming that the first row is a header row > > i = sourcedoc.Tables(1).Rows.Count - 1 > > > > ' Get the number of columns in the table of Author details > > j = sourcedoc.Tables(1).Columns.Count > > > > ' Set the number of columns in the Combobox to match > > ' the number of columns in the table of Author details > > cmbAuthorsInitials.ColumnCount = j > > > > ' Define an array to be loaded with the Author 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 cmbAuthoursInitials > > cmbAuthorsInitials.List() = MyArray > > > > ' Close the file containing the client details > > sourcedoc.Close SaveChanges:=wdDoNotSaveChanges > > '========================== > > > > 'Inserts Current Date > > txtDate.Text = Format$(Date$, "mmmm d, yyyy") > > End Sub > >

Re: Word UserForm ScreenUpdating Slow jkbourland[ at ]att.net 13.07.2006 21:05:34 Hi Jonathan, Okay, I started the DAO using the link you provided on the previous reply. But I am already getting an error. The first error was on the 'retrieve the recordset option. I had to remove the single quotes from the name of the named range. ('Authors'). That worked and then the second error is at the "ListBox1.ColumnCount = rs.Fields.Count". I get object required 424 error. I did add the Microsoft DAO 3.6 Object Library as well. Thanks for your help. Kerri 'I have this saved as a module and run it in the initalize statement. Sub CompleteAuthorsList() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long 'Open the database Set db = OpenDatabase("S:\JW Temp Authors\JWAuthor List.xls", False, False, "Excel 8.0") 'Retrieve the recordset Set rs = db.OpenRecordset("SELECT * From Authors") 'Determine the number of retrieved records With rs .movelast NoOfRecords = .RecordCount .MoveFirst End With '+++ Getting Object Required Error424 here. 'Set the number of columns = number of fields in the recordset 'cmbAuthorsInitials ' this is a combo box that I wanted to set it to but I created 'a ListBox to see if that was the reason it didn't work. ListBox1.ColumnCount = rs.Fields.Count 'Load the combobox with the retrieved records ListBox1 = rs.getrows(NoOfRecords) 'Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub jkbourland[ at ]att.net wrote: [Quoted Text] > Hi Jonathan, > > Thank you for replying. I tried replacing the Normal.dot first, > unfortunately that didn't work either. I'll check the network > connections today and see if that will resolve it. If not, I'll try > the Excel DAO options you listed. Hopefully I can figure out how to > integrate them properly. If not, you'll probably see another post from > me. =) > > Thank you so much for taking the time to respond and for getting me the > resources I need to try something new. > > Thanks again! > Kerri > > > > Jonathan West wrote: > > Hi Kerri > > > > There seems to be some problem concerning the combination of that computer > > and that document. There is nothing obvious in your code that would cause > > this. Other things you might look at > > > > - check the network connection for that computer > > - try replacing normal.dot on the computer > > > > However, as a longer-term solution, you might like to look at formats other > > than Word documents for the information you want, since Word documents are > > comparatively slow to open. Any of the following would probably enable the > > data to be loaded much faster on all PCs. > > > > 1. Plain text files, comma separated perhaps, opened using the Open command > > and then read using the Input or Line Input command > > > > 2. An Excel spreadsheet. The following two articles will help you > > > > Load a ListBox from a Named Range in Excel using DAO > > http://www.word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm > > > > Retrieving Data from a Named Range in Excel using DAO > > http://www.word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm > > > > 3. An INI file. Use the code on Karl Peterson's website to retrieve values > > from the INI file > > http://vb.mvps.org/samples/project.asp?id=kpIni > > > > 4. An XML file. Set a reference to a version of the MSXML library in Tools > > References to access the XML object model to read the file. > > > > > > -- > > Regards > > Jonathan West - Word MVP > > www.intelligentdocuments.co.uk > > Please reply to the newsgroup > > Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org > > > > <jkbourland[ at ]att.net> wrote in message > > news:1152739828.454190.314820[ at ]m79g2000cwm.googlegroups.com... > > >I am currently running a VBA UserForm in Word 2000 and 2003. > > > > > > In the userform initialize statement, the template pulls in data from a > > > saved word document that is set up as a table. This table has 7 > > > columns and 66 rows. This list the employees, phone...and other data > > > to be pulled into the document. I recently copied the document that > > > stores employee data and renamed it. I then updated the table by > > > modifying text in two of the columns. > > > > > > The userform works fine on 74 of 75 computers. On one computer it > > > takes 20 seconds (which seems like an hour) for the userform to display > > > and you see it opening the word document behind the sceens...looks like > > > it is going to hang but then it shows the form and works fine. If I > > > change my code to use the original document it is fast again. I > > > don't understand why only one computer is having issues. > > > > > > > > > Things I've tried: > > > Uninstalled and reinstalled MS Word 2003 - installed all updates > > > > > > Word Document - Table: > > > Stripped the table of formatting and recreated table in new doc - > > > just incase there was some code causing the problems. > > > > > > > > > ScreenUpdating = False > > > I added this in the initialize stmt....did nothing. (then removed) > > > I added = True before the form is loaded then = False before it runs > > > the code that opens the Word table. (didn't work) > > > > > > I put the Initialize code below. Thank you in advance for you time. > > > Kerri > > > > > > > > > CODE: go down to '========================== > > > > > > Private Sub UserForm_Initialize() > > > Dim sourcedoc As Document, i As Integer, j As Integer, myitem As > > > Range, m As Long, n As Long > > > Application.ScreenUpdating = True > > > 'This populates the combo box for Office Location > > > cmbOfficeLocation.AddItem "Phoenix" > > > cmbOfficeLocation.AddItem "Scottsdale" > > > 'This populates the combo box for Letter type. > > > cmbLtrType.AddItem "Individual" > > > cmbLtrType.AddItem "Firm" > > > 'This populates the combo box for Delivery Instructions > > > cmbDeliveryInstructions.AddItem "ATTORNEY/CLIENT PRIVILEGED" > > > cmbDeliveryInstructions.AddItem "PERSONAL AND CONFIDENTIAL" > > > cmbDeliveryInstructions.AddItem "VIA FACSIMILE (xxx) xxx-xxxx" > > > 'This Populates the combo box for Closing > > > cmbClosing.AddItem "Very truly yours," > > > cmbClosing.AddItem "Sincerely," > > > cmbClosing.AddItem "Sincerely yours," > > > cmbClosing.AddItem "Best regards," > > > > > > Application.ScreenUpdating = False > > > > > > '========================== > > > 'Steps provided by Doug Robbins - Word MVP > > > ' Open the file containing the Author details > > > ' Modify the path in the following line so that it matches where > > > you Saved Authors.doc > > > Set sourcedoc = Documents.Open(FileName:="S:\JW Temp > > > Authors\JWAuthor List.doc") > > > > > > ' Get the number of Authors = number of rows in the table of Author > > > details less one, > > > 'assuming that the first row is a header row > > > i = sourcedoc.Tables(1).Rows.Count - 1 > > > > > > ' Get the number of columns in the table of Author details > > > j = sourcedoc.Tables(1).Columns.Count > > > > > > ' Set the number of columns in the Combobox to match > > > ' the number of columns in the table of Author details > > > cmbAuthorsInitials.ColumnCount = j > > > > > > ' Define an array to be loaded with the Author 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 cmbAuthoursInitials > > > cmbAuthorsInitials.List() = MyArray > > > > > > ' Close the file containing the client details > > > sourcedoc.Close SaveChanges:=wdDoNotSaveChanges > > > '========================== > > > > > > 'Inserts Current Date > > > txtDate.Text = Format$(Date$, "mmmm d, yyyy") > > > End Sub > > >

Re: Word UserForm ScreenUpdating Slow "Jonathan West" <jwest[ at ]mvps.org> 13.07.2006 21:12:32 <jkbourland[ at ]att.net> wrote in message news:1152824734.558493.316120[ at ]p79g2000cwp.googlegroups.com... [Quoted Text] > Hi Jonathan, > > Okay, I started the DAO using the link you provided on the previous > reply. But I am already getting an error. The first error was on the > 'retrieve the recordset option. I had to remove the single quotes from > the name of the named range. ('Authors'). That worked and then the > second error is at the "ListBox1.ColumnCount = rs.Fields.Count". I get > object required 424 error. I did add the Microsoft DAO 3.6 Object > Library as well. > > Thanks for your help. > Kerri > > 'I have this saved as a module and run it in the initalize statement. > Sub CompleteAuthorsList() > Dim db As DAO.Database > Dim rs As DAO.Recordset > Dim NoOfRecords As Long > > 'Open the database > Set db = OpenDatabase("S:\JW Temp Authors\JWAuthor List.xls", False, > False, "Excel 8.0") > > 'Retrieve the recordset > Set rs = db.OpenRecordset("SELECT * From Authors") > > 'Determine the number of retrieved records > With rs > .movelast > NoOfRecords = .RecordCount > .MoveFirst > End With > > > '+++ Getting Object Required Error424 here. > 'Set the number of columns = number of fields in the recordset > 'cmbAuthorsInitials ' this is a combo box that I wanted to set it to > but I created > 'a ListBox to see if that was the reason it didn't work. > ListBox1.ColumnCount = rs.Fields.Count Well, there are 3 objects there. ListBox1, the rs Recordset and the Fields collection. Check each of them in turn and see if they are currently valid. -- Regards Jonathan West - Word MVP www.intelligentdocuments.co.uk Please reply to the newsgroup Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Re: Word UserForm ScreenUpdating Slow jkbourland[ at ]att.net 13.07.2006 22:10:10 I might need some direction on how to check if they are valid. I right clicked on the objects and added a "Watch" to rs, ListBox1 and Fields object then stepped through the macro. Listbox1's value was <empty>, because it erred before it could give it a value (right?). I also know that if you press (Ctrl + J) it will list the "Properties and Methods" that can be used with that object....and I get nothing after Listbox1. So that makes me guess that it doesn't like that combination? If I removed the UserForm Field Name like "ListBox1" it would not give me an error. But it also didn't populate my list box with the records from the excel documents named range. ColumnCount = rs.Fields.Count Do you know if you have to set up an ODBC connection through Control Panel | Administrative Tools | Data Sources (ODBC) in order for this to work? Sorry, my experience is self taught through the newsgroups. Thanks for your help. Kerri Jonathan West wrote: [Quoted Text] > <jkbourland[ at ]att.net> wrote in message > news:1152824734.558493.316120[ at ]p79g2000cwp.googlegroups.com... > > Hi Jonathan, > > > > Okay, I started the DAO using the link you provided on the previous > > reply. But I am already getting an error. The first error was on the > > 'retrieve the recordset option. I had to remove the single quotes from > > the name of the named range. ('Authors'). That worked and then the > > second error is at the "ListBox1.ColumnCount = rs.Fields.Count". I get > > object required 424 error. I did add the Microsoft DAO 3.6 Object > > Library as well. > > > > Thanks for your help. > > Kerri > > > > 'I have this saved as a module and run it in the initalize statement. > > Sub CompleteAuthorsList() > > Dim db As DAO.Database > > Dim rs As DAO.Recordset > > Dim NoOfRecords As Long > > > > 'Open the database > > Set db = OpenDatabase("S:\JW Temp Authors\JWAuthor List.xls", False, > > False, "Excel 8.0") > > > > 'Retrieve the recordset > > Set rs = db.OpenRecordset("SELECT * From Authors") > > > > 'Determine the number of retrieved records > > With rs > > .movelast > > NoOfRecords = .RecordCount > > .MoveFirst > > End With > > > > > > '+++ Getting Object Required Error424 here. > > 'Set the number of columns = number of fields in the recordset > > 'cmbAuthorsInitials ' this is a combo box that I wanted to set it to > > but I created > > 'a ListBox to see if that was the reason it didn't work. > > ListBox1.ColumnCount = rs.Fields.Count > > Well, there are 3 objects there. ListBox1, the rs Recordset and the Fields > collection. Check each of them in turn and see if they are currently valid. > > > -- > Regards > Jonathan West - Word MVP > www.intelligentdocuments.co.uk > Please reply to the newsgroup > Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Re: Word UserForm ScreenUpdating Slow "Jonathan West" <jwest[ at ]mvps.org> 14.07.2006 09:41:28 Actually, it looks like you have done a simple typo compared to the article. You have this ListBox1 = rs.getrows(NoOfRecords) but the article has this ListBox1.Column = rs.GetRows(NoOfRecords) -- Regards Jonathan West - Word MVP www.intelligentdocuments.co.uk Please reply to the newsgroup Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org <jkbourland[ at ]att.net> wrote in message news:1152828610.269558.17370[ at ]i42g2000cwa.googlegroups.com... [Quoted Text] >I might need some direction on how to check if they are valid. > > I right clicked on the objects and added a "Watch" to rs, ListBox1 and > Fields object then stepped through the macro. Listbox1's value was > <empty>, because it erred before it could give it a value (right?). I > also know that if you press (Ctrl + J) it will list the "Properties and > Methods" that can be used with that object....and I get nothing after > Listbox1. So that makes me guess that it doesn't like that > combination? > > If I removed the UserForm Field Name like "ListBox1" it would not give > me an error. But it also didn't populate my list box with the records > from the excel documents named range. > > ColumnCount = rs.Fields.Count > > Do you know if you have to set up an ODBC connection through Control > Panel | Administrative Tools | Data Sources (ODBC) in order for this to > work? > > Sorry, my experience is self taught through the newsgroups. Thanks for > your help. > Kerri > > > Jonathan West wrote: >> <jkbourland[ at ]att.net> wrote in message >> news:1152824734.558493.316120[ at ]p79g2000cwp.googlegroups.com... >> > Hi Jonathan, >> > >> > Okay, I started the DAO using the link you provided on the previous >> > reply. But I am already getting an error. The first error was on the >> > 'retrieve the recordset option. I had to remove the single quotes from >> > the name of the named range. ('Authors'). That worked and then the >> > second error is at the "ListBox1.ColumnCount = rs.Fields.Count". I get >> > object required 424 error. I did add the Microsoft DAO 3.6 Object >> > Library as well. >> > >> > Thanks for your help. >> > Kerri >> > >> > 'I have this saved as a module and run it in the initalize statement. >> > Sub CompleteAuthorsList() >> > Dim db As DAO.Database >> > Dim rs As DAO.Recordset >> > Dim NoOfRecords As Long >> > >> > 'Open the database >> > Set db = OpenDatabase("S:\JW Temp Authors\JWAuthor List.xls", False, >> > False, "Excel 8.0") >> > >> > 'Retrieve the recordset >> > Set rs = db.OpenRecordset("SELECT * From Authors") >> > >> > 'Determine the number of retrieved records >> > With rs >> > .movelast >> > NoOfRecords = .RecordCount >> > .MoveFirst >> > End With >> > >> > >> > '+++ Getting Object Required Error424 here. >> > 'Set the number of columns = number of fields in the recordset >> > 'cmbAuthorsInitials ' this is a combo box that I wanted to set it to >> > but I created >> > 'a ListBox to see if that was the reason it didn't work. >> > ListBox1.ColumnCount = rs.Fields.Count >> >> Well, there are 3 objects there. ListBox1, the rs Recordset and the >> Fields >> collection. Check each of them in turn and see if they are currently >> valid. >> >> >> -- >> Regards >> Jonathan West - Word MVP >> www.intelligentdocuments.co.uk >> Please reply to the newsgroup >> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org >

Re: Word UserForm ScreenUpdating Slow jkbourland[ at ]att.net 20.07.2006 21:42:21 Hi Jonathan , After your last reply I decided to copy and paste the text in, instead of typing the text, and it worked great (it's super fast). There must have been another typo somewhere. I put the code below for anyone following the thread. I do have one more question. Before when I was pulling the data in from the word table I could write the following code and I would not get an error. Now it doesn't recognize the quotes ("") to indicate Null or Empty field. If I try to use Null or Empty I get an error also...any suggestions? It goes right to "Else" instead of running the code if the statement is true. If you point to cmbAuthorsInitials.Value it says = Null. Thank you again for your time and help. -Kerri '------New problem----- 'Insert Title under authorsnamae at top. cmbAuthorsInitials.BoundColumn = 7 'Title at top If cmbAuthorsInitials.Value = "" Then ActiveDocument.CustomDocumentProperties("AuthorTitle").Value = " " ' UpdateBookmarkText .Bookmarks("JwBk3"), " " 'space to clear text 'JWAuthorTitle2 Style apply formatting after to 0 ActiveDocument.Styles("JWAuthorTitle2").ParagraphFormat.SpaceAfter = 0 Else: ActiveDocument.CustomDocumentProperties("AuthorTitle").Value = cmbAuthorsInitials.Value StylesTitle 'Module called JWLtrStyles 'Runs a macro that changes the style formatting 'Apply spacing after if ther is a title End If '-------------------- '========================= Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("S:\JW Temp Authors\JWAuthor List.xls", False, False, "Excel 8.0") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM `Authors`") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset cmbAuthorsInitials.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records cmbAuthorsInitials.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub '========= Jonathan West wrote: [Quoted Text] > Actually, it looks like you have done a simple typo compared to the article. > You have this > > ListBox1 = rs.getrows(NoOfRecords) > > but the article has this > > ListBox1.Column = rs.GetRows(NoOfRecords) > > > -- > Regards > Jonathan West - Word MVP > www.intelligentdocuments.co.uk > Please reply to the newsgroup > Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org > > > <jkbourland[ at ]att.net> wrote in message > news:1152828610.269558.17370[ at ]i42g2000cwa.googlegroups.com... > >I might need some direction on how to check if they are valid. > > > > I right clicked on the objects and added a "Watch" to rs, ListBox1 and > > Fields object then stepped through the macro. Listbox1's value was > > <empty>, because it erred before it could give it a value (right?). I > > also know that if you press (Ctrl + J) it will list the "Properties and > > Methods" that can be used with that object....and I get nothing after > > Listbox1. So that makes me guess that it doesn't like that > > combination? > > > > If I removed the UserForm Field Name like "ListBox1" it would not give > > me an error. But it also didn't populate my list box with the records > > from the excel documents named range. > > > > ColumnCount = rs.Fields.Count > > > > Do you know if you have to set up an ODBC connection through Control > > Panel | Administrative Tools | Data Sources (ODBC) in order for this to > > work? > > > > Sorry, my experience is self taught through the newsgroups. Thanks for > > your help. > > Kerri > > > > > > Jonathan West wrote: > >> <jkbourland[ at ]att.net> wrote in message > >> news:1152824734.558493.316120[ at ]p79g2000cwp.googlegroups.com... > >> > Hi Jonathan, > >> > > >> > Okay, I started the DAO using the link you provided on the previous > >> > reply. But I am already getting an error. The first error was on the > >> > 'retrieve the recordset option. I had to remove the single quotes from > >> > the name of the named range. ('Authors'). That worked and then the > >> > second error is at the "ListBox1.ColumnCount = rs.Fields.Count". I get > >> > object required 424 error. I did add the Microsoft DAO 3.6 Object > >> > Library as well. > >> > > >> > Thanks for your help. > >> > Kerri > >> > > >> > 'I have this saved as a module and run it in the initalize statement. > >> > Sub CompleteAuthorsList() > >> > Dim db As DAO.Database > >> > Dim rs As DAO.Recordset > >> > Dim NoOfRecords As Long > >> > > >> > 'Open the database > >> > Set db = OpenDatabase("S:\JW Temp Authors\JWAuthor List.xls", False, > >> > False, "Excel 8.0") > >> > > >> > 'Retrieve the recordset > >> > Set rs = db.OpenRecordset("SELECT * From Authors") > >> > > >> > 'Determine the number of retrieved records > >> > With rs > >> > .movelast > >> > NoOfRecords = .RecordCount > >> > .MoveFirst > >> > End With > >> > > >> > > >> > '+++ Getting Object Required Error424 here. > >> > 'Set the number of columns = number of fields in the recordset > >> > 'cmbAuthorsInitials ' this is a combo box that I wanted to set it to > >> > but I created > >> > 'a ListBox to see if that was the reason it didn't work. > >> > ListBox1.ColumnCount = rs.Fields.Count > >> > >> Well, there are 3 objects there. ListBox1, the rs Recordset and the > >> Fields > >> collection. Check each of them in turn and see if they are currently > >> valid. > >> > >> > >> -- > >> Regards > >> Jonathan West - Word MVP > >> www.intelligentdocuments.co.uk > >> Please reply to the newsgroup > >> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org > >

Re: Word UserForm ScreenUpdating Slow jkbourland[ at ]att.net 20.07.2006 21:42:24 Hi Jonathan , After your last reply I decided to copy and paste the text in, instead of typing the text, and it worked great (it's super fast). There must have been another typo somewhere. I put the code below for anyone following the thread. I do have one more question. Before when I was pulling the data in from the word table I could write the following code and I would not get an error. Now it doesn't recognize the quotes ("") to indicate Null or Empty field. If I try to use Null or Empty I get an error also...any suggestions? It goes right to "Else" instead of running the code if the statement is true. If you point to cmbAuthorsInitials.Value it says = Null. Thank you again for your time and help. -Kerri '------New problem----- 'Insert Title under authorsnamae at top. cmbAuthorsInitials.BoundColumn = 7 'Title at top If cmbAuthorsInitials.Value = "" Then ActiveDocument.CustomDocumentProperties("AuthorTitle").Value = " " ' UpdateBookmarkText .Bookmarks("JwBk3"), " " 'space to clear text 'JWAuthorTitle2 Style apply formatting after to 0 ActiveDocument.Styles("JWAuthorTitle2").ParagraphFormat.SpaceAfter = 0 Else: ActiveDocument.CustomDocumentProperties("AuthorTitle").Value = cmbAuthorsInitials.Value StylesTitle 'Module called JWLtrStyles 'Runs a macro that changes the style formatting 'Apply spacing after if ther is a title End If '-------------------- '========================= Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = OpenDatabase("S:\JW Temp Authors\JWAuthor List.xls", False, False, "Excel 8.0") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT * FROM `Authors`") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset cmbAuthorsInitials.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records cmbAuthorsInitials.Column = rs.GetRows(NoOfRecords) ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub '========= Jonathan West wrote: [Quoted Text] > Actually, it looks like you have done a simple typo compared to the article. > You have this > > ListBox1 = rs.getrows(NoOfRecords) > > but the article has this > > ListBox1.Column = rs.GetRows(NoOfRecords) > > > -- > Regards > Jonathan West - Word MVP > www.intelligentdocuments.co.uk > Please reply to the newsgroup > Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org > > > <jkbourland[ at ]att.net> wrote in message > news:1152828610.269558.17370[ at ]i42g2000cwa.googlegroups.com... > >I might need some direction on how to check if they are valid. > > > > I right clicked on the objects and added a "Watch" to rs, ListBox1 and > > Fields object then stepped through the macro. Listbox1's value was > > <empty>, because it erred before it could give it a value (right?). I > > also know that if you press (Ctrl + J) it will list the "Properties and > > Methods" that can be used with that object....and I get nothing after > > Listbox1. So that makes me guess that it doesn't like that > > combination? > > > > If I removed the UserForm Field Name like "ListBox1" it would not give > > me an error. But it also didn't populate my list box with the records > > from the excel documents named range. > > > > ColumnCount = rs.Fields.Count > > > > Do you know if you have to set up an ODBC connection through Control > > Panel | Administrative Tools | Data Sources (ODBC) in order for this to > > work? > > > > Sorry, my experience is self taught through the newsgroups. Thanks for > > your help. > > Kerri > > > > > > Jonathan West wrote: > >> <jkbourland[ at ]att.net> wrote in message > >> news:1152824734.558493.316120[ at ]p79g2000cwp.googlegroups.com... > >> > Hi Jonathan, > >> > > >> > Okay, I started the DAO using the link you provided on the previous > >> > reply. But I am already getting an error. The first error was on the > >> > 'retrieve the recordset option. I had to remove the single quotes from > >> > the name of the named range. ('Authors'). That worked and then the > >> > second error is at the "ListBox1.ColumnCount = rs.Fields.Count". I get > >> > object required 424 error. I did add the Microsoft DAO 3.6 Object > >> > Library as well. > >> > > >> > Thanks for your help. > >> > Kerri > >> > > >> > 'I have this saved as a module and run it in the initalize statement. > >> > Sub CompleteAuthorsList() > >> > Dim db As DAO.Database > >> > Dim rs As DAO.Recordset > >> > Dim NoOfRecords As Long > >> > > >> > 'Open the database > >> > Set db = OpenDatabase("S:\JW Temp Authors\JWAuthor List.xls", False, > >> > False, "Excel 8.0") > >> > > >> > 'Retrieve the recordset > >> > Set rs = db.OpenRecordset("SELECT * From Authors") > >> > > >> > 'Determine the number of retrieved records > >> > With rs > >> > .movelast > >> > NoOfRecords = .RecordCount > >> > .MoveFirst > >> > End With > >> > > >> > > >> > '+++ Getting Object Required Error424 here. > >> > 'Set the number of columns = number of fields in the recordset > >> > 'cmbAuthorsInitials ' this is a combo box that I wanted to set it to > >> > but I created > >> > 'a ListBox to see if that was the reason it didn't work. > >> > ListBox1.ColumnCount = rs.Fields.Count > >> > >> Well, there are 3 objects there. ListBox1, the rs Recordset and the > >> Fields > >> collection. Check each of them in turn and see if they are currently > >> valid. > >> > >> > >> -- > >> Regards > >> Jonathan West - Word MVP > >> www.intelligentdocuments.co.uk > >> Please reply to the newsgroup > >> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org > >

Re: Word UserForm ScreenUpdating Slow jkbourland[ at ]att.net 24.07.2006 22:40:48 After a lot of searching... this is what has worked so far. If IsNull(cmbAuthorsInitials.Value) Then Kerri jkbourland[ at ]att.net wrote: [Quoted Text] > Hi Jonathan , > > After your last reply I decided to copy and paste the text in, instead > of typing the text, and it worked great (it's super fast). There must > have been another typo somewhere. I put the code below for anyone > following the thread. > > I do have one more question. Before when I was pulling the data in > from the word table I could write the following code and I would not > get an error. Now it doesn't recognize the quotes ("") to indicate > Null or Empty field. If I try to use Null or Empty I get an error > also...any suggestions? It goes right to "Else" instead of running the > code if the statement is true. If you point to > cmbAuthorsInitials.Value it says = Null. > > Thank you again for your time and help. > -Kerri > > '------New problem----- > 'Insert Title under authorsnamae at top. > cmbAuthorsInitials.BoundColumn = 7 'Title at top > > If cmbAuthorsInitials.Value = "" Then > > ActiveDocument.CustomDocumentProperties("AuthorTitle").Value = " " > ' UpdateBookmarkText .Bookmarks("JwBk3"), " " 'space to > clear text > 'JWAuthorTitle2 Style apply formatting after to 0 > > ActiveDocument.Styles("JWAuthorTitle2").ParagraphFormat.SpaceAfter = 0 > Else: > > ActiveDocument.CustomDocumentProperties("AuthorTitle").Value = > cmbAuthorsInitials.Value > StylesTitle 'Module called JWLtrStyles 'Runs a > macro that changes the style formatting 'Apply spacing after if ther is > a title > > End If > > '-------------------- > > > '========================= > Private Sub UserForm_Initialize() > Dim db As DAO.Database > Dim rs As DAO.Recordset > Dim NoOfRecords As Long > > ' Open the database > Set db = OpenDatabase("S:\JW Temp Authors\JWAuthor List.xls", > False, False, "Excel 8.0") > > ' Retrieve the recordset > Set rs = db.OpenRecordset("SELECT * FROM `Authors`") > > ' Determine the number of retrieved records > With rs > .MoveLast > NoOfRecords = .RecordCount > .MoveFirst > End With > > ' Set the number of Columns = number of Fields in recordset > cmbAuthorsInitials.ColumnCount = rs.Fields.Count > > ' Load the ListBox with the retrieved records > cmbAuthorsInitials.Column = rs.GetRows(NoOfRecords) > > ' Cleanup > rs.Close > db.Close > > Set rs = Nothing > Set db = Nothing > > End Sub > '========= > > Jonathan West wrote: > > Actually, it looks like you have done a simple typo compared to the article. > > You have this > > > > ListBox1 = rs.getrows(NoOfRecords) > > > > but the article has this > > > > ListBox1.Column = rs.GetRows(NoOfRecords) > > > > > > -- > > Regards > > Jonathan West - Word MVP > > www.intelligentdocuments.co.uk > > Please reply to the newsgroup > > Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org > > > > > > <jkbourland[ at ]att.net> wrote in message > > news:1152828610.269558.17370[ at ]i42g2000cwa.googlegroups.com... > > >I might need some direction on how to check if they are valid. > > > > > > I right clicked on the objects and added a "Watch" to rs, ListBox1 and > > > Fields object then stepped through the macro. Listbox1's value was > > > <empty>, because it erred before it could give it a value (right?). I > > > also know that if you press (Ctrl + J) it will list the "Properties and > > > Methods" that can be used with that object....and I get nothing after > > > Listbox1. So that makes me guess that it doesn't like that > > > combination? > > > > > > If I removed the UserForm Field Name like "ListBox1" it would not give > > > me an error. But it also didn't populate my list box with the records > > > from the excel documents named range. > > > > > > ColumnCount = rs.Fields.Count > > > > > > Do you know if you have to set up an ODBC connection through Control > > > Panel | Administrative Tools | Data Sources (ODBC) in order for this to > > > work? > > > > > > Sorry, my experience is self taught through the newsgroups. Thanks for > > > your help. > > > Kerri > > > > > > > > > Jonathan West wrote: > > >> <jkbourland[ at ]att.net> wrote in message > > >> news:1152824734.558493.316120[ at ]p79g2000cwp.googlegroups.com... > > >> > Hi Jonathan, > > >> > > > >> > Okay, I started the DAO using the link you provided on the previous > > >> > reply. But I am already getting an error. The first error was on the > > >> > 'retrieve the recordset option. I had to remove the single quotes from > > >> > the name of the named range. ('Authors'). That worked and then the > > >> > second error is at the "ListBox1.ColumnCount = rs.Fields.Count". I get > > >> > object required 424 error. I did add the Microsoft DAO 3.6 Object > > >> > Library as well. > > >> > > > >> > Thanks for your help. > > >> > Kerri > > >> > > > >> > 'I have this saved as a module and run it in the initalize statement. > > >> > Sub CompleteAuthorsList() > > >> > Dim db As DAO.Database > > >> > Dim rs As DAO.Recordset > > >> > Dim NoOfRecords As Long > > >> > > > >> > 'Open the database > > >> > Set db = OpenDatabase("S:\JW Temp Authors\JWAuthor List.xls", False, > > >> > False, "Excel 8.0") > > >> > > > >> > 'Retrieve the recordset > > >> > Set rs = db.OpenRecordset("SELECT * From Authors") > > >> > > > >> > 'Determine the number of retrieved records > > >> > With rs > > >> > .movelast > > >> > NoOfRecords = .RecordCount > > >> > .MoveFirst > > >> > End With > > >> > > > >> > > > >> > '+++ Getting Object Required Error424 here. > > >> > 'Set the number of columns = number of fields in the recordset > > >> > 'cmbAuthorsInitials ' this is a combo box that I wanted to set it to > > >> > but I created > > >> > 'a ListBox to see if that was the reason it didn't work. > > >> > ListBox1.ColumnCount = rs.Fields.Count > > >> > > >> Well, there are 3 objects there. ListBox1, the rs Recordset and the > > >> Fields > > >> collection. Check each of them in turn and see if they are currently > > >> valid. > > >> > > >> > > >> -- > > >> Regards > > >> Jonathan West - Word MVP > > >> www.intelligentdocuments.co.uk > > >> Please reply to the newsgroup > > >> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org > > >