Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick VBA Worksheet Change Event or Selection Question:
Quick VBA Worksheet Change Event or Selection Question:
I have a workbook that has a sheet named "Formatted Prices". I enter data starting from row 7 & from column A to S all the way down on a daily basis. This range is not completely filled on a daily basis…, somedays I might just enter 2 or 3 data only filling 3 rows, but always from column A to S. In column Q, I have a formula that depends on column P…, it basically a simple calculation formula that says if the data in say P7 is empty, then show nothing, but if the data in P7 is something, then subtract today's date from P7. The data in P7 is a date and has a date format. So if I enter today's date in cell P7, Q7 should show 0, If I enter 11/16/2009 in cell P7, Q7 should show 1…etc. down the column. The problem is that this sheet is used by multiple people and they often get rid of this simple code. So what happens is I have to come in and update the code every single time!! I don't want to have to lock this column because of other issues. As a result, I have decided that a VBA worksheet change event or selection will be best. So how can I create a VBA worksheet code that will automatically populate this formula in column Q as data is enter in column P? Example: If both columns P & Q are blank, and data are been entered in other columns, then nothing should happen. If data is entered in P2, then the worksheet VBA code should auto populate the code (explained above) into Q2, but nothing in the other columns below it…., so they can stay blank. If data is entered in P3, then the worksheet VBA code should auto populate the code (explained above) into Q3, but nothing in the other columns below…, so they can stay blank. …ect. I thank you for your patience and assistance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick VBA Worksheet Change Event or Selection Question:
Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myC As Range If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myC In Intersect(Target, Range("P:P")) Range("Q" & myC.Row).Value = Date - Target.Value Next myC Application.EnableEvents = True End Sub "Damil4real" wrote in message ... Quick VBA Worksheet Change Event or Selection Question: I have a workbook that has a sheet named "Formatted Prices". I enter data starting from row 7 & from column A to S all the way down on a daily basis. This range is not completely filled on a daily basis…, somedays I might just enter 2 or 3 data only filling 3 rows, but always from column A to S. In column Q, I have a formula that depends on column P…, it basically a simple calculation formula that says if the data in say P7 is empty, then show nothing, but if the data in P7 is something, then subtract today's date from P7. The data in P7 is a date and has a date format. So if I enter today's date in cell P7, Q7 should show 0, If I enter 11/16/2009 in cell P7, Q7 should show 1…etc. down the column. The problem is that this sheet is used by multiple people and they often get rid of this simple code. So what happens is I have to come in and update the code every single time!! I don't want to have to lock this column because of other issues. As a result, I have decided that a VBA worksheet change event or selection will be best. So how can I create a VBA worksheet code that will automatically populate this formula in column Q as data is enter in column P? Example: If both columns P & Q are blank, and data are been entered in other columns, then nothing should happen. If data is entered in P2, then the worksheet VBA code should auto populate the code (explained above) into Q2, but nothing in the other columns below it…., so they can stay blank. If data is entered in P3, then the worksheet VBA code should auto populate the code (explained above) into Q3, but nothing in the other columns below…, so they can stay blank. …ect. I thank you for your patience and assistance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick VBA Worksheet Change Event or Selection Question:
'Macro based on worksheet change
Private Sub Worksheet_Change(ByVal Target As Range) 'Don't do anything unless needed If Intersect(Range("P:P"), Target) Is Nothing _ Or Target.Count 1 Then Exit Sub 'Don't do anything if cell is blank If Target.Value = "" Then Exit Sub Application.EnableEvents = False 'Create value: Target.Offset(0, 1).FormulaR1C1 = _ "=IF(RC[-1]="""","""",TODAY()-RC[-1])" Application.EnableEvents = True End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Damil4real" wrote: Quick VBA Worksheet Change Event or Selection Question: I have a workbook that has a sheet named "Formatted Prices". I enter data starting from row 7 & from column A to S all the way down on a daily basis. This range is not completely filled on a daily basis€¦, somedays I might just enter 2 or 3 data only filling 3 rows, but always from column A to S. In column Q, I have a formula that depends on column P€¦, it basically a simple calculation formula that says if the data in say P7 is empty, then show nothing, but if the data in P7 is something, then subtract today's date from P7. The data in P7 is a date and has a date format. So if I enter today's date in cell P7, Q7 should show 0, If I enter 11/16/2009 in cell P7, Q7 should show 1€¦etc. down the column. The problem is that this sheet is used by multiple people and they often get rid of this simple code. So what happens is I have to come in and update the code every single time!! I don't want to have to lock this column because of other issues. As a result, I have decided that a VBA worksheet change event or selection will be best. So how can I create a VBA worksheet code that will automatically populate this formula in column Q as data is enter in column P? Example: If both columns P & Q are blank, and data are been entered in other columns, then nothing should happen. If data is entered in P2, then the worksheet VBA code should auto populate the code (explained above) into Q2, but nothing in the other columns below it€¦., so they can stay blank. If data is entered in P3, then the worksheet VBA code should auto populate the code (explained above) into Q3, but nothing in the other columns below€¦, so they can stay blank. €¦ect. I thank you for your patience and assistance! . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick VBA Worksheet Change Event or Selection Question:
On 17 Nov, 12:13, Luke M wrote:
'Macro based on worksheet change Private Sub Worksheet_Change(ByVal Target As Range) 'Don't do anything unless needed If Intersect(Range("P:P"), Target) Is Nothing _ * * Or Target.Count 1 Then Exit Sub 'Don't do anything if cell is blank If Target.Value = "" Then Exit Sub Application.EnableEvents = False 'Create value: Target.Offset(0, 1).FormulaR1C1 = _ * * "=IF(RC[-1]="""","""",TODAY()-RC[-1])" Application.EnableEvents = True End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Damil4real" wrote: Quick VBA Worksheet Change Event or Selection Question: I have a workbook that has a sheet named "Formatted Prices". I enter data starting from row 7 & from column A to S all the way down on a daily basis. This range is not completely filled on a daily basis…, somedays I might just enter 2 or 3 data only filling 3 rows, but always from column A to S. In column Q, I have a formula that depends on column P…, it basically a simple calculation formula that says if the data in say P7 is empty, then show nothing, but if the data in P7 is something, then subtract today's date from P7. The data in P7 is a date and has a date format. So if I enter today's date in cell P7, Q7 should show 0, If I enter 11/16/2009 in cell P7, Q7 should show 1…etc. down the column. The problem is that this sheet is used by multiple people and they often get rid of this simple code. So what happens is I have to come in and update the code every single time!! I don't want to have to lock this column because of other issues. As a result, I have decided that a VBA worksheet change event or selection will be best. So how can I create a VBA worksheet code that will automatically populate this formula in column Q as data is enter in column P? Example: If both columns P & Q are blank, and data are been entered in other columns, then nothing should happen. If data is entered in P2, then the worksheet VBA code should auto populate the code (explained above) *into Q2, but nothing in the other columns below it…., so they can stay blank. If data is entered in P3, then the worksheet VBA code should auto populate the code (explained above) into Q3, but nothing in the other columns below…, so they can stay blank. …ect. I thank you for your patience and assistance! .- Hide quoted text - - Show quoted text - Bernie & Luke, I thank you very much for the code! Worked like a charm!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick VBA Worksheet Change Event or Selection Question:
You should be aware that my code puts values into the cell, and Luke's codes puts in a formula. The
formula will return a different result on subsequent days - it wasn't clear whether you wanted that behavior or not.... HTH, Bernie MS Excel MVP "Damil4real" wrote in message ... On 17 Nov, 12:13, Luke M wrote: 'Macro based on worksheet change Private Sub Worksheet_Change(ByVal Target As Range) 'Don't do anything unless needed If Intersect(Range("P:P"), Target) Is Nothing _ Or Target.Count 1 Then Exit Sub 'Don't do anything if cell is blank If Target.Value = "" Then Exit Sub Application.EnableEvents = False 'Create value: Target.Offset(0, 1).FormulaR1C1 = _ "=IF(RC[-1]="""","""",TODAY()-RC[-1])" Application.EnableEvents = True End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Damil4real" wrote: Quick VBA Worksheet Change Event or Selection Question: I have a workbook that has a sheet named "Formatted Prices". I enter data starting from row 7 & from column A to S all the way down on a daily basis. This range is not completely filled on a daily basis…, somedays I might just enter 2 or 3 data only filling 3 rows, but always from column A to S. In column Q, I have a formula that depends on column P…, it basically a simple calculation formula that says if the data in say P7 is empty, then show nothing, but if the data in P7 is something, then subtract today's date from P7. The data in P7 is a date and has a date format. So if I enter today's date in cell P7, Q7 should show 0, If I enter 11/16/2009 in cell P7, Q7 should show 1…etc. down the column. The problem is that this sheet is used by multiple people and they often get rid of this simple code. So what happens is I have to come in and update the code every single time!! I don't want to have to lock this column because of other issues. As a result, I have decided that a VBA worksheet change event or selection will be best. So how can I create a VBA worksheet code that will automatically populate this formula in column Q as data is enter in column P? Example: If both columns P & Q are blank, and data are been entered in other columns, then nothing should happen. If data is entered in P2, then the worksheet VBA code should auto populate the code (explained above) into Q2, but nothing in the other columns below it…., so they can stay blank. If data is entered in P3, then the worksheet VBA code should auto populate the code (explained above) into Q3, but nothing in the other columns below…, so they can stay blank. …ect. I thank you for your patience and assistance! .- Hide quoted text - - Show quoted text - Bernie & Luke, I thank you very much for the code! Worked like a charm!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick VBA Worksheet Change Event or Selection Question:
On 17 Nov, 12:13, Luke M wrote:
'Macro based on worksheet change Private Sub Worksheet_Change(ByVal Target As Range) 'Don't do anything unless needed If Intersect(Range("P:P"), Target) Is Nothing _ * * Or Target.Count 1 Then Exit Sub 'Don't do anything if cell is blank If Target.Value = "" Then Exit Sub Application.EnableEvents = False 'Create value: Target.Offset(0, 1).FormulaR1C1 = _ * * "=IF(RC[-1]="""","""",TODAY()-RC[-1])" Application.EnableEvents = True End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Damil4real" wrote: Quick VBA Worksheet Change Event or Selection Question: I have a workbook that has a sheet named "Formatted Prices". I enter data starting from row 7 & from column A to S all the way down on a daily basis. This range is not completely filled on a daily basis…, somedays I might just enter 2 or 3 data only filling 3 rows, but always from column A to S. In column Q, I have a formula that depends on column P…, it basically a simple calculation formula that says if the data in say P7 is empty, then show nothing, but if the data in P7 is something, then subtract today's date from P7. The data in P7 is a date and has a date format. So if I enter today's date in cell P7, Q7 should show 0, If I enter 11/16/2009 in cell P7, Q7 should show 1…etc. down the column. The problem is that this sheet is used by multiple people and they often get rid of this simple code. So what happens is I have to come in and update the code every single time!! I don't want to have to lock this column because of other issues. As a result, I have decided that a VBA worksheet change event or selection will be best. So how can I create a VBA worksheet code that will automatically populate this formula in column Q as data is enter in column P? Example: If both columns P & Q are blank, and data are been entered in other columns, then nothing should happen. If data is entered in P2, then the worksheet VBA code should auto populate the code (explained above) *into Q2, but nothing in the other columns below it…., so they can stay blank. If data is entered in P3, then the worksheet VBA code should auto populate the code (explained above) into Q3, but nothing in the other columns below…, so they can stay blank. …ect. I thank you for your patience and assistance! .- Hide quoted text - - Show quoted text - These two codes are just great and they both work like a charm!!!! What if I want to expand this code to include other columns? How can I expand the worksheet selection code above in order to incorporate the functions that I have below? I'll greatly appreciate your assistance. Data in both column A to F are manually entered. Data in column J populates when you enter data in column E. Data in column K populates when you enter data in column F. Data in columns L, M & O populates when you enter data in column E & F. The three Sheet Tabs are named: (Overview, M.A., Vacation Trip). All of these data are in the Overview tab. Excel code in each cell of each column are as follows. Code in column F is: =IF(ISNA(VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE))=TRUE, 0,VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE)) Code in column J is: =IF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5708,4,FALSE))=T RUE,0,VLOOKUP ($G7,M.A.!$A$2:$E$5708,4,FALSE)) Code in column L is: =J7-K7 Code in column M is: =IF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5392,5,FALSE))=T RUE,0,VLOOKUP ($G7,M.A.!$A$2:$E$5392,5,FALSE))-IF(ISNA(VLOOKUP($H7,'Vacation Trip'!$A $2:$C$4573,3,FALSE))=TRUE,0,VLOOKUP($H7,'Vacation Trip'!$A$2:$C $4573,3,FALSE)) Constant data in column N is 1 (no code) Code in column O is: =M7*N7 Data in column P is manually entered (DATE format) Code in column Q is: =IF(P7<"",=TODAY()-P7,"") The two codes above that I'm trying to expand was initially created to have column Q populate its code based off of the date entered in column P…, but now I'm trying to see if I can expand this code to incorporate the other codes & columns above. Thanks a great deal for your assistance!!!!! Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick VBA Worksheet Change Event or Selection Question:
Copy this code, right-Click the "Overview" tab, select view code, and paste
the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myR As Long Dim myC As Integer If Target.Cells.Count 1 Then Exit Sub myR = Target.Row myC = Target.Column Application.EnableEvents = False If myC = 5 Then 'column E Range("J" & myR).FormulaR1C1 = _ "=IF(ISNA(VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))= " _ & "TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE) )" End If If myC = 6 Then 'column F Range("K" & myR).FormulaR1C1 = _ "=IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,2,FALSE))=" _ & "TRUE,0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,2,FALSE))" End If If (myC = 5 Or myC = 6) And (Cells(myR, 5).Value < "" _ And Cells(myR, 6).Value < "") Then Range("L" & myR).FormulaR1C1 = "=RC[-2]-RC[-1]" Range("M" & myR).FormulaR1C1 = _ "=IF(ISNA(VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))= " _ & "TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE) )-" _ & "IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" _ & "=TRUE,0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" Range("N" & myR).Value = 1 Range("O" & myR).FormulaR1C1 = "=RC[-2]*RC[-1]" End If If myC = 16 Then Range("Q" & myR).FormulaR1C1 = "=IF(RC[-1]<"""",TODAY()-RC[-1],"""")" End If Application.EnableEvents = True End Sub "Damil4real" wrote in message ... On 17 Nov, 12:13, Luke M wrote: 'Macro based on worksheet change Private Sub Worksheet_Change(ByVal Target As Range) 'Don't do anything unless needed If Intersect(Range("P:P"), Target) Is Nothing _ Or Target.Count 1 Then Exit Sub 'Don't do anything if cell is blank If Target.Value = "" Then Exit Sub Application.EnableEvents = False 'Create value: Target.Offset(0, 1).FormulaR1C1 = _ "=IF(RC[-1]="""","""",TODAY()-RC[-1])" Application.EnableEvents = True End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Damil4real" wrote: Quick VBA Worksheet Change Event or Selection Question: I have a workbook that has a sheet named "Formatted Prices". I enter data starting from row 7 & from column A to S all the way down on a daily basis. This range is not completely filled on a daily basis…, somedays I might just enter 2 or 3 data only filling 3 rows, but always from column A to S. In column Q, I have a formula that depends on column P…, it basically a simple calculation formula that says if the data in say P7 is empty, then show nothing, but if the data in P7 is something, then subtract today's date from P7. The data in P7 is a date and has a date format. So if I enter today's date in cell P7, Q7 should show 0, If I enter 11/16/2009 in cell P7, Q7 should show 1…etc. down the column. The problem is that this sheet is used by multiple people and they often get rid of this simple code. So what happens is I have to come in and update the code every single time!! I don't want to have to lock this column because of other issues. As a result, I have decided that a VBA worksheet change event or selection will be best. So how can I create a VBA worksheet code that will automatically populate this formula in column Q as data is enter in column P? Example: If both columns P & Q are blank, and data are been entered in other columns, then nothing should happen. If data is entered in P2, then the worksheet VBA code should auto populate the code (explained above) into Q2, but nothing in the other columns below it…., so they can stay blank. If data is entered in P3, then the worksheet VBA code should auto populate the code (explained above) into Q3, but nothing in the other columns below…, so they can stay blank. …ect. I thank you for your patience and assistance! .- Hide quoted text - - Show quoted text - These two codes are just great and they both work like a charm!!!! What if I want to expand this code to include other columns? How can I expand the worksheet selection code above in order to incorporate the functions that I have below? I'll greatly appreciate your assistance. Data in both column A to F are manually entered. Data in column J populates when you enter data in column E. Data in column K populates when you enter data in column F. Data in columns L, M & O populates when you enter data in column E & F. The three Sheet Tabs are named: (Overview, M.A., Vacation Trip). All of these data are in the Overview tab. Excel code in each cell of each column are as follows. Code in column F is: =IF(ISNA(VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE))=TRUE, 0,VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE)) Code in column J is: =IF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5708,4,FALSE))=T RUE,0,VLOOKUP ($G7,M.A.!$A$2:$E$5708,4,FALSE)) Code in column L is: =J7-K7 Code in column M is: =IF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5392,5,FALSE))=T RUE,0,VLOOKUP ($G7,M.A.!$A$2:$E$5392,5,FALSE))-IF(ISNA(VLOOKUP($H7,'Vacation Trip'!$A $2:$C$4573,3,FALSE))=TRUE,0,VLOOKUP($H7,'Vacation Trip'!$A$2:$C $4573,3,FALSE)) Constant data in column N is 1 (no code) Code in column O is: =M7*N7 Data in column P is manually entered (DATE format) Code in column Q is: =IF(P7<"",=TODAY()-P7,"") The two codes above that I'm trying to expand was initially created to have column Q populate its code based off of the date entered in column P…, but now I'm trying to see if I can expand this code to incorporate the other codes & columns above. Thanks a great deal for your assistance!!!!! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use selection change instead of double click event? | Excel Discussion (Misc queries) | |||
Worksheet change Event | Excel Worksheet Functions | |||
Worksheet Change event | Excel Discussion (Misc queries) | |||
Worksheet Change Event | Excel Discussion (Misc queries) | |||
Worksheet Change Event | Excel Discussion (Misc queries) |