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?
Ron Coderre wrote:
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:
If UCase(cCell.Value) Like MyLetter & "*" Then
The asterisk is a wildcard indicating all other (or no) characters.
MyLetter & "*"<<
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" wrote:
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
"Joanne" wrote:
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
"Joanne" wrote:
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