worksheet change
 

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

worksheet change
 

The workbook open macro should not call the worksheet change macro,
--
Gary''s Student - gsnu2007d


"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

worksheet change
 

Chris
That's not going to do it. The Workbook_Open macro fires when the
workbook is opened, as you know. The Worksheet_Change macro fires whenever
the entry in any cell in the entire sheet is changed. You cannot fire
either one of these macros from another macro.
Post back and provide more detail about what you want to happen and when you
want it to happen. You say that you want it to happen when H3 has a new
value placed into it but you want this to happen when the workbook is
opened. Obviously you can't have a new value in H3 when the workbook is
opened unless the Workbook_Open macro puts it there itself. HTH Otto
"Chris" wrote in message
...
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

worksheet change
 

The Worksheet_Change event can only be properly executed when a change is
made to the sheet behind which the code resides. If you want the code to run
on workbook_open, then put the code into the standard code module with a
different procedure name and call that name from the workbook_open event.
Or, you could make the procedure part of the workbook_open event. Don't
forget, if the code was written to trigger only on a worksheet change, you
might have to do some rewrite to the code to make it work from the other
locations.


"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