Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
Hi,
I need to run a procedure when the value of a specific cell changes.. How would I do that? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target ' do your stuff End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "R Tanner" wrote in message ... Hi, I need to run a procedure when the value of a specific cell changes.. How would I do that? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
You are talking about Event code. Right click the sheet tab you want to catch
the events on and select View Code. The VBE will open. Just above the code window to the left is a drop down of the Object. Change it from General to Worksheet. A code stub will be created for you base on selection change. In the drop down next to the one you just changed is a list of the events. Select Change and you will get a code stub something like this... Private Sub Worksheet_Change(ByVal Target As Range) End Sub Target is the cell that was changed so just do something like this... Private Sub Worksheet_Change(ByVal Target As Range) if target.Address = "$A$1" then msgbox "Tada" End Sub -- HTH... Jim Thomlinson "R Tanner" wrote: Hi, I need to run a procedure when the value of a specific cell changes.. How would I do that? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
Okay I actually Did something different, which I have listed below...
The problem is I showed it to our trade analysts and they liked it. lol ( wasn't expected). So to modify, there are some changes I would need to make to this. To give you a thorough idea, I have a series of indicators that update a table of mine. I need to record each indicator's table of data (which will update when the value of that cell changes) in a separate workbook. It would be extremely inefficient to have 19 different workbooks open recording data at the same time. So, would I be able to record data to a closed excel workbook? Or maybe I can have 1 workbook open and have the data written to different csv files which I could then import into different workbooks with a small macro... What do you think? Private Sub Worksheet_Calculate() Dim R1 As Range Dim I As Integer Dim CR1 As Range Dim R2 As Range Dim CR2 As Range Dim I2 As Integer Dim R3 As Range Dim CR3 As Range Dim I3 As Integer Dim R4 As Range Dim CR4 As Range Dim I4 As Integer Dim R5 As Range Dim CR5 As Range Dim I5 As Integer Dim R6 As Range Dim CR6 As Range Dim I6 As Integer Set R1 = Range("C4") Set CR1 = Range("J4") Set R2 = Range("D4") Set CR2 = Range("L4") Set R3 = Range("C5") Set CR3 = Range("O4") Set R4 = Range("D5") Set CR4 = Range("Q4") Set R5 = Range("C6") Set CR5 = Range("T4") Set R6 = Range("D6") Set CR6 = Range("V4") If R1.Value < CR1.End(xlDown).Value Then I = Range(CR1, CR1.End(xlDown)).Rows.Count CR1.Offset(I, 0).Value = R1.Value CR1.Offset(I, -1) = Time End If If R2.Value < CR2.End(xlDown).Value Then I2 = Range(CR2, CR2.End(xlDown)).Rows.Count CR2.Offset(I2, 0).Value = R2.Value CR2.Offset(I2, -1) = Time End If If R3.Value < CR3.End(xlDown).Value Then I3 = Range(CR3, CR3.End(xlDown)).Rows.Count CR3.Offset(I3, 0).Value = R3.Value CR3.Offset(I3, -1) = Time End If If R4.Value < CR4.End(xlDown).Value Then I4 = Range(CR4, CR4.End(xlDown)).Rows.Count CR4.Offset(I4, 0).Value = R4.Value CR4.Offset(I4, -1) = Time End If If R5.Value < CR5.End(xlDown).Value Then I5 = Range(CR5, CR5.End(xlDown)).Rows.Count CR5.Offset(I5, 0).Value = R5.Value CR5.Offset(I5, -1) = Time End If If R6.Value < CR6.End(xlDown).Value Then I6 = Range(CR6, CR6.End(xlDown)).Rows.Count CR6.Offset(I6, 0).Value = R6.Value CR6.Offset(I6, -1) = Time End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
On Sep 15, 10:26*am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: You are talking about Event code. Right click the sheet tab you want to catch the events on and select View Code. The VBE will open. Just above the code window to the left is a drop down of the Object. Change it from General to Worksheet. A code stub will be created for you base on selection change. In the drop down next to the one you just changed is a list of the events. Select Change and you will get a code stub something like this... Private Sub Worksheet_Change(ByVal Target As Range) End Sub Target is the cell that was changed so just do something like this... Private Sub Worksheet_Change(ByVal Target As Range) * if target.Address = "$A$1" then msgbox "Tada" End Sub -- HTH... Jim Thomlinson "R Tanner" wrote: Hi, I need to run a procedure when the value of a specific cell changes.. How would I do that?- Hide quoted text - - Show quoted text - This didn't work...It doesn't do anything...This is the code I used and it does not work... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MsgBox "tada!" End Sub Right now what I am thinking I could do is write each of my different indicators to a separate sheet and then, once the data in each sheet reaches a specific row, I will copy it to a workbook specifically for that indicator and then repeat the process, appending the new data to the bottom of the workbook, closing it back down, and continuing until the end of the day. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
If it was me I would write the data to a database such as Access. Then you
can use XL as your front end or you can use Access to get at the data. The nice thing about using a database is that it will allow concurrent access to the data. Take a look for ADODB.Recordset to get some examples of reading from and writing to a database... -- HTH... Jim Thomlinson "R Tanner" wrote: Okay I actually Did something different, which I have listed below... The problem is I showed it to our trade analysts and they liked it. lol ( wasn't expected). So to modify, there are some changes I would need to make to this. To give you a thorough idea, I have a series of indicators that update a table of mine. I need to record each indicator's table of data (which will update when the value of that cell changes) in a separate workbook. It would be extremely inefficient to have 19 different workbooks open recording data at the same time. So, would I be able to record data to a closed excel workbook? Or maybe I can have 1 workbook open and have the data written to different csv files which I could then import into different workbooks with a small macro... What do you think? Private Sub Worksheet_Calculate() Dim R1 As Range Dim I As Integer Dim CR1 As Range Dim R2 As Range Dim CR2 As Range Dim I2 As Integer Dim R3 As Range Dim CR3 As Range Dim I3 As Integer Dim R4 As Range Dim CR4 As Range Dim I4 As Integer Dim R5 As Range Dim CR5 As Range Dim I5 As Integer Dim R6 As Range Dim CR6 As Range Dim I6 As Integer Set R1 = Range("C4") Set CR1 = Range("J4") Set R2 = Range("D4") Set CR2 = Range("L4") Set R3 = Range("C5") Set CR3 = Range("O4") Set R4 = Range("D5") Set CR4 = Range("Q4") Set R5 = Range("C6") Set CR5 = Range("T4") Set R6 = Range("D6") Set CR6 = Range("V4") If R1.Value < CR1.End(xlDown).Value Then I = Range(CR1, CR1.End(xlDown)).Rows.Count CR1.Offset(I, 0).Value = R1.Value CR1.Offset(I, -1) = Time End If If R2.Value < CR2.End(xlDown).Value Then I2 = Range(CR2, CR2.End(xlDown)).Rows.Count CR2.Offset(I2, 0).Value = R2.Value CR2.Offset(I2, -1) = Time End If If R3.Value < CR3.End(xlDown).Value Then I3 = Range(CR3, CR3.End(xlDown)).Rows.Count CR3.Offset(I3, 0).Value = R3.Value CR3.Offset(I3, -1) = Time End If If R4.Value < CR4.End(xlDown).Value Then I4 = Range(CR4, CR4.End(xlDown)).Rows.Count CR4.Offset(I4, 0).Value = R4.Value CR4.Offset(I4, -1) = Time End If If R5.Value < CR5.End(xlDown).Value Then I5 = Range(CR5, CR5.End(xlDown)).Rows.Count CR5.Offset(I5, 0).Value = R5.Value CR5.Offset(I5, -1) = Time End If If R6.Value < CR6.End(xlDown).Value Then I6 = Range(CR6, CR6.End(xlDown)).Rows.Count CR6.Offset(I6, 0).Value = R6.Value CR6.Offset(I6, -1) = Time End If End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
On Sep 15, 12:13*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: If it was me I would write the data to a database such as Access. Then you can use XL as your front end or you can use Access to get at the data. The nice thing about using a database is that it will allow concurrent access to the data. Take a look for ADODB.Recordset to get some examples of reading from and writing to a database... -- HTH... Jim Thomlinson "R Tanner" wrote: Okay I actually Did something different, which I have listed below... The problem is I showed it to our trade analysts and they liked it. lol ( wasn't expected). *So to modify, there are some changes I would need to make to this. To give you a thorough idea, I have a series of indicators that update a table of mine. *I need to record each indicator's table of data (which will update when the value of that cell changes) in a separate workbook. *It would be extremely inefficient to have 19 different workbooks open recording data at the same time. *So, would I be able to record data to a closed excel workbook? *Or maybe I can have 1 workbook open and have the data written to different csv files which I could then import into different workbooks with a small macro... What do you think? Private Sub Worksheet_Calculate() Dim R1 As Range Dim I As Integer Dim CR1 As Range Dim R2 As Range Dim CR2 As Range Dim I2 As Integer Dim R3 As Range Dim CR3 As Range Dim I3 As Integer Dim R4 As Range Dim CR4 As Range Dim I4 As Integer Dim R5 As Range Dim CR5 As Range Dim I5 As Integer Dim R6 As Range Dim CR6 As Range Dim I6 As Integer Set R1 = Range("C4") Set CR1 = Range("J4") Set R2 = Range("D4") Set CR2 = Range("L4") Set R3 = Range("C5") Set CR3 = Range("O4") Set R4 = Range("D5") Set CR4 = Range("Q4") Set R5 = Range("C6") Set CR5 = Range("T4") Set R6 = Range("D6") Set CR6 = Range("V4") If R1.Value < CR1.End(xlDown).Value Then * * I = Range(CR1, CR1.End(xlDown)).Rows.Count * * CR1.Offset(I, 0).Value = R1.Value * * CR1.Offset(I, -1) = Time End If If R2.Value < CR2.End(xlDown).Value Then * * I2 = Range(CR2, CR2.End(xlDown)).Rows.Count * * CR2.Offset(I2, 0).Value = R2.Value * * CR2.Offset(I2, -1) = Time End If If R3.Value < CR3.End(xlDown).Value Then * * I3 = Range(CR3, CR3.End(xlDown)).Rows.Count * * CR3.Offset(I3, 0).Value = R3.Value * * CR3.Offset(I3, -1) = Time End If If R4.Value < CR4.End(xlDown).Value Then * * I4 = Range(CR4, CR4.End(xlDown)).Rows.Count * * CR4.Offset(I4, 0).Value = R4.Value * * CR4.Offset(I4, -1) = Time End If If R5.Value < CR5.End(xlDown).Value Then * * I5 = Range(CR5, CR5.End(xlDown)).Rows.Count * * CR5.Offset(I5, 0).Value = R5.Value * * CR5.Offset(I5, -1) = Time End If If R6.Value < CR6.End(xlDown).Value Then * * I6 = Range(CR6, CR6.End(xlDown)).Rows.Count * * CR6.Offset(I6, 0).Value = R6.Value * * CR6.Offset(I6, -1) = Time End If End Sub- Hide quoted text - - Show quoted text - Okay I will take a look. Thank you for the info Jim. The only thing that makes me not want to use Access is the fact that I don't have the slightest idea how to. I am not familiar with how to use access at all, let alone how to program in access. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
That will make life a little trickier. In Access you can just import some of
the data into a new table. That will give a table to read an write to. That being said if you know nothing of databases do you really want to head down that road... -- HTH... Jim Thomlinson "R Tanner" wrote: On Sep 15, 12:13 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: If it was me I would write the data to a database such as Access. Then you can use XL as your front end or you can use Access to get at the data. The nice thing about using a database is that it will allow concurrent access to the data. Take a look for ADODB.Recordset to get some examples of reading from and writing to a database... -- HTH... Jim Thomlinson "R Tanner" wrote: Okay I actually Did something different, which I have listed below... The problem is I showed it to our trade analysts and they liked it. lol ( wasn't expected). So to modify, there are some changes I would need to make to this. To give you a thorough idea, I have a series of indicators that update a table of mine. I need to record each indicator's table of data (which will update when the value of that cell changes) in a separate workbook. It would be extremely inefficient to have 19 different workbooks open recording data at the same time. So, would I be able to record data to a closed excel workbook? Or maybe I can have 1 workbook open and have the data written to different csv files which I could then import into different workbooks with a small macro... What do you think? Private Sub Worksheet_Calculate() Dim R1 As Range Dim I As Integer Dim CR1 As Range Dim R2 As Range Dim CR2 As Range Dim I2 As Integer Dim R3 As Range Dim CR3 As Range Dim I3 As Integer Dim R4 As Range Dim CR4 As Range Dim I4 As Integer Dim R5 As Range Dim CR5 As Range Dim I5 As Integer Dim R6 As Range Dim CR6 As Range Dim I6 As Integer Set R1 = Range("C4") Set CR1 = Range("J4") Set R2 = Range("D4") Set CR2 = Range("L4") Set R3 = Range("C5") Set CR3 = Range("O4") Set R4 = Range("D5") Set CR4 = Range("Q4") Set R5 = Range("C6") Set CR5 = Range("T4") Set R6 = Range("D6") Set CR6 = Range("V4") If R1.Value < CR1.End(xlDown).Value Then I = Range(CR1, CR1.End(xlDown)).Rows.Count CR1.Offset(I, 0).Value = R1.Value CR1.Offset(I, -1) = Time End If If R2.Value < CR2.End(xlDown).Value Then I2 = Range(CR2, CR2.End(xlDown)).Rows.Count CR2.Offset(I2, 0).Value = R2.Value CR2.Offset(I2, -1) = Time End If If R3.Value < CR3.End(xlDown).Value Then I3 = Range(CR3, CR3.End(xlDown)).Rows.Count CR3.Offset(I3, 0).Value = R3.Value CR3.Offset(I3, -1) = Time End If If R4.Value < CR4.End(xlDown).Value Then I4 = Range(CR4, CR4.End(xlDown)).Rows.Count CR4.Offset(I4, 0).Value = R4.Value CR4.Offset(I4, -1) = Time End If If R5.Value < CR5.End(xlDown).Value Then I5 = Range(CR5, CR5.End(xlDown)).Rows.Count CR5.Offset(I5, 0).Value = R5.Value CR5.Offset(I5, -1) = Time End If If R6.Value < CR6.End(xlDown).Value Then I6 = Range(CR6, CR6.End(xlDown)).Rows.Count CR6.Offset(I6, 0).Value = R6.Value CR6.Offset(I6, -1) = Time End If End Sub- Hide quoted text - - Show quoted text - Okay I will take a look. Thank you for the info Jim. The only thing that makes me not want to use Access is the fact that I don't have the slightest idea how to. I am not familiar with how to use access at all, let alone how to program in access. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
On Sep 15, 1:07*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: That will make life a little trickier. In Access you can just import some of the data into a new table. That will give a table to read an write to. That being said if you know nothing of databases do you really want to head down that road... -- HTH... Jim Thomlinson "R Tanner" wrote: On Sep 15, 12:13 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: If it was me I would write the data to a database such as Access. Then you can use XL as your front end or you can use Access to get at the data.. The nice thing about using a database is that it will allow concurrent access to the data. Take a look for ADODB.Recordset to get some examples of reading from and writing to a database... -- HTH... Jim Thomlinson "R Tanner" wrote: Okay I actually Did something different, which I have listed below.... The problem is I showed it to our trade analysts and they liked it. lol ( wasn't expected). *So to modify, there are some changes I would need to make to this. To give you a thorough idea, I have a series of indicators that update a table of mine. *I need to record each indicator's table of data (which will update when the value of that cell changes) in a separate workbook. *It would be extremely inefficient to have 19 different workbooks open recording data at the same time. *So, would I be able to record data to a closed excel workbook? *Or maybe I can have 1 workbook open and have the data written to different csv files which I could then import into different workbooks with a small macro... What do you think? Private Sub Worksheet_Calculate() Dim R1 As Range Dim I As Integer Dim CR1 As Range Dim R2 As Range Dim CR2 As Range Dim I2 As Integer Dim R3 As Range Dim CR3 As Range Dim I3 As Integer Dim R4 As Range Dim CR4 As Range Dim I4 As Integer Dim R5 As Range Dim CR5 As Range Dim I5 As Integer Dim R6 As Range Dim CR6 As Range Dim I6 As Integer Set R1 = Range("C4") Set CR1 = Range("J4") Set R2 = Range("D4") Set CR2 = Range("L4") Set R3 = Range("C5") Set CR3 = Range("O4") Set R4 = Range("D5") Set CR4 = Range("Q4") Set R5 = Range("C6") Set CR5 = Range("T4") Set R6 = Range("D6") Set CR6 = Range("V4") If R1.Value < CR1.End(xlDown).Value Then * * I = Range(CR1, CR1.End(xlDown)).Rows.Count * * CR1.Offset(I, 0).Value = R1.Value * * CR1.Offset(I, -1) = Time End If If R2.Value < CR2.End(xlDown).Value Then * * I2 = Range(CR2, CR2.End(xlDown)).Rows.Count * * CR2.Offset(I2, 0).Value = R2.Value * * CR2.Offset(I2, -1) = Time End If If R3.Value < CR3.End(xlDown).Value Then * * I3 = Range(CR3, CR3.End(xlDown)).Rows.Count * * CR3.Offset(I3, 0).Value = R3.Value * * CR3.Offset(I3, -1) = Time End If If R4.Value < CR4.End(xlDown).Value Then * * I4 = Range(CR4, CR4.End(xlDown)).Rows.Count * * CR4.Offset(I4, 0).Value = R4.Value * * CR4.Offset(I4, -1) = Time End If If R5.Value < CR5.End(xlDown).Value Then * * I5 = Range(CR5, CR5.End(xlDown)).Rows.Count * * CR5.Offset(I5, 0).Value = R5.Value * * CR5.Offset(I5, -1) = Time End If If R6.Value < CR6.End(xlDown).Value Then * * I6 = Range(CR6, CR6.End(xlDown)).Rows.Count * * CR6.Offset(I6, 0).Value = R6.Value * * CR6.Offset(I6, -1) = Time End If End Sub- Hide quoted text - - Show quoted text - Okay I will take a look. *Thank you for the info Jim. *The only thing that makes me not want to use Access is the fact that I don't have the slightest idea how to. *I am not familiar with how to use access at all, let alone how to program in access.- Hide quoted text - - Show quoted text - I understand the fundamentals of databases and I have played around with Access a little bit, but not enough to be able to do any damage, and definitely not enough to make anything happen like what I am trying to...:) Despite my lack of understanding of Access, I can see how that would be my best option. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
Here is some example code accessing data in an Access database, you don't
need to go anywhere need the Access application Sub AddData() Dim oConn As Object Dim oRS As Object Dim sSQL As String Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "c:\bob.mdb" sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _ " VALUES ('Bob','Phillips','01202 345678','me')" oConn.Execute sSQL oConn.Close Set oConn = Nothing End Sub Sub GetData() Const adOpenForwardOnly As Long = 0 Const adLockReadOnly As Long = 1 Const adCmdText As Long = 1 Dim oRS As Object Dim sConnect As String Dim sSQL As String Dim ary sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "c:\bob.mdb" sSQL = "SELECT * From Contacts" Set oRS = CreateObject("ADODB.Recordset") oRS.Open sSQL, sConnect, adOpenForwardOnly, _ adLockReadOnly, adCmdText ' Check to make sure we received data. If Not oRS.EOF Then ary = oRS.getrows MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0) Else MsgBox "No records returned.", vbCritical End If oRS.Close Set oRS = Nothing End Sub Sub UpdateData() Const adOpenForwardOnly As Long = 0 Const adLockReadOnly As Long = 1 Const adCmdText As Long = 1 Dim oConn As Object Dim oRS As Object Dim sConnect As String Dim sSQL As String Dim ary sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "c:\bob.mdb" sSQL = "SELECT * From Contacts" Set oRS = CreateObject("ADODB.Recordset") oRS.Open sSQL, sConnect, adOpenForwardOnly, _ adLockReadOnly, adCmdText ' Check to make sure we received data. If oRS.EOF Then MsgBox "No records returned.", vbCritical Else sSQL = "UPDATE Contacts " & _ " SET Phone = 'None' " & _ "WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'" oRS.ActiveConnection.Execute sSQL sSQL = "SELECT * From Contacts" oRS.ActiveConnection.Execute sSQL ary = oRS.getrows MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0) End If oRS.Close Set oRS = Nothing End Sub -- __________________________________ HTH Bob "R Tanner" wrote in message ... On Sep 15, 1:07 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: That will make life a little trickier. In Access you can just import some of the data into a new table. That will give a table to read an write to. That being said if you know nothing of databases do you really want to head down that road... -- HTH... Jim Thomlinson "R Tanner" wrote: On Sep 15, 12:13 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: If it was me I would write the data to a database such as Access. Then you can use XL as your front end or you can use Access to get at the data. The nice thing about using a database is that it will allow concurrent access to the data. Take a look for ADODB.Recordset to get some examples of reading from and writing to a database... -- HTH... Jim Thomlinson "R Tanner" wrote: Okay I actually Did something different, which I have listed below... The problem is I showed it to our trade analysts and they liked it. lol ( wasn't expected). So to modify, there are some changes I would need to make to this. To give you a thorough idea, I have a series of indicators that update a table of mine. I need to record each indicator's table of data (which will update when the value of that cell changes) in a separate workbook. It would be extremely inefficient to have 19 different workbooks open recording data at the same time. So, would I be able to record data to a closed excel workbook? Or maybe I can have 1 workbook open and have the data written to different csv files which I could then import into different workbooks with a small macro... What do you think? Private Sub Worksheet_Calculate() Dim R1 As Range Dim I As Integer Dim CR1 As Range Dim R2 As Range Dim CR2 As Range Dim I2 As Integer Dim R3 As Range Dim CR3 As Range Dim I3 As Integer Dim R4 As Range Dim CR4 As Range Dim I4 As Integer Dim R5 As Range Dim CR5 As Range Dim I5 As Integer Dim R6 As Range Dim CR6 As Range Dim I6 As Integer Set R1 = Range("C4") Set CR1 = Range("J4") Set R2 = Range("D4") Set CR2 = Range("L4") Set R3 = Range("C5") Set CR3 = Range("O4") Set R4 = Range("D5") Set CR4 = Range("Q4") Set R5 = Range("C6") Set CR5 = Range("T4") Set R6 = Range("D6") Set CR6 = Range("V4") If R1.Value < CR1.End(xlDown).Value Then I = Range(CR1, CR1.End(xlDown)).Rows.Count CR1.Offset(I, 0).Value = R1.Value CR1.Offset(I, -1) = Time End If If R2.Value < CR2.End(xlDown).Value Then I2 = Range(CR2, CR2.End(xlDown)).Rows.Count CR2.Offset(I2, 0).Value = R2.Value CR2.Offset(I2, -1) = Time End If If R3.Value < CR3.End(xlDown).Value Then I3 = Range(CR3, CR3.End(xlDown)).Rows.Count CR3.Offset(I3, 0).Value = R3.Value CR3.Offset(I3, -1) = Time End If If R4.Value < CR4.End(xlDown).Value Then I4 = Range(CR4, CR4.End(xlDown)).Rows.Count CR4.Offset(I4, 0).Value = R4.Value CR4.Offset(I4, -1) = Time End If If R5.Value < CR5.End(xlDown).Value Then I5 = Range(CR5, CR5.End(xlDown)).Rows.Count CR5.Offset(I5, 0).Value = R5.Value CR5.Offset(I5, -1) = Time End If If R6.Value < CR6.End(xlDown).Value Then I6 = Range(CR6, CR6.End(xlDown)).Rows.Count CR6.Offset(I6, 0).Value = R6.Value CR6.Offset(I6, -1) = Time End If End Sub- Hide quoted text - - Show quoted text - Okay I will take a look. Thank you for the info Jim. The only thing that makes me not want to use Access is the fact that I don't have the slightest idea how to. I am not familiar with how to use access at all, let alone how to program in access.- Hide quoted text - - Show quoted text - I understand the fundamentals of databases and I have played around with Access a little bit, but not enough to be able to do any damage, and definitely not enough to make anything happen like what I am trying to...:) Despite my lack of understanding of Access, I can see how that would be my best option. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Procedure when The Value of A Cell Changes
Hey Bob thank you for the code...I will take this from here and see what I can do...this looks very useful... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to jump from a Form procedure to a Workbook or Module procedure? | Excel Programming | |||
How to run a procedure on entering a specific cell? | Excel Programming | |||
How to call a procedure when a cell value changes? | Excel Programming | |||
Unable to set the active cell from within a VBA procedure | Excel Programming |