![]() |
If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
![]()
Sue,
I have created a VBA program that does pretty much what I want to do in terms of reading data from Excel into a (4,30) array and then filling the list box in a VBA test form that contains no controls but the list box. However, I am trying to refine the code by studying the properties for the list box object and can't find answers to following questions: 1) When Column Heads is selected to True, how do I make the first row of data from the spreadsheet range appear in the Column Heads? I see the boxes for the Column heads appear in my filled list but can't figure out how to populate them. Instead the first row of data from the spreadsheet appears as the first check box row in the list. 2) The Excel spreadsheet is a log that will have new records/rows placed into it over days or months by the user, with only a couple of rows initially. I have a unique requirement to retain the check boxes that were previously set in the list box by the user without over writing them when the form is intialized at a later time and the code is then triggered to update the list from the spreadsheet. Is there some way via code to freeze or lock the state of check boxes for all 30 rows during the update and then let the user check any new entries after the update? My requirement is to keep earlier rows that were in the spreadsheet and only add new ones that weren't there before when an Intialize triggers an update from the spreadsheet. 3) Is Initalize the best event for me to use to trigger the code to update from the spreadsheet? My requirement is for the latest spreadsheet data to always appear when the user opens a custom contact, custom appointment, or custom Task Request and I just presumed Initialize would be the best choice. 4) Once I get this code exactly the way I want it, how can I insert it into my custom form that I created with the Design Tool to fill the list box in it each time the selected event triggers it? Since the code is VBA not VBscript I don't know how to insert it or modify it to VBscript and I don't want to take the time to use VBA to create the same custom form I already did via the Design Tool. -- John E. |
#2
|
|||
|
|||
![]()
Sue,
I think I figured out the answer to my question 4) in this post by studying Chapter 18 of your book. Even got a first cut of my VBA code into the VBScript editor on my custom form working down to a point. However, I believe that because VBScript has no eqivalent to "ListBox.ColumnCount = x", I can't use the ListBox.List() approach to fill in my list box from an array. Is there any work around or can you suggest another way to convert my code to VBScript? Here is my code down to the line where the run error says "Variable is undefined: lstVisitRequest". Option Explicit Dim m_blnIsNew Dim m_objActiveFolder Dim m_arrMyArray(6,3) Dim m_lstVisitRequests Dim m_intI Function Item_Open() Set m_objActiveFolder = Application.ActiveExplorer.CurrentFolder If Item.Size = 0 Then m_blnIsNew = True Else m_blnIsNew = False End If Call InitForm End Function Sub InitForm() 'The list box contains 3 data columns lstVisitRequests.ColumnCount = 3 'Load integer values into first column of MyArray For m_intI = 0 To 5 arrMyArray(m_intI, 0) = m_intI Next 'Load columns 2 and three of MyArray m_arrMyArray(0, 1) = "Zero" m_arrMyArray(1, 1) = "One" m_arrMyArray(2, 1) = "Two" m_arrMyArray(3, 1) = "Three" m_arrMyArray(4, 1) = "Four" m_arrMyArray(5, 1) = "Five" 'Load data into lstVisitRequests lstVisitRequests.List() = arrMyArray If m_blnIsNew Then MsgBox "New item initialized" Else MsgBox "Existing item initialized" End If End Sub Function Item_Write() If Item.Subject = "" Then Item_Write = False MsgBox "Please fill in the Subject." End If End Function -- John E. "John E." wrote: Sue, I have created a VBA program that does pretty much what I want to do in terms of reading data from Excel into a (4,30) array and then filling the list box in a VBA test form that contains no controls but the list box. However, I am trying to refine the code by studying the properties for the list box object and can't find answers to following questions: 1) When Column Heads is selected to True, how do I make the first row of data from the spreadsheet range appear in the Column Heads? I see the boxes for the Column heads appear in my filled list but can't figure out how to populate them. Instead the first row of data from the spreadsheet appears as the first check box row in the list. 2) The Excel spreadsheet is a log that will have new records/rows placed into it over days or months by the user, with only a couple of rows initially. I have a unique requirement to retain the check boxes that were previously set in the list box by the user without over writing them when the form is intialized at a later time and the code is then triggered to update the list from the spreadsheet. Is there some way via code to freeze or lock the state of check boxes for all 30 rows during the update and then let the user check any new entries after the update? My requirement is to keep earlier rows that were in the spreadsheet and only add new ones that weren't there before when an Intialize triggers an update from the spreadsheet. 3) Is Initalize the best event for me to use to trigger the code to update from the spreadsheet? My requirement is for the latest spreadsheet data to always appear when the user opens a custom contact, custom appointment, or custom Task Request and I just presumed Initialize would be the best choice. 4) Once I get this code exactly the way I want it, how can I insert it into my custom form that I created with the Design Tool to fill the list box in it each time the selected event triggers it? Since the code is VBA not VBscript I don't know how to insert it or modify it to VBscript and I don't want to take the time to use VBA to create the same custom form I already did via the Design Tool. -- John E. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Properties of e-mails and signatures | Kate | Outlook Express | 3 | September 6th 06 06:26 PM |
Recepient properties (such as department) | waxwing | Outlook and VBA | 3 | August 10th 06 03:55 PM |
Sender Properties | [email protected] | Outlook and VBA | 4 | July 6th 06 04:34 PM |
Programming changes to XML properties | Ray Jackson | Outlook and VBA | 1 | February 7th 06 02:06 PM |
Calendar properties?? | Sue Mosher [MVP-Outlook] | Outlook - Installation | 0 | January 20th 06 09:09 PM |