• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Codes for selected data criteria

A

Ajinkya

Member
Hi friends,


I have a data sheet where i want "Select Data" button to select data by user form criteria


EG:


If im selecting following details with user form

Brand: HCL

Handset Type: IdeaPad Tablet K1

Handset Color: Black


in data sheet data should show as per above selected criteria


but in a beginner stage, im fail to write a code to make it done. Please help


uploaded file on: https://www.dropbox.com/s/na4n36brc1gklvn/ViewDataBySelectionCriteria_V1.xlsm


Thanks
 
Hi Ajinkya,


It can be possible by simple Advance Filter or For loop in VBA..


But.. Can you please read the below topics..


http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/

http://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/


If any issue to adapt it, please let us know..


Regards,

Deb.
 
It would be easier if your database are transposed Rows/ Columns


But, Code tested on your file

[pre]
Code: 
Option Explicit

Private Sub UserForm_Initialize()
Dim c As Range

Me.ComboBox1.AddItem "(All)"
For Each c In Sheet2.Range("B1").CurrentRegion.Offset(0, 1).Rows(1).Cells
If c.Value <> "" Then
Me.ComboBox1.Value = c.Value
If Me.ComboBox1.ListIndex = -1 Then Me.ComboBox1.AddItem c.Value
End If
Next c
Me.ComboBox1.ListIndex = 0
End Sub

Private Sub ComboBox1_Change()
Dim Brand As String
Dim c As Range

Me.ComboBox2.Clear
Me.ComboBox2.AddItem "(All)"
If Me.ComboBox1.ListIndex > -1 Then
Brand = Me.ComboBox1.Value
If Brand = "(All)" Then Brand = "*"
For Each c In Sheet2.Range("B1").CurrentRegion.Offset(0, 1).Rows(1).Cells
If c.Value Like Brand And c.Offset(1, 0).Value <> "" Then
Me.ComboBox2.Value = c.Offset(1, 0).Value
If Me.ComboBox2.ListIndex = -1 Then Me.ComboBox2.AddItem c.Offset(1, 0).Value
End If
Next c
End If
Me.ComboBox2.ListIndex = 0
End Sub

Private Sub ComboBox2_Change()
Dim Typ As String
Dim c As Range

Me.ComboBox3.Clear
Me.ComboBox3.AddItem "(All)"
If Me.ComboBox2.ListIndex > -1 Then
Typ = Me.ComboBox2.Value
If Typ = "(All)" Then Typ = "*"
For Each c In Sheet2.Range("B1").CurrentRegion.Offset(0, 1).Rows(2).Cells
If c.Value Like Typ And c.Offset(1, 0).Value <> "" Then
Me.ComboBox3.Value = c.Offset(1, 0).Value
If Me.ComboBox3.ListIndex = -1 Then Me.ComboBox3.AddItem c.Offset(1, 0).Value
End If
Next c
End If
Me.ComboBox3.ListIndex = 0
End Sub

Private Sub CommandButton1_Click()
Dim Brand As String, Typ As String, Colr As String
Dim Plage As Range, c As Range

Brand = Me.ComboBox1.Value
If Brand = "(All)" Then Brand = "*"
Typ = Me.ComboBox2.Value
If Typ = "(All)" Then Typ = "*"
Colr = Me.ComboBox3.Value
If Colr = "(All)" Then Colr = "*"

With Sheet2
Set Plage = .Range("B:B")
For Each c In Sheet2.Range("B1").CurrentRegion.Offset(0, 1).Rows(1).Cells
If c.Value Like Brand And c.Offset(1, 0) Like Typ And c.Offset(2, 0) Like Colr Then Set Plage = Union(Plage, c.EntireColumn.Cells)
Next c
End With

With Sheet1
.UsedRange.Clear
Plage.Copy .Range("A1")
End With
Set Plage = Nothing
Unload Me
End Sub

Private Sub Cancel_Click()

Unload UserForm1
End Sub
[/pre]
 
thanks it works,


In another view of possible requirement, instead of get data in sheet1


can we select data in the data origin sheet i.e. "Data"


Data should appear as per userform criteria in the origin sheet only, like other columns and rows should get hide...
 
1. Change Code Initialize like this

[pre]
Code: 
Private Sub UserForm_Initialize()
Dim c As Range

Sheet2.UsedRange.EntireColumn.Hidden = True
Me.ComboBox1.AddItem "(All)"
For Each c In Sheet2.Range("B1").CurrentRegion.Offset(0, 1).Rows(1).Cells
If c.Value <> "" Then
Me.ComboBox1.Value = c.Value
If Me.ComboBox1.ListIndex = -1 Then Me.ComboBox1.AddItem c.Value
End If
Next c
Me.ComboBox1.ListIndex = 0
End Sub
2. Change code of CommandButton1 like this

Private Sub CommandButton1_Click()
Dim Brand As String, Typ As String, Colr As String
Dim Plage As Range, c As Range

Brand = Me.ComboBox1.Value
If Brand = "(All)" Then Brand = "*"
Typ = Me.ComboBox2.Value
If Typ = "(All)" Then Typ = "*"
Colr = Me.ComboBox3.Value
If Colr = "(All)" Then Colr = "*"

With Sheet2
Set Plage = .Range("B:B")
For Each c In Sheet2.Range("B1").CurrentRegion.Offset(0, 1).Rows(1).Cells
If c.Value Like Brand And c.Offset(1, 0) Like Typ And c.Offset(2, 0) Like Colr Then Set Plage = Union(Plage, c.EntireColumn.Cells)
Next c
End With

Plage.EntireColumn.Hidden = False
Set Plage = Nothing
Unload Me
End Sub
[/pre]
Regards
 
@mercatog

Hi!

If you let me ask, what language Excel version do you use?

If not English, maybe you'd want to give a look at this:

http://chandoo.org/forums/topic/excel-multilanguage-formula-translator-and-function-reference

Regards!
 
@ SirJB7,


Someone already done this JOB.

http://chandoo.org/forums/topic/return-a-value-from-a-range-of-values#post-38332
 
@Debraj Roy

Hi!

Thank you, just reading later this topic :)

Regards!

PS: BTW, did you dig into the code?
 
Dear mercatog, SirJB7 & Debraj Roy,


Thanks & very good morning to all of you :)


May i go ahead with mercatog's recent codes to select data in the data origin sheet only means i data should visible as per selected criteria in data sheet only.


Thanks.

Happy learning... :)
 
You must log in or register to reply here.
Back
Top