R
R Tanner
Hi,
I need to run a procedure when the value of a specific cell changes..
How would I do that?
I need to run a procedure when the value of a specific cell changes..
How would I do that?
B
Bob Phillips
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.
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.
J
Jim Thomlinson
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
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
R
R Tanner
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
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
R
R Tanner
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.
J
Jim Thomlinson
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...
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...
R
R Tanner
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.
J
Jim Thomlinson
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...
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...
R
R Tanner
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.
B
Bob Phillips
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
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.
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
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.
R
R Tanner
Hey Bob thank you for the code...I will take this from here and see
what I can do...this looks very useful...
what I can do...this looks very useful...