Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with updating spreadsheet | New Users to Excel | |||
help with updating spreadsheet | Excel Discussion (Misc queries) | |||
updating spreadsheet. | Excel Programming | |||
Updating 1 spreadsheet from another | Excel Worksheet Functions | |||
updating spreadsheet | Excel Programming |