how do i use this formula without command button

[ganeshm]

[pre]

A1       B1       C1               D1                         L1
Name	Name	Position 	Department                   keyword

The above mentioned cells contain Employee database.

If i mention dept. say "HR" in cell L1, i should get only data relating to HR department.

I have used this with command button (with the below formula).

how do i use it without command button.

Sub LoadData()
Dim rng As Range
Sheets("Sheet1").Range("A1:D14").Clear
Set rng = Sheet2.Cells(1, 1).CurrentRegion
With rng
.AutoFilter 4, Sheets("Sheet1").Buttons(Application.Caller).Caption
.SpecialCells(12).Copy Sheets("Sheet1").Range("A4")
.AutoFilter
End With
End Sub

Private Sub CommandButton2_Click()
LoadData
End Sub

[/pre]

 

[Luke M]

Right-click on the sheet tab, view code, paste this in. Modify as needed.

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange

Set myRange = Range("L2:L100")

'Check if cell of interest changed
If Intersect(Target, myRange) Is Nothing Then Exit Sub
LoadData

End Sub

[/pre]

 

[ganeshm]

thank you luke

 

[ganeshm]

Hi.....


i executed the formula, but it is showing error in the following:


.AutoFilter 4, Sheets("Sheet1").Buttons(Application.Caller).Caption


i believe that due to command ".buttons" the error arises, is there any solution to that.

 

[Debraj]

Hi Ganeshm,


Its always better to give as much as possible information, it will increase your & our response time..


http://chandoo.org/forums/topic/command-to-extract-value


Regards,

Deb

 

[Debraj]

Please re-download the file from same location..


https://dl.dropboxusercontent.com/u/78831150/Excel/Sheet%20to%20extract%20value%28ganeshm%29.xlsm

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Sheet1.[L1], Target) Is Nothing Then
Dim rng As Range
Sheets("Sheet1").Range("A4:D14").Clear
Set rng = Sheet2.Cells(1, 1).CurrentRegion
With rng
.AutoFilter 4, Target
.SpecialCells(12).Copy Sheets("Sheet1").Range("A4")
.AutoFilter
End With
End If
End Sub

[/pre]

Regards,

Deb

 

[Luke M]

I think Deb's solution will work just fine, but explanation as to the problem (I missed before)

This line:

.AutoFilter 4, Sheets("Sheet1").Buttons(Application.Caller).Caption

Says to take the previous caller's name...before this was the name of the button, but this is not a worksheet_change event that does the calling. You need to either change this whole line to a constant, if you know what the name should be, or perhaps to a range callout as Deb has shown above.

 

[Debraj]

Hi Luke..


Only behalf of OP's provided data.. Its really hard to decide..

as previous solution was provided by me, that's why I able catch this..


Regards,

Deb