Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change
Hello all,
I'm trying to use the workshee_change event to grab the value of the current filter criteria1 and then paste that value into a particular cell. However, it's not working. Anyone have ideas? Here's the code; Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$H$991" Then 'Application.EnableEvents = False If Target.Value = 10 Then With ActiveWorkbook If .AutoFilterMode Then With .AutoFilter.Filters(1) If .On Then c1 = .Criteria1 Range("F992").Value = c1 End With End If End With End If ' Application.EnableEvents = True End If End Sub Cheers, Job |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change
Filters belong to the worksheet--not the workbook.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim c1 As String If Target.Address = "$H$991" Then Application.EnableEvents = False If Target.Value = 10 Then With Me If .AutoFilterMode Then With Me.AutoFilter.Filters(1) If .On Then c1 = .Criteria1 End If Range("F992").Value = c1 End With End If End With End If Application.EnableEvents = True End If End Sub I turned on the enableevents stuff. If you're changing another cell, you probably don't want the code to fire again. The Me. refers to the thing that we'er in. Since we'er in a worksheet module, it refers to the worksheet owning the code. (I also changed your if/then into a multiple line if/then. I just find that easier to read when it's in that portion of code--but that's just my preference.) Job wrote: Hello all, I'm trying to use the workshee_change event to grab the value of the current filter criteria1 and then paste that value into a particular cell. However, it's not working. Anyone have ideas? Here's the code; Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$H$991" Then 'Application.EnableEvents = False If Target.Value = 10 Then With ActiveWorkbook If .AutoFilterMode Then With .AutoFilter.Filters(1) If .On Then c1 = .Criteria1 Range("F992").Value = c1 End With End If End With End If ' Application.EnableEvents = True End If End Sub Cheers, Job -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change | Excel Worksheet Functions | |||
Worksheet_Change | Excel Discussion (Misc queries) | |||
Worksheet_change won't run | Excel Discussion (Misc queries) | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |