Spreadsheet updating question
 

Hi

I am trying to update a spreadsheet from a9 to z(infinity). If the
user clicks or enters data into a cell then id like some checks to be
made within the row and maybe calculations to be made. I am able to
do this when data is entered into a specific column with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("h:H")) Is Nothing Then
If IsEmpty(Range("p" & Target.Row).Value) Then...

I also have this statement for the P column

What I would like to do is if the user clicks are changes/adds data to
a cell then have the spreadsheet automatically update based on
criteria.
I have tried the selection change but sometimes errors occur when i
manipulate things

Could someone point me into the right direction on how to go about
this?

Thanks
Susan

Spreadsheet updating question
 

Based on your description, Change rather than SelectionChange would be the
appropriate event to use.

If your code would then make changes to the worksheet, you should disable
events

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
If Not Intersect(Target, Columns("h:H")) Is Nothing Then
If IsEmpty(Range("p" & Target.Row).Value) Then
Application.EnableEvents = False
' code to do updates

End if
End if
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"susan" wrote in message
...
Hi

I am trying to update a spreadsheet from a9 to z(infinity). If the
user clicks or enters data into a cell then id like some checks to be
made within the row and maybe calculations to be made. I am able to
do this when data is entered into a specific column with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("h:H")) Is Nothing Then
If IsEmpty(Range("p" & Target.Row).Value) Then...

I also have this statement for the P column

What I would like to do is if the user clicks are changes/adds data to
a cell then have the spreadsheet automatically update based on
criteria.
I have tried the selection change but sometimes errors occur when i
manipulate things

Could someone point me into the right direction on how to go about
this?

Thanks
Susan

Spreadsheet updating question
 

Hi Tom
Thanks for your answer

I may have mislead you. If the user selects for example row 4 col 2
then the spreadsheet would perform an expiration date check and
change/unchange a cell in row 4 col 20. I think the "If Not
Intersect(Target, Columns("P:P")) Is Nothing Then" is just waiting for
col P to change. I would like to make the spreadsheet invoke changes
when the user clicks on any column as checks are made across the data
in the row.
Is it possible to put something like columns("A:Z")

Thanks again
Susan



On Sun, 22 Jan 2006 17:09:11 -0500, "Tom Ogilvy"
wrote:

Based on your description, Change rather than SelectionChange would be the
appropriate event to use.

If your code would then make changes to the worksheet, you should disable
events

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
If Not Intersect(Target, Columns("h:H")) Is Nothing Then
If IsEmpty(Range("p" & Target.Row).Value) Then
Application.EnableEvents = False
' code to do updates

End if
End if
ErrHandler:
Application.EnableEvents = True
End Sub

Spreadsheet updating question
 

If target.Column <= 26 then
' do something

--
Regards,
Tom Ogilvy

"susan" wrote in message
...
Hi Tom
Thanks for your answer

I may have mislead you. If the user selects for example row 4 col 2
then the spreadsheet would perform an expiration date check and
change/unchange a cell in row 4 col 20. I think the "If Not
Intersect(Target, Columns("P:P")) Is Nothing Then" is just waiting for
col P to change. I would like to make the spreadsheet invoke changes
when the user clicks on any column as checks are made across the data
in the row.
Is it possible to put something like columns("A:Z")

Thanks again
Susan



On Sun, 22 Jan 2006 17:09:11 -0500, "Tom Ogilvy"
wrote:

Based on your description, Change rather than SelectionChange would be
the
appropriate event to use.

If your code would then make changes to the worksheet, you should disable
events

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
If Not Intersect(Target, Columns("h:H")) Is Nothing Then
If IsEmpty(Range("p" & Target.Row).Value) Then
Application.EnableEvents = False
' code to do updates

End if
End if
ErrHandler:
Application.EnableEvents = True
End Sub