jeverist, sorry it took so long to get back to this thread.
I appreciate your response very much. It was helpful, especially since it reminded me of the object-oriented capabilities of Visual Basic.
I have decided to solve this problem another way entirely. What I really need is a way to use the Excel spreadsheet as a database, with a GUI interface to make it easy to view and enter the data. I tried doing this with Microsoft's Access, but I was unable to do so. Then I thought it might be possible with OpenOffice's suite of tools, and I found that OpenOffice has such a capability. So I am just using that to develop my GUI interface.
Thanks for your help. Since you were the only one who responded (??!), I am awarding you the points. Best wishes, and thanks again.
coderlen
Main Topics
Browse All Topics
by: jeveristPosted on 2007-09-25 at 04:30:44ID: 19954793
Hi coderlen,
> 1) Modify the code I found to allow for 30 columns
This change to your 'UserForm_Initialize()' routine will set the ListBox columns to the number of used columns in Sheet1:
Private Sub UserForm_Initialize()
'Clean data range
DeleteBlankRows
DeleteBlankColumns
Dim rng As Range, irows As Long, icols As Long
Set rng = Worksheets("Sheet1").UsedR
irows = rng.Rows.Count - rng.Row + 1
icols = rng.Columns.Count - rng.Column + 1
'Set properties of listbox1
With Me.ListBox1
.BoundColumn = 1
.ColumnCount = icols
.ColumnHeads = True
.TextColumn = True
.RowSource = rng.Parent.Name & "!" & rng.Address
.ListStyle = fmListStyleOption
.ListIndex = 0
End With
End Sub
Jim