Getting Value of the terms using excel macro

[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

 

[mikerickson]

You could use a multi-column ListBox or Combobox and a Label.

Private Sub ListBox1_Click()
    Label1.Caption = ListBox1.Text
End Sub

Private Sub UserForm_Initialize()
    With ListBox1
        .ColumnCount = 2: Rem can be set as default
        .ColumnWidths = ";0": Rem default
        .BoundColumn = 1: Rem default
        .TextColumn = 2: Rem default
        
        .List = Range("A1:B600").Value
    End With
End Sub

 

[laxmananm]

Dear MICKG,

Thanks for the Help.But I have a question here.


Now, 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.


User form Call :

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

User form Code

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

Thanks

 

[MickG]

Perhaps something like the below:-
I Have "Remarked Out" this bits of code I did not use, Reinstate as required.
The Userform Now Has "Combobox1", "Combobox2" and "TextBox1"
On selection from "Combobox2" the List in "Combobox1" changes, with the results in TextBox1.

Private Sub ComboBox2_Change()
FillComboBox1
End Sub
Private Sub UserForm_Initialize()
    Dim n As Integer
        FillComboBox1
         Me.ComboBox2.Clear
        For n = 65 To 90
            Me.ComboBox2.AddItem Chr(n)
        Next n
        Me.ComboBox2.ListIndex = 0
End Sub




Private Sub FillComboBox1()
  Dim Rng As Range, Dn As Range
  Me.ComboBox1.Clear
  With Sheet1
       Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If UCase(Left(Dn.Value, 1)) = Me.ComboBox2.Value Then
        Me.ComboBox1.AddItem Dn.Value
    End If
Next Dn
If Me.ComboBox1.ListCount > 0 Then Me.ComboBox1.ListIndex = 0
    
    '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]

Oh my god..I can't belive this MICKG. How could I say thanks..What a brilliant Idea you have.Chanceless.Thank you so much.

1.I have two Question Here. The Combo box 2 having A-z. It will list down based on the selection.That is perfect.But if any of the Alphabet does not not have any value/Word in the Sheet1 Database it should through a pop-Up message like"The selected items does not available".

2.Also I have one Clear box Button to Clear the Drop down for Both COmbo box. When I open User form the two Combo box should have Empty Space in it.

3.Also I have one button Called "All".When click that button it should enable combo 1 and select all the sheet1 data base values and that time the A-Z aplphabet should be disabled to view all the values one next one basis.

Kindly advice.

Simply ..need 2 option ..One is selection based on the alphabet and another one is One next one view option.

Thanks

 

[MickG]

Try this :-
The CommandButton1.caption should change on CommandButton1.selection:- See code.
See other notes in code to Show Comboboxes as blank. personally I find it more helpful to leave as is , alter to suit !!!!

Private Sub ComboBox2_Change()
FillComboBox1
    If Me.ComboBox1.ListCount = 0 Then MsgBox "No Data Exists": TextBox1.Value = ""
End Sub
Private Sub CommandButton1_Click()
With CommandButton1
.Caption = IIf(.Caption = "ALL", "A-Z", "ALL")
     If .Caption = "ALL" Then
        FillComboBox1
        Me.ComboBox2.Clear
    ElseIf .Caption = "A-Z" Then
        FillComboBox1
        FillComboBox2
    End If
End With
End Sub


Private Sub UserForm_Initialize()
    Dim n As Integer
      CommandButton1.Caption = "ALL"
        FillComboBox1
          If Me.ComboBox2.ListCount > 0 Then Me.ComboBox2.ListIndex = 0
End Sub

Private Sub FillComboBox1()
  Dim Rng As Range, Dn As Range
  Me.ComboBox1.Clear
With Sheet1
   If CommandButton1.Caption = "A-Z" Then
       Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
        For Each Dn In Rng
            If UCase(Left(Dn.Value, 1)) = Me.ComboBox2.Value Then
                 Me.ComboBox1.AddItem Dn.Value
            End If
        Next Dn
    Else
         Me.ComboBox1.List = WorksheetFunction.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)))
    End If
    'Remove the line below to show blank Combooxes at start
        If Me.ComboBox1.ListCount > 0 Then Me.ComboBox1.ListIndex = 0
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 FillComboBox2()
Dim n As Integer
Me.ComboBox2.Clear
        For n = 65 To 90
            Me.ComboBox2.AddItem Chr(n)
        Next n
 'Remove the line below to show blank Combooxes at start
 If Me.ComboBox2.ListCount > 0 Then Me.ComboBox2.ListIndex = 0
End Sub

'Private Sub CommandButton2_Click()
'  FillComboBox1
'  ComboBox1.Value = ""
'  TextBox1.Value = ""
'End Sub
'
'
'Private Sub CommandButton1_Click()
'  Unload Me
'End Sub

 

[laxmananm]

Dear MICK,
Sorry for the delayed response.I just came.. With the updated code Im getting error..
I just replace the code with the new code and it is showing error.

please correct me if im wrong:

Am asssigning the below items:

Private Sub ComboBox2_Change() for Combo Box 2 Button
Private Sub FillComboBox1() for Combo Box 1 button


for other part which i have to assign..I just confused little.

How many buttons i need to execute this macro..

 

[MickG]

I attach an example file, Modify as required!!!!
https://app.box.com/s/9l2hfr9ey5olsk44sbx2aoztogtkf6l6

 

[laxmananm]

Sorry for the More inconvinece Dear MICK. Me itself tried and Created userform with the help of your code.

When the command button selected option the Other combo box will allow the user to edit.What I can add to avoid editing the combo box in selection/Non selection.
Also I have a text box data in Sheet1.I have fixed some length for the description.when running macro it fetch the datas to text box.but it is not align /Accommodate to the fixed length/Space of the Text box. Whatever (What length may be)the text box result having only single line and part of the data's are missing to view the user.

Thanks in advance

 

[MickG]

Have you looked at the Example file sent in the previous thread ????

 

[laxmananm]

I don't have a access for specific URL MICKG.So developed my own USerform with your code and it is working. I have that two Doubts only.