Glossary of terms using excel vba

[laxmananm]

Hi ,

I have a 600+ terms in my excel sheet and each term has different description/Explanation. I have to create a macro that should retrieve /Display the description/Explanation when I click the specific term in the macro. I have a terms in the Alphabetical order. So if I Select A button it should retrieve only That terms alone.Like wise for till Z.Can anyone help me to build up the macro for the requirement.


Thanks in advance

[Yongle]

I do not understand what you are asking

Are TERMS in ColumnA
Are DESCRIPTIONS\EXPLANATIONS in ColumnB

Are you wanting dynamic list of all terms (sorted alphabetically)
the user will select one only
then get vba to automatically display the description/explanation for that term

[laxmananm]

Thanks Yongle.Let me explain.

Like I have 600 terms or Words separately.Each word have own explanation with one or two line. Now I have a word in one cell like from A1 to AX..Explanation starting from B1 to BX..respectively.I just want to create a user form / Pop up message pattern. If I select on word in the user form it will pop-up the description of the same .Like wise for all.

let me know for any questions
Thanks

[Kenneth Hobs]

Should be simple enough. If you can attach a short example file, that will help us help you faster and better.

[laxmananm]

Hi this is the sample.I want to mainatain the values in A,B column as a data base

[Kenneth Hobs]

Private Sub UserForm_Initialize()
FillComboBox1
End Sub


Private Sub FillComboBox1()
ComboBox1.List = WorksheetFunction.Transpose(Range("A2", Range("A" & Rows.Count).End(xlUp)))
End Sub


Private Sub ComboBox1_Change()
Dim r As Range, f As Range
Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set f = r.Find(ComboBox1.Value)
If Not f Is Nothing Then TextBox1.Value = f.Offset(, 1).Value
End Sub


Private Sub CommandButton2_Click()
FillComboBox1
ComboBox1.Value = ""
TextBox1.Value = ""
End Sub


Private Sub CommandButton1_Click()
Unload Me
End Sub

[laxmananm]

That's Awesome Kenneth. But one small update ..When am trying to open user form it ll be opening with the Dropdown and the dropdown having all the values present in the A column. So there is no use of Update button field.What I want is ,When I click button it will open the user form With empty list of dropdown.When I click update button it will be load the A column values to the drop down.One more question is when I open the macro the values behind is visible to user.but I want to disappear and the Data should be act as a Database and it will be hidden.

Thanks

[Kenneth Hobs]

You could add a macro like this in a Module and call it from a Form Button.



Sub ShowUserForm1()
With Sheet1
.Visible = xlSheetHidden
UserForm1.Show
.Visible = xlSheetVisible
End With
End Sub

If you don't want the ComboBox to have values when the userform initiates, remove the FillComboBox1 line from the UserForm_Initialize event.

It is also prudent to use full reference to the sheet if you use the method above so code for the Userform1 would be:


Private Sub UserForm_Initialize()
FillComboBox1
End Sub


Private Sub FillComboBox1()
With Sheet1
ComboBox1.List = WorksheetFunction.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)))
End With
End Sub


Private Sub ComboBox1_Change()
Dim r As Range, f As Range
With Sheet1
Set r = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set f = r.Find(ComboBox1.Value)
If Not f Is Nothing Then TextBox1.Value = f.Offset(, 1).Value
End With
End Sub


Private Sub CommandButton2_Click()
FillComboBox1
ComboBox1.Value = ""
TextBox1.Value = ""
End Sub


Private Sub CommandButton1_Click()
Unload Me
End Sub

[laxmananm]

Dear Keeneth,
Thanks, I have Done what we have discussed earliear.Now I have a below Question.

I have a user form With the below buttons and controls.
Combo Box-The combo box will listdown all the Words available in sheet1 (Which is ordered in alphabetically)
Text box: It should retrieve description for the selected word in combo box.
My Question: When i select the combo box it will rertieve all the words availble in the Sheet1 A coulmn.
But i want to Add some buttons.I need add a buttons from A to Z and if i select A button it should Get the words in the dropdown
start with letter A and if i select B it should fetch only the Words Start From B letter.Like wise till Z..Is there any shortcut
code for Making this simple or i need to add 24 Alphabets in my userform.If yes how could i code for this.Please advice.


Thanks