Run macro automatically when a cell in a dynamic range changes

[glimmer23]

Hi

my problem is hopefully pretty simple.

my code so far looks like this:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
If Union(target, Range("$B$76:$B$114")).Address = "$B$76:$B$114" Then
Run ("Model.Hide")
End If
Application.ScreenUpdating = True
End Sub

which works fine for the range $B$76:$B$114 however the row numbers are
prone to change.

I have setup a table thats location will not change, which will store
the reference start and the reference ends. This is located at B8
(start) and C8 (End).

what i am tring to do is get the information from B8 and C8 to replace
the B76 and B114 respectively.

any ideas?

 

[Norman Jones]

Hi Glimmer23.

Try:

Private Sub Worksheet_Change(ByVal target As Range)

Dim startCell As Range
Dim endCell As Range
Dim rng As Range

Set startCell = Me.Range(Me.Range("B8").Value)
Set endCell = Me.Range(Me.Range("C8").Value)

Set rng = Range(startCell, endCell)

If Not Intersect(target, rng) Is Nothing Then
Application.ScreenUpdating = False
Run "Model.MyHide"
End If
Application.ScreenUpdating = True

End Sub

I have intentionally changed the name of the called macro from Hide to
MyHide, because 'Hide' as a special significance for VBA and and the
potential subsequent confusion may cause subtle problems.


---
Regards,
Norman

 

[Bob Phillips]

sRange = b78 & ":" & C78
If Not Intersect(target, Range(sRange)) Is Nothing Then

 

[glimmer23]

YOUR THE KING - been tring to get that for ages works as just hide -
but ill see if by change both to my hide if it speeds it up

thank you very much for your help

 

[Norman Jones]

Hi Glimmer23,

been tring to get that for ages works as just hide -
but ill see if by change both to my hide if it speeds it up

Using 'MyHide' (instead of 'Hide') will not increase execution speed. It is
offered, purely as advice, to prevent VBA from being confused later when,
perhaps, your code becomes more complex and more difficult to debug.

As a general rule of thumb, it is adivisable to avoid the use of VBA
reserved words in any elective naming process.

 

[Bob Phillips]

oops, should be

sRange = range("b78").Value & ":" & range("C78")
If Not Intersect(target, Range(sRange)) Is Nothing Then

 

[glimmer23]

Ya didnt relise it was a reserved word - but i know that rule well and
changed it after i made my last post to MyHide because i have run into
reserved name problems before...

thanks again for your help.