PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Course Of The Month
3 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
ABCDEFGHI....Command Box's Lookup
Posted on 2001-08-31
I need to have an Alphabetical Lookup Routine with
a Bar accross the top of the page.
A B C D etc
The lookup up is a Access Database
I realise i can have a combo or list box doing this, and already have this working ok., but, i would also like to have the above as well.
Is there an easy way of doing this, without manually generating 26 command buttons etc....
And also, having a common routine eg (AlphaClicked), which will know which Button has been clicked, and then do the lookup.
Would appreciate some code to do the above.
Hope someone can give me some assistance.
a Bar accross the top of the page.
A B C D etc
The lookup up is a Access Database
I realise i can have a combo or list box doing this, and already have this working ok., but, i would also like to have the above as well.
Is there an easy way of doing this, without manually generating 26 command buttons etc....
And also, having a common routine eg (AlphaClicked), which will know which Button has been clicked, and then do the lookup.
Would appreciate some code to do the above.
Hope someone can give me some assistance.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
2
-
2
- +2
11 Comments
Use a toolbar
Or
For a common routine use 26 command buttons in an array and use the .caption parameter to instigate the source of your query.
GarrenB
Or
For a common routine use 26 command buttons in an array and use the .caption parameter to instigate the source of your query.
GarrenB
Kennedy:
You can accomplish this by doing the following:
1) Add a button to your form
2) Name the button "cmdLetter" (without the quotes)
3) Set the caption of this new button to 'A'
4) Resize the button as desired
5) Position this button where you want it to be
6) Set the Index property of this button to 0 (zero)
7) Add the following line of code to your form's Load Event:
SetUpLetterButtons
8) Add the following Subroutine to your form:
Private Sub SetUpLetterButtons()
Dim idx As Integer
Dim Btn As Control
Dim Width As Single
Dim Left As Single
With cmdLetter(0)
Left = .Left
Width = .Width
End With
For idx = 1 To 25
Load cmdLetter(idx)
With cmdLetter(idx)
.Move Left + (idx * Width)
.Caption = Chr(64 + idx)
.Visible = True
End With
Next 'idx
End Sub
9) Add the following subroutine to your form:
Private Sub UnloadLetterButtons()
Dim idx As Integer
For idx = 1 To cmdLetter.UBound
Unload cmdLetter(idx)
Next 'idx
End Sub
10) Add the following line of code to your Form's Unload Event:
UnloadLetterButtons
-Dennis Borg
You can accomplish this by doing the following:
1) Add a button to your form
2) Name the button "cmdLetter" (without the quotes)
3) Set the caption of this new button to 'A'
4) Resize the button as desired
5) Position this button where you want it to be
6) Set the Index property of this button to 0 (zero)
7) Add the following line of code to your form's Load Event:
SetUpLetterButtons
8) Add the following Subroutine to your form:
Private Sub SetUpLetterButtons()
Dim idx As Integer
Dim Btn As Control
Dim Width As Single
Dim Left As Single
With cmdLetter(0)
Left = .Left
Width = .Width
End With
For idx = 1 To 25
Load cmdLetter(idx)
With cmdLetter(idx)
.Move Left + (idx * Width)
.Caption = Chr(64 + idx)
.Visible = True
End With
Next 'idx
End Sub
9) Add the following subroutine to your form:
Private Sub UnloadLetterButtons()
Dim idx As Integer
For idx = 1 To cmdLetter.UBound
Unload cmdLetter(idx)
Next 'idx
End Sub
10) Add the following line of code to your Form's Unload Event:
UnloadLetterButtons
-Dennis Borg
Small correction:
>.Caption = Chr(64 + idx)
This really should read:
.Caption = Chr(65 + idx)
-Dennis Borg
>.Caption = Chr(64 + idx)
This really should read:
.Caption = Chr(65 + idx)
-Dennis Borg
Here's a neat solution to your problem:
1. Put a single command button on your, a little tiny one, with just the letter "A" on it. Put it exactly where you want the button to appear in the finished product. Set font, font size, bold, etc. for this "A" button, however you want all the buttons to look. The "A" button is the model for all 26 buttons. Call the button "cmdLetter" and set Index to 0, so it'll be considered an array by VB.
2. Now write this simple little function and call it from Form_Load, setting LETTER_SPACING to the distance you want between buttons:
Private Sub createLetterButtons_Click(
Const LETTER_SPACING As Integer = 100
Dim i As Integer
Dim leftPos As Integer
leftPos = cmdLetter(0).left
For i = 1 To 25
leftPos = leftPos + cmdLetter(0).width + LETTER_SPACING
Load cmdLetter(cmdLetter.UBound
With cmdLetter(cmdLetter.UBound
.Caption = Chr(i + 65)
.left = leftPos
.Visible = True
End With
Next i
End Sub
The command buttons will appear, all 26 of them, no problem. To respond to a user's selection of one of them, this is all you need:
Private Sub cmdLetter_Click(index As Integer)
MsgBox "You clicked: " & cmdLetter(index).Caption
End Sub
1. Put a single command button on your, a little tiny one, with just the letter "A" on it. Put it exactly where you want the button to appear in the finished product. Set font, font size, bold, etc. for this "A" button, however you want all the buttons to look. The "A" button is the model for all 26 buttons. Call the button "cmdLetter" and set Index to 0, so it'll be considered an array by VB.
2. Now write this simple little function and call it from Form_Load, setting LETTER_SPACING to the distance you want between buttons:
Private Sub createLetterButtons_Click(
Const LETTER_SPACING As Integer = 100
Dim i As Integer
Dim leftPos As Integer
leftPos = cmdLetter(0).left
For i = 1 To 25
leftPos = leftPos + cmdLetter(0).width + LETTER_SPACING
Load cmdLetter(cmdLetter.UBound
With cmdLetter(cmdLetter.UBound
.Caption = Chr(i + 65)
.left = leftPos
.Visible = True
End With
Next i
End Sub
The command buttons will appear, all 26 of them, no problem. To respond to a user's selection of one of them, this is all you need:
Private Sub cmdLetter_Click(index As Integer)
MsgBox "You clicked: " & cmdLetter(index).Caption
End Sub
Manually generating 26 command buttons is not big problem - if you have one control with index=0, you don't have to press 'Paste' 25 times, like:
aaaaaaaaaaaaaaaaaaaaaaaaaa
To position controls horizontally, use menu Format, Horizontal Spacing, Remove.
aaaaaaaaaaaaaaaaaaaaaaaaaa
To position controls horizontally, use menu Format, Horizontal Spacing, Remove.
Appreciate all the helpfull comments, very much.
Appreciate the help from dennisborg,also,this method works fine, as well.
Allways hard to work out who to give points with several working examples.!!!
Thanks again Kennedymr
Appreciate the help from dennisborg,also,this method works fine, as well.
Allways hard to work out who to give points with several working examples.!!!
Thanks again Kennedymr
For DennisBorg---
My solution was indeed exactly the same as yours, but I promise, it was arrived at completely independently. I didn't even know you had responded when I posted my answer. If there's a way to split the points, I'd be glad to.
---GebhartBob
My solution was indeed exactly the same as yours, but I promise, it was arrived at completely independently. I didn't even know you had responded when I posted my answer. If there's a way to split the points, I'd be glad to.
---GebhartBob
GebhartBob:
>but I promise, it was arrived at completely independently.
I am certain it was; I wasn't accusing you of cheating.
I was just surprised at the selected answer.
>If there's a way to split the points, I'd be glad to.
Nah, that's not necessary. I appreciate the offer though. I'm not really in it for the points.
It's not really a big deal to me, and I don't think it was intentional. I just don't understand why.
-Dennis Borg
>but I promise, it was arrived at completely independently.
I am certain it was; I wasn't accusing you of cheating.
I was just surprised at the selected answer.
>If there's a way to split the points, I'd be glad to.
Nah, that's not necessary. I appreciate the offer though. I'm not really in it for the points.
It's not really a big deal to me, and I don't think it was intentional. I just don't understand why.
-Dennis Borg
Sorry to get in this position.
I did not notice the comments were so close in content.
How am i able to give some points to Dennis
Regards Kennedymr
I did not notice the comments were so close in content.
How am i able to give some points to Dennis
Regards Kennedymr
>Sorry to get in this position.
>I did not notice the comments were so close in content.
>How am i able to give some points to Dennis
Kennedy, don't worry about it. As I had said in my last post, it's not the points I was thinking about.
I just did not understand why it happened that way. I do not believe it was intentional on your part.
Regards,
-Dennis Borg
>I did not notice the comments were so close in content.
>How am i able to give some points to Dennis
Kennedy, don't worry about it. As I had said in my last post, it's not the points I was thinking about.
I just did not understand why it happened that way. I do not believe it was intentional on your part.
Regards,
-Dennis Borg
Featured Post
Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library. This article will introduce you to the SC library a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.
This l…
- Microsoft Office
- Office 365
- Microsoft Access
- VBA
- Microsoft Excel
- Visual Basic Classic
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 13 hours left to enroll
Earn Certification
CompTIA IT Fundamentals
$49.00$44.10
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.