User Forms

[Joanne]

I am using winxp pro and msoffice 2003

I have created a user form in my workbook, but I don't have a clue how
to show the form.

When I click on the desktop icon for the workbook, I would like it to
open and show me the form so the user can use it. I don't know where to
put the code to show the form on open, or how to code it. I know coding
will be something simple like maybe frmName.Show - but I cannot figure
out or find the answer where to put the code so I see my form when I
open the dang workbook.

I have been googleing excel forms, but have yet to come up with a
definitive site on how to create and show and code behind the form I
created. Can do this in Access, and I'm afraid my Access knowledge is
making this all the tougher for me (certainly is creating a degree of
frustration).

If you know of a site that would take me from 'turn on the computer' to
'final form project' for the raw beginner, I sure would be grateful if
you would point me to it. Or perhaps there is a book out there that
would give some time to forms. Using MSOffice 2003 Inside Out right now
but not finding what I need in the index, and reading 1000 pages of
Excel is not in my cards!! The only index listing is 'Form Command' and
that is 3 pages long and is doing me no good as far as creating a user
form.

Any help/info you can give me, I thank you muchly
Joanne

 

[Guest]

Hi, Joanne

You'll want to engage the Workbook.Open event.

Right-Click on the Excel icon (in the upper left of the Excel window)
Select: View Code
Click the "General" dropdown and select Workbook

The default event should be: Open

In its simplest form, your code would look like this:

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Does that help?
(Post back with more questions)

***********
Regards,
Ron

XL2002, WinXP

 

[Joanne]

Ron
Thank you, thank you, thank you. I am so relieved to have at least one
thing going right with this new project. And now I have a bit better
idea how to get to the right place in the VB editor and enter code.
You invited me to ask more questions and that I will do!!

I have a form with a button for each letter of the alphabet. When I
click on, say, letter A, I want the control to populate my cbo box with
all entries from my table (sheet1 in the workbook that holds the form)
that begin with the letter A in Column A.

The cbo box will have 3 columns, and when it populates, I want it to
take cols A, B and C from the table and put them in cols 0, 1, and 2 for
all records begining with the chosen alphabet. (I am assuming that
Excel, like Access, begins with col 0 in it's combo boxes.) Do I need to
choose 0 as a 'bound' column?

Does this sound feasible to you?

Joane

 

[Guest]

First...For your situation, the BoundColumns start numbering at 1. See
"BoundColumn" in VBA help for more details.

Next, for this example

On Sheet1:
1 range named: MyDataRange that refers to: A2:A11

Cells A2:C11 contain these values:
Alpha Col_2_Row: 2 Col_3_Row: 2
Bravo Col_2_Row: 3 Col_3_Row: 3
Charlie Col_2_Row: 4 Col_3_Row: 4
Delta Col_2_Row: 5 Col_3_Row: 5
Echo Col_2_Row: 6 Col_3_Row: 6
Adam Col_2_Row: 7 Col_3_Row: 7
Betty Col_2_Row: 8 Col_3_Row: 8
Carl Col_2_Row: 9 Col_3_Row: 9
Donna Col_2_Row: 10 Col_3_Row: 10
Ed Col_2_Row: 11 Col_3_Row: 11


I also created a UserForm containing:
1 CommandButton named: cmd_A
1 ComboBox named: ComboBox1

In the UserForm code module, I created a procedure for altering the ComboBox
data:

'------Start of Code------
Private Sub FillCboBoxList(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With ComboBox1
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
End With
End Sub
'------End of Code------


This is the code for cmd_A:
'------Start of Code------
Private Sub cmd_A_Click()
FillCboBoxList MyLetter:="A"
End Sub
'------End of Code------

When the form is displayed, and the cmd_A button is clicked....The clicked
button sends its letter to the FillCboBoxList program....which clears the
ComboBox list and repopulates it.

You can attach that same code to each button...changing the letter, of course

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Joanne]

Thanks for the help. I am beginning to find my way around the Excel VB
environment a bit. Need to understand where the code goes. Your work is
helping me do it.

I have the FillCboBoxList sub in the userform module. I have the code
behind cmd_A. When I click on "A", I get this error on this line
..List(.ListCount - 1, 1) - cCell.Offset(ColumnOffset = 1).Value
The error is a Compile error telling me that ColumnOffset is a variable
not defined. Don't know what to do about this.

Couple other questions to help me understand the code.
in Like MyLetter & "*" what does the * do?

Also, when I look at my cbo box it looks to me like there is only one
column - may be because there is no data in there yet, but I was
thinking that I may not have the properties setup correctly. Column 1 is
bound, and column count is 3. I did not do anything else with the
properties and I wonder if I should be.

Joanne
(ps you are making this project fun after all the frustration I was
suffering - feels great to be moving forward. Thanks a bunch)

 

[Guest]

Questions, in order:

1) I'm guessing you typed the code into your UserForm module, instead of
copying it from the post. Here's why...

The problem line you posted:
..List(.ListCount - 1, 1) - cCell.Offset(ColumnOffset = 1).Value

Should be this:
..List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value

Notice the colon after "ColumnOffset". ColumnOffset is a named argument of
the Offset function.

2) Next....regarding the asterisk in this line:
The asterisk is a wildcard indicating all other (or no) characters.If MyLetter = "C", the the phrase is the equivalent of "Begins with C"

3) In a multi-column ComboBox, the other columns only assist the user when
the dropdown is used. Otherwise, only one column displays. If you want ALL 3
fields to ALWAYS be visible....try using a ListBox control

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Joanne]

You're right, I did type it in and I missed the colons and a couple of
missing periods elsewhere showed me a moment of trouble also!

When I hit the Cmd_A control, nothing is happening - here is the way my
code is looking:

Private Sub cmdA_Click()
FillCboBox MyLetter:="A"
End Sub

Private Sub FillCboBox(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With cboCustName
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
End With
End Sub

Right now the entire customer list is on sheet 1 of the workbook, and it
is alphabetized on column A.

I am wondering if it would be better to have a sheet "A" with only the
"A" customers to fill the cbo box instead of reading through all the
records all the time, I could just grab the info on sheet A and populate
the cbobox and do likewise thruout the alphabet?

Is this code going to still work if I trade out my cbo box for a list
box? Of course I realize I will have to refer to the list box instead of
the cbo box in the code.

I do want all 3 columns to be visible to the user at all times so they
can make a proper choice. The premise of this app is to load a cbo box
with Customer Name, Cust Street and CustCIty. The user will make the
correct choice and when they click on their choice, a copy of the
master pricing sheet will open and by code the unnecessary sheets will
be dumped, and the unnecessary columns on remaining sheets will be
dumpled. Then the user will have only the relevant info, send it to the
printer and send it to the customer. That is the only purpose of the
app, print out a customr's special pricing. (while not showing any of
the special pricing on vendors they don't buy from or info on other
customers).

DOes your stuff help
Does that help?
***********
You're darned tootin your stuff helps.
Thanks a million
Joanne

 

[Guest]

Hi, Joanne
Your code works fine. After clicking the [A] button...the combobox
populates. You may be expecting it to show the first item, maybe? If
yes...then read on. Both the listbox and combobox are set to display the
first item after their respective lists are filled.

Notice, the same code (with slight changes) works for the listbox.

Here's the new code:

Private Sub cmd_A_Click()
FillCboBox MyLetter:="A"
FillListBox MyLetter:="A"
End Sub

Private Sub FillCboBox(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With cboCustName
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
.ListIndex = 1
End With
End Sub

Private Sub FillListBox(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With lbxCustName
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
.ListIndex = 1
End With
End Sub

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Um....in case it wasn't obvious....I added a ListBox named "lbxCustName" to
the form. So, now it has a ComboBox and a ListBox, both populated by clicking
the [A] button.

***********
Regards,
Ron

XL2002, WinXP

Hi, Joanne
Your code works fine. After clicking the [A] button...the combobox
populates. You may be expecting it to show the first item, maybe? If
yes...then read on. Both the listbox and combobox are set to display the
first item after their respective lists are filled.

Notice, the same code (with slight changes) works for the listbox.

Here's the new code:

Private Sub cmd_A_Click()
FillCboBox MyLetter:="A"
FillListBox MyLetter:="A"
End Sub

Private Sub FillCboBox(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With cboCustName
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
.ListIndex = 1
End With
End Sub

Private Sub FillListBox(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With lbxCustName
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
.ListIndex = 1
End With
End Sub

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP

Thanks for the help. I am beginning to find my way around the Excel VB
environment a bit. Need to understand where the code goes. Your work is
helping me do it.

I have the FillCboBoxList sub in the userform module. I have the code
behind cmd_A. When I click on "A", I get this error on this line
.List(.ListCount - 1, 1) - cCell.Offset(ColumnOffset = 1).Value
The error is a Compile error telling me that ColumnOffset is a variable
not defined. Don't know what to do about this.

Couple other questions to help me understand the code.
in Like MyLetter & "*" what does the * do?

Also, when I look at my cbo box it looks to me like there is only one
column - may be because there is no data in there yet, but I was
thinking that I may not have the properties setup correctly. Column 1 is
bound, and column count is 3. I did not do anything else with the
properties and I wonder if I should be.

Joanne
(ps you are making this project fun after all the frustration I was
suffering - feels great to be moving forward. Thanks a bunch)

 

[Joanne]

Yes it is working sweet - I love it.
so .Listindex = 1 causes the first item to show in the cbo?

I would like to use column heads and fix the column widths to fit my
data.

Again, it is all greek to me. In Access, you just list the widths you
want with a semi-colon separating the measurements. Here in Excel it
seems to be a different thing becuause it didn't work for me.

So how can I make the widths the way I want and put column titles in the
col heads?

You said that in a cbobox the columns other than 1 are just to assist
the user. Is that also true in a list box. Before I go too far on this
project, which do you recommend that I use and why (for my future info
please). I will have the user click on the customer name in column 1 (or
will they really be clicking the entire entry in the row, it being an
index of the array that is loaded in the cbobox, or am I all wet here?,
and I want to fire my code off of that click. I hope this is possible.
Must look around more in the vb editor.

I have learned more from you in less time than it would take me to get
to my Community College for just 1 class, and they don't give me this
much useful info in one class.
Thank you for your expertise and time.
Joanne

Hi, Joanne
Your code works fine. After clicking the [A] button...the combobox
populates. You may be expecting it to show the first item, maybe? If
yes...then read on. Both the listbox and combobox are set to display the
first item after their respective lists are filled.

Notice, the same code (with slight changes) works for the listbox.

Here's the new code:

Private Sub cmd_A_Click()
FillCboBox MyLetter:="A"
FillListBox MyLetter:="A"
End Sub

Private Sub FillCboBox(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With cboCustName
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
.ListIndex = 1
End With
End Sub

Private Sub FillListBox(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With lbxCustName
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
.ListIndex = 1
End With
End Sub

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP

Thanks for the help. I am beginning to find my way around the Excel VB
environment a bit. Need to understand where the code goes. Your work is
helping me do it.

I have the FillCboBoxList sub in the userform module. I have the code
behind cmd_A. When I click on "A", I get this error on this line
.List(.ListCount - 1, 1) - cCell.Offset(ColumnOffset = 1).Value
The error is a Compile error telling me that ColumnOffset is a variable
not defined. Don't know what to do about this.

Couple other questions to help me understand the code.
in Like MyLetter & "*" what does the * do?

Also, when I look at my cbo box it looks to me like there is only one
column - may be because there is no data in there yet, but I was
thinking that I may not have the properties setup correctly. Column 1 is
bound, and column count is 3. I did not do anything else with the
properties and I wonder if I should be.

Joanne
(ps you are making this project fun after all the frustration I was
suffering - feels great to be moving forward. Thanks a bunch)

 

[Joanne]

Ron
I am using the code in a list box and I notice that the list box is
being populated by the customer name, street and city as expected, but
also it is being populated by the city before and after the listings, as
well as sporadically withing the correct listings.
In other words, it looks like column 3 is being read once as part of the
3 column record and showing in column 3, and then C is being read again
and distributed thruout column 1.

I don't know what to make of it.
Can you advise me please?

 

[Guest]

Hi, Joanne

First, a correction: ListIndex = 0 displays the first item in the list
(sorry for the mental lapse)

Second, you can only display ColumnHeaders when you link to a contiguous
range of cells. As you'll see in the demo file (referenced below), I used a
workaround of just putting text labels above the ComboBox and ListBox.

Third, if a picture is worth a thousand words, think how many words a demo
Excel file is worth!

I put my "play" file in a free file service at this link:
http://www.savefile.com/files/862594

Please click that link and open the file
Yes, it contains macros...none of them are AutoOpen.
No, it's not production quality work...it's just a demo file

Study the file and post back with more questions.

***********
Regards,
Ron

XL2002, WinXP

Yes it is working sweet - I love it.
so .Listindex = 1 causes the first item to show in the cbo?

I would like to use column heads and fix the column widths to fit my
data.

Again, it is all greek to me. In Access, you just list the widths you
want with a semi-colon separating the measurements. Here in Excel it
seems to be a different thing becuause it didn't work for me.

So how can I make the widths the way I want and put column titles in the
col heads?

You said that in a cbobox the columns other than 1 are just to assist
the user. Is that also true in a list box. Before I go too far on this
project, which do you recommend that I use and why (for my future info
please). I will have the user click on the customer name in column 1 (or
will they really be clicking the entire entry in the row, it being an
index of the array that is loaded in the cbobox, or am I all wet here?,
and I want to fire my code off of that click. I hope this is possible.
Must look around more in the vb editor.

I have learned more from you in less time than it would take me to get
to my Community College for just 1 class, and they don't give me this
much useful info in one class.
Thank you for your expertise and time.
Joanne

Hi, Joanne
Your code works fine. After clicking the [A] button...the combobox
populates. You may be expecting it to show the first item, maybe? If
yes...then read on. Both the listbox and combobox are set to display the
first item after their respective lists are filled.

Notice, the same code (with slight changes) works for the listbox.

Here's the new code:

Private Sub cmd_A_Click()
FillCboBox MyLetter:="A"
FillListBox MyLetter:="A"
End Sub

Private Sub FillCboBox(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With cboCustName
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
.ListIndex = 1
End With
End Sub

Private Sub FillListBox(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With lbxCustName
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
.ListIndex = 1
End With
End Sub

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP

Thanks for the help. I am beginning to find my way around the Excel VB
environment a bit. Need to understand where the code goes. Your work is
helping me do it.

I have the FillCboBoxList sub in the userform module. I have the code
behind cmd_A. When I click on "A", I get this error on this line
.List(.ListCount - 1, 1) - cCell.Offset(ColumnOffset = 1).Value
The error is a Compile error telling me that ColumnOffset is a variable
not defined. Don't know what to do about this.

Couple other questions to help me understand the code.
in Like MyLetter & "*" what does the * do?

Also, when I look at my cbo box it looks to me like there is only one
column - may be because there is no data in there yet, but I was
thinking that I may not have the properties setup correctly. Column 1 is
bound, and column count is 3. I did not do anything else with the
properties and I wonder if I should be.

Joanne
(ps you are making this project fun after all the frustration I was
suffering - feels great to be moving forward. Thanks a bunch)
Ron Coderre wrote:

First...For your situation, the BoundColumns start numbering at 1. See
"BoundColumn" in VBA help for more details.

Next, for this example

On Sheet1:
1 range named: MyDataRange that refers to: A2:A11

Cells A2:C11 contain these values:
Alpha Col_2_Row: 2 Col_3_Row: 2
Bravo Col_2_Row: 3 Col_3_Row: 3
Charlie Col_2_Row: 4 Col_3_Row: 4
Delta Col_2_Row: 5 Col_3_Row: 5
Echo Col_2_Row: 6 Col_3_Row: 6
Adam Col_2_Row: 7 Col_3_Row: 7
Betty Col_2_Row: 8 Col_3_Row: 8
Carl Col_2_Row: 9 Col_3_Row: 9
Donna Col_2_Row: 10 Col_3_Row: 10
Ed Col_2_Row: 11 Col_3_Row: 11


I also created a UserForm containing:
1 CommandButton named: cmd_A
1 ComboBox named: ComboBox1

In the UserForm code module, I created a procedure for altering the ComboBox
data:

'------Start of Code------
Private Sub FillCboBoxList(MyLetter As String)
Dim SrcData As Range
Dim cCell As Range

Set SrcData = Range("MyDataRange")
With ComboBox1
.Clear
For Each cCell In SrcData.Cells
If UCase(cCell.Value) Like MyLetter & "*" Then
.AddItem cCell.Value
.List(.ListCount - 1, 1) = cCell.Offset(ColumnOffset:=1).Value
.List(.ListCount - 1, 2) = cCell.Offset(ColumnOffset:=2).Value
End If
Next cCell
End With
End Sub
'------End of Code------


This is the code for cmd_A:
'------Start of Code------
Private Sub cmd_A_Click()
FillCboBoxList MyLetter:="A"
End Sub
'------End of Code------

When the form is displayed, and the cmd_A button is clicked....The clicked
button sends its letter to the FillCboBoxList program....which clears the
ComboBox list and repopulates it.

You can attach that same code to each button...changing the letter, of course

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Ron
Thank you, thank you, thank you. I am so relieved to have at least one
thing going right with this new project. And now I have a bit better
idea how to get to the right place in the VB editor and enter code.
You invited me to ask more questions and that I will do!!

I have a form with a button for each letter of the alphabet. When I
click on, say, letter A, I want the control to populate my cbo box with
all entries from my table (sheet1 in the workbook that holds the form)
that begin with the letter A in Column A.

The cbo box will have 3 columns, and when it populates, I want it to
take cols A, B and C from the table and put them in cols 0, 1, and 2 for
all records begining with the chosen alphabet. (I am assuming that
Excel, like Access, begins with col 0 in it's combo boxes.) Do I need to
choose 0 as a 'bound' column?

Does this sound feasible to you?

Joane
Ron Coderre wrote:

Hi, Joanne

You'll want to engage the Workbook.Open event.

Right-Click on the Excel icon (in the upper left of the Excel window)
Select: View Code
Click the "General" dropdown and select Workbook

The default event should be: Open

In its simplest form, your code would look like this:

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Does that help?
(Post back with more questions)

***********
Regards,
Ron

XL2002, WinXP


:

I am using winxp pro and msoffice 2003

I have created a user form in my workbook, but I don't have a clue how
to show the form.

When I click on the desktop icon for the workbook, I would like it to
open and show me the form so the user can use it. I don't know where to
put the code to show the form on open, or how to code it. I know coding
will be something simple like maybe frmName.Show - but I cannot figure
out or find the answer where to put the code so I see my form when I
open the dang workbook.

I have been googleing excel forms, but have yet to come up with a
definitive site on how to create and show and code behind the form I
created. Can do this in Access, and I'm afraid my Access knowledge is
making this all the tougher for me (certainly is creating a degree of
frustration).

If you know of a site that would take me from 'turn on the computer' to
'final form project' for the raw beginner, I sure would be grateful if
you would point me to it. Or perhaps there is a book out there that
would give some time to forms. Using MSOffice 2003 Inside Out right now
but not finding what I need in the index, and reading 1000 pages of
Excel is not in my cards!! The only index listing is 'Form Command' and
that is 3 pages long and is doing me no good as far as creating a user
form.

Any help/info you can give me, I thank you muchly
Joanne

 

[Joanne]

Ron
I crawled around in your app, learned how to do column widths, put
labels in to title my columns, fixed listindex to = 0, but I have
decided to go with the listbox control so that all 3 columns are
consistently visible.

I am using the code in a list box and I notice that the list box is
being populated by the customer name, street and city as expected, but
also it is being populated by the city before and after the listings, as
well as sporadically withing the correct listings.
In other words, it looks like column 3 is being read once as part of the
3 column record and showing in column 3, and then C is being read again
and distributed thruout column 1.
Here is a small example of what is happening. I wonder if there is
something in my table that is causing this?
Naperville
Niles
Niles
Niles
North Judson
New Lenox
Company A 100 Main St Naperville
Company B 101 Main St New Lenox
Company C 120 Main St New Berlin
New Berlin
Company D 130 Main St Naperville
Company E 140 Main St Niles
Niles
Company F 150 Main St New Lenox
etc etc
and at the end of the correct entries, there is another list of cities
in column 1.

I don't know what to make of it.
Can you advise me please?

 

[Guest]

Joanne,

If you copy/paste your data into the demo I posted, do you get the same
results?
I extended the data in my copy to this:

Name Address City
Alpha 2 Main Street Annapolis
Bravo 3 Pond Street Boston
Charlie 4 Pleasant Street Charlotte
Delta 5 Main Street Denver
Echo 6 Pond Street Erie
Adam 7 Pleasant Street Fredericksburg
Betty 8 Main Street Gorham
Carl 9 Pond Street Hyannis
Donna 10 Pleasant Street Islington
Ed 11 Main Street Jackson
Anne 12 Pond Street Kirbyville
Bart 13 Pleasant Street Littleton
Cora 14 Main Street Munroe
Dave 15 Pond Street Naperville
Ellie 16 Pleasant Street Orlando
Allan 17 Main Street Petersburg
Barbara 18 Pond Street Quebec
Cory 19 Pleasant Street Raleigh
Darlene 20 Main Street Scranton
Ernie 21 Pond Street Titusville

The code ran properly without incident and the display was correct.
I suspect the problem may lie in your data. Can you post a sample of the
structure?

Also, I posted a new version of the demo, containing the latest changes.

File name: LoadMultiCol_ListBox_v1a.xls
Locaton: http://www.savefile.com/files/864195


***********
Regards,
Ron

XL2002, WinXP

 

[Joanne]

Ron
When I paste my table data into your app, I get the same errors I do in
my app.
I see though that your data runs sweet in your app. I tried to put your
data into my app but couldn't get it to run from sheet2 because I don't
have enough excel knowledge to set it up.

What should I look at in the table. I cut and pasted it - am thinking I
should maybe re-create the table, as I was going to make additional
columns of info for each customer anyway so I would be doing quite a bit
of work to get the data where I want it?

What do you think?

Also, can we talk about how I will capture the choice the user makes so
that I can send it to a function that will read the data and eliminate
the columns and the sheets that are not needed for this particular
customer? There are 62 sheets on the master - only maybe half a dozen
will be relevant to a particular customer - I want the relevant columns
to be listed with the CustInfo record for each customer so that when I
capture the choice, I can read the record to see what sheets I will need
to present to the user and code the rest of the sheets to be deleted
from this instance of the 'copyOfMaster' workbook. Likewise, on the
sheets that I show the user, I need to dump maybe 5 or 6 columns, again
the info will be on the record in the table. After the sheet is built
the way the user needs it, it will be used to inform the customer and/or
be printed. Then the workbook will be deleted and the app shut down
until user needs to look up another customer's info.
I think this will work extremely well for my users, but the coding is
going to be a bit of a challenge for me, but I like a good challenge and
you mvps are always so willing to help us newbies along.
Again, I am learning a ton from you that will stay with me for future
projects, and I have a great appreciation for that.

 

[Guest]

Since you're rapidly approaching the line between "Help me with this
question" and "Help me build a project", let's decide to work this,
issue-by-issue, in separate threads. There's a general tendency in the
newsgroups to let the primary responder continue with the thread. By posting
each request category separately, you'll get a larger pool of talent working
with you simultaneously.

Regarding your listbox issue, can you post some sample data (including the
problem items)? Alter any sensitive data, of course, but keep the structure
intact.

***********
Regards,
Ron

XL2002, WinXP

 

[Joanne]

Gotcha
Thanks a bunch Ron

Since you're rapidly approaching the line between "Help me with this
question" and "Help me build a project", let's decide to work this,
issue-by-issue, in separate threads. There's a general tendency in the
newsgroups to let the primary responder continue with the thread. By posting
each request category separately, you'll get a larger pool of talent working
with you simultaneously.

Regarding your listbox issue, can you post some sample data (including the
problem items)? Alter any sensitive data, of course, but keep the structure
intact.

***********
Regards,
Ron

XL2002, WinXP

 

[Joanne]

Here is a table that I just recreated for this little test of the list
box fill procedure:
F E Wheaton Main St Wheaton
F E Wheaton Main St Yorkville
Fox Home Center Broadway Alsip
ABC Supply Co Belmont Chicago
Bradco Supply Rathbone Montgomery
Bradco Supply Lake St Aurora
Gazebo Junction Rte 35 St. Charles
Deckman Lily Street Elburn
Deck Yard April St Big Rock
Allied Bldrs Leisure Ln Oswego
Evanston Lbr Charles Ln Evanston
Ericksons Pulaski Chicago

Here are the results of clicking cmd_A:
Alsip
ABC Supply Co Belmont Chicago
Aurora
April St Big Rock
Allied Bldrs Leisure Ln Oswego

It looks to me like the code behind cmd_A is looking at the data like
r1c1, r1c2, r1c3, r2c1, r2c2, r2c3, r3c1, r3c2, r3c3 etc and anythime
the first letter in the cell is an A, it is starting a new entry in the
list box.

So perhaps the code for cmd_A needs to be modified to force the search
to work only on column 1.

Am I in the ballpark on this one?

I see the answer to my problem!! on the printout of the code you gave
me. I used A1:C333 for MyDataRange where I think I was supposed to use
A1:A333 - correct?

You got me thinking in code speak!! hooray

Will fix this then tackle the next problem in a new thread as you
suggested. Please let me know if this looks like it is my problem and
if I fixed it myself - I need the confidence ;- ) to tackle the rest of
this challenge

 

[Guest]

I see the answer to my problem!! on the printout of the code you gave
me. I used A1:C333 for MyDataRange where I think I was supposed to use
A1:A333 - correct?<<

A+, Joanne! Absolutely, correct. The MyDataRange refers to a single column
of cells. Nice job figuring out the issue.

Let me know if I can help any more with your listbox issues.
(and...I'll looking for your next posts.)

***********
Regards,
Ron

XL2002, WinXP