Spreadsheet updating question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

susan - 22 Jan 2006 17:17 GMT

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

Reply to this Message

Tom Ogilvy - 22 Jan 2006 22:09 GMT

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

Signature

Regards,
Tom Ogilvy

> Hi
>
[quoted text clipped - 20 lines]
> Thanks
> Susan

Reply to this Message

susan - 23 Jan 2006 05:48 GMT

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

>Based on your description, Change rather than SelectionChange would be the
>appropriate event to use.
[quoted text clipped - 14 lines]
>Application.EnableEvents = True
>End Sub

Reply to this Message

Tom Ogilvy - 24 Jan 2006 02:21 GMT

If target.Column <= 26 then
' do something

Signature

Regards,
Tom Ogilvy

> Hi Tom
> Thanks for your answer
[quoted text clipped - 29 lines]
> >Application.EnableEvents = True
> >End Sub

Reply to this Message