Hi,

I'm having problems with the code below, and wondered if anybody had any
ideas.

Basically, the code is supposed to tell an autofilter which value to filter
on. This is supposed to be prompted when a certain cell (H3) has the value
entered into it. And the code is supposed to run automatically when the
spreadsheet is opened.

I'm happy with the autofilter part of the code, and (when I take out the
part that is supposed to update the autofilter when a new value is entered
in H3), I'm also happy with the part which makes the code run when the
spreadsheet is opened. My question therefore is: what is wrong about the
worksheet_change part of the function? (And am I calling it correctly from
workbook_open?)

I should point out that I'm a bit of a newbie, so please assume I don't know
anything if you answer!



Private Sub workbook_open()
Call worksheet_change
End Sub

Public Sub worksheet_change(ByVal target As Range)
End Sub
If Intersect(target, Range("h3")) Is Nothing Then
Exit Sub
Else
Sub filterthing()
With Sheet1

.AutoFilterMode = False
.Range("A10:M5000").AutoFilter
.Range("A10:M5000").AutoFilter Field:=5, Criteria1:=Range("h3").Text

End With

End Sub






Many thanks

Hi
Worksheet_Change happens when the worksheet changes. Opening the
workbook does not change a worksheet, so worksheet_change is not going
to happen.
Looking at your code, you are not using a worksheet_change event at
all. You can simply call an ordinary sub.
try this:

Private Sub workbook_open()
Call filterthing
End Sub

Sub filterthing()
With Sheet1
.AutoFilterMode = False
.Range("A10:M5000").AutoFilter
.Range("A10:M5000").AutoFilter Field:=5,
Criteria1:=Range("h3").Text
End With
End Sub

The sub filterthing() goes in an ordinary code module. Open the VB
editor and do Insert, Module. Put the filterthing() code in there. The
workbook_open code goes in the ThisWorkbook code module.
There are four levels of Module:
Thisworkbook module - subs in here called when some classes of event
happen at workbook level e.g. workbook opens or sheet changes
Sheet Module - subs in here called when some classes of event happen
at worksheet level e.g. the worksheet changes or a cell selection is
made
normal code module - These contain the vast bulk of your code. You can
insert as many as you want to group code sections together.They do not
require any event in the workbook or worksheet to happen for them to
run. All the macros you might assign to a button or toolbar will be in
here.
class modules - spend enough time coding with the above three modules
and you will find situations that just don't seem elegently done using
them, or even possible. Class modules then make sense.

regards
Paul

On Feb 25, 7:41*am, "Chris" wrote:
Hi,

I'm having problems with the code below, and wondered if anybody had any
ideas.

Basically, the code is supposed to tell an autofilter which value to filter
on. *This is supposed to be prompted when a certain cell (H3) has the value
entered into it. *And the code is supposed to run automatically when the
spreadsheet is opened.

I'm happy with the autofilter part of the code, and (when I take out the
part that is supposed to update the autofilter when a new value is entered
in H3), I'm also happy with the part which makes the code run when the
spreadsheet is opened. *My question therefore is: what is wrong about the
worksheet_change part of the function? (And am I calling it correctly from
workbook_open?)

I should point out that I'm a bit of a newbie, so please assume I don't know
anything if you answer!

Private Sub workbook_open()
Call worksheet_change
End Sub

Public Sub worksheet_change(ByVal target As Range)
End Sub
If Intersect(target, Range("h3")) Is Nothing Then
Exit Sub
Else
Sub filterthing()
With Sheet1

* * .AutoFilterMode = False
* * .Range("A10:M5000").AutoFilter
* * .Range("A10:M5000").AutoFilter Field:=5, Criteria1:=Range("h3").Text

End With

End Sub

Many thanks

Hi Paul,

Thanks for your advice - just one thing though: the reason I thought I
needed to use worksheet_change was that I wanted filterthing() to run each
time cell H3 had new data entered into it. Currently autofilter is only
setting itself to the value in H3 when the workbook is opened.

Is this easily achieveable?

Many thanks

Chris



wrote in message
...
Hi
Worksheet_Change happens when the worksheet changes. Opening the
workbook does not change a worksheet, so worksheet_change is not going
to happen.
Looking at your code, you are not using a worksheet_change event at
all. You can simply call an ordinary sub.
try this:

Private Sub workbook_open()
Call filterthing
End Sub

Sub filterthing()
With Sheet1
.AutoFilterMode = False
.Range("A10:M5000").AutoFilter
.Range("A10:M5000").AutoFilter Field:=5,
Criteria1:=Range("h3").Text
End With
End Sub

The sub filterthing() goes in an ordinary code module. Open the VB
editor and do Insert, Module. Put the filterthing() code in there. The
workbook_open code goes in the ThisWorkbook code module.
There are four levels of Module:
Thisworkbook module - subs in here called when some classes of event
happen at workbook level e.g. workbook opens or sheet changes
Sheet Module - subs in here called when some classes of event happen
at worksheet level e.g. the worksheet changes or a cell selection is
made
normal code module - These contain the vast bulk of your code. You can
insert as many as you want to group code sections together.They do not
require any event in the workbook or worksheet to happen for them to
run. All the macros you might assign to a button or toolbar will be in
here.
class modules - spend enough time coding with the above three modules
and you will find situations that just don't seem elegently done using
them, or even possible. Class modules then make sense.

regards
Paul

On Feb 25, 7:41 am, "Chris" wrote:
Hi,

I'm having problems with the code below, and wondered if anybody had any
ideas.

Basically, the code is supposed to tell an autofilter which value to
filter
on. This is supposed to be prompted when a certain cell (H3) has the value
entered into it. And the code is supposed to run automatically when the
spreadsheet is opened.

I'm happy with the autofilter part of the code, and (when I take out the
part that is supposed to update the autofilter when a new value is entered
in H3), I'm also happy with the part which makes the code run when the
spreadsheet is opened. My question therefore is: what is wrong about the
worksheet_change part of the function? (And am I calling it correctly from
workbook_open?)

I should point out that I'm a bit of a newbie, so please assume I don't
know
anything if you answer!

Private Sub workbook_open()
Call worksheet_change
End Sub

Public Sub worksheet_change(ByVal target As Range)
End Sub
If Intersect(target, Range("h3")) Is Nothing Then
Exit Sub
Else
Sub filterthing()
With Sheet1

.AutoFilterMode = False
.Range("A10:M5000").AutoFilter
.Range("A10:M5000").AutoFilter Field:=5, Criteria1:=Range("h3").Text

End With

End Sub

Many thanks

Hi
In that case you can just call filterthing() from the worksheet_change
event

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("H3").Address Then
Call filterthing
End If
End Sub

The change will kick in when H3 is changed.
This event goes in the code module for sheet1.
regards
Paul

On Feb 25, 6:38*pm, "Chris" wrote:
Hi Paul,

Thanks for your advice - just one thing though: *the reason I thought I
needed to use worksheet_change was that I wanted filterthing() to run each
time cell H3 had new data entered into it. *Currently autofilter is only
setting itself to the value in H3 when the workbook is opened.

Is this easily achieveable?

Many thanks

Chris

wrote in message

...
Hi
Worksheet_Change happens when the worksheet changes. Opening the
workbook does not change a worksheet, so worksheet_change is not going
to happen.
Looking at your code, you are not using a worksheet_change event at
all. You can simply call an ordinary sub.
try this:

Private Sub workbook_open()
*Call filterthing
End Sub

Sub filterthing()
With Sheet1
* * .AutoFilterMode = False
* * .Range("A10:M5000").AutoFilter
* * .Range("A10:M5000").AutoFilter Field:=5,
Criteria1:=Range("h3").Text
End With
End Sub

The sub filterthing() goes in an ordinary code module. Open the VB
editor and do Insert, Module. Put the filterthing() code in there. The
workbook_open code goes in the ThisWorkbook code module.
There are four levels of Module:
Thisworkbook module - subs in here called when some classes of event
happen at workbook level e.g. workbook opens or sheet changes
Sheet Module - *subs in here called when some classes of event happen
at worksheet level e.g. the worksheet changes or a cell selection is
made
normal code module - These contain the vast bulk of your code. You can
insert as many as you want to group code sections together.They do not
require any event in the workbook or worksheet to happen for them to
run. All the macros you might assign to a button or toolbar will be in
here.
class modules - spend enough time coding with the above three modules
and you will find situations that just don't seem elegently done using
them, or even possible. Class modules then make sense.

regards
Paul

On Feb 25, 7:41 am, "Chris" wrote:



Hi,

I'm having problems with the code below, and wondered if anybody had any
ideas.

Basically, the code is supposed to tell an autofilter which value to
filter
on. This is supposed to be prompted when a certain cell (H3) has the value
entered into it. And the code is supposed to run automatically when the
spreadsheet is opened.

I'm happy with the autofilter part of the code, and (when I take out the
part that is supposed to update the autofilter when a new value is entered
in H3), I'm also happy with the part which makes the code run when the
spreadsheet is opened. My question therefore is: what is wrong about the
worksheet_change part of the function? (And am I calling it correctly from
workbook_open?)

I should point out that I'm a bit of a newbie, so please assume I don't
know
anything if you answer!

Private Sub workbook_open()
Call worksheet_change
End Sub

Public Sub worksheet_change(ByVal target As Range)
End Sub
If Intersect(target, Range("h3")) Is Nothing Then
Exit Sub
Else
Sub filterthing()
With Sheet1

.AutoFilterMode = False
.Range("A10:M5000").AutoFilter
.Range("A10:M5000").AutoFilter Field:=5, Criteria1:=Range("h3").Text

End With

End Sub

Many thanks- Hide quoted text -

- Show quoted text -