Hi

I am attempting to run a macro when the value of a drop down field changes.

The macro, Refresh1, is detailed below and it works fine if I run it via
Tools/Macro/Macro/Run.

Sub Refresh1()

Application.ScreenUpdating = False
Application.Calculation = xlManual

ActiveSheet.ShowAllData

Range("A4:B2000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Application.Run "HideBlankRows"
Range("C1").Select
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub


However it does not operate when the macro is run via a change in the drop
down field. The code is below. It appears that the Show All Data and
HideBlankRows commands are not working. Any suggestions to clarify my error
would be appreciated.

Thanks

Monk


Private Sub Worksheet_Change(ByVal Target As Range)



If Target.Address = "$c$1" Then Refresh1


End Sub

Hi,

I think you just need to capitalize the "c" in your range address.

I tried it that way and it works.

Thanks
Ariel


"Monk" wrote in message
...
Hi

I am attempting to run a macro when the value of a drop down field
changes.

The macro, Refresh1, is detailed below and it works fine if I run it via
Tools/Macro/Macro/Run.

Sub Refresh1()

Application.ScreenUpdating = False
Application.Calculation = xlManual

ActiveSheet.ShowAllData

Range("A4:B2000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Application.Run "HideBlankRows"
Range("C1").Select
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub


However it does not operate when the macro is run via a change in the drop
down field. The code is below. It appears that the Show All Data and
HideBlankRows commands are not working. Any suggestions to clarify my
error
would be appreciated.

Thanks

Monk


Private Sub Worksheet_Change(ByVal Target As Range)



If Target.Address = "$c$1" Then Refresh1


End Sub

Thanks Ariel. Didn't realise it was that fussy!!. Cheers

"Ariel Dugan" wrote:

Hi,

I think you just need to capitalize the "c" in your range address.

I tried it that way and it works.

Thanks
Ariel


"Monk" wrote in message
...
Hi

I am attempting to run a macro when the value of a drop down field
changes.

The macro, Refresh1, is detailed below and it works fine if I run it via
Tools/Macro/Macro/Run.

Sub Refresh1()

Application.ScreenUpdating = False
Application.Calculation = xlManual

ActiveSheet.ShowAllData

Range("A4:B2000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Application.Run "HideBlankRows"
Range("C1").Select
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub


However it does not operate when the macro is run via a change in the drop
down field. The code is below. It appears that the Show All Data and
HideBlankRows commands are not working. Any suggestions to clarify my
error
would be appreciated.

Thanks

Monk


Private Sub Worksheet_Change(ByVal Target As Range)



If Target.Address = "$c$1" Then Refresh1


End Sub