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.

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.