Macro to copy certain cells in a changed row

Microsoft Ms sla excel lic/sa 3yr-1
June 30, 2010 at 21:35:15
Specs: Windows Vista
I'm lookign for help to create a macro that:
1) identifies when a change has occurred to a cell/row within a named range ("Codes") sitting in column A within just one worksheet in the book (ie Sheet 1)
2) identifies and stores the row value (say i) where this changes has occurred
3) Copies the following cells within this row,, anely (i,5) (i,9) (i,19-20)
4) Pastes them to a set of respective cells within Sheet 2. The destination cells remain fixed
5) Recalculates Sheet2

See More: Macro to copy certain cells in a changed row

Report •


#1
July 1, 2010 at 05:48:37
Hi,

First a few points:
1. There is no need to include an instruction to recalculate, unless you have Calculation set to Manual
2. I do not understand what cells you want to copy.
Your named range "Codes" is said to be in column A and you refer to a row value as i - did you mean 1. Then you say copy cells such as (i,5). Is this Row 1 and column E or column I and row 5.
3. You haven't said what cells to copy to on Sheet2

To get the result you want you can use the Change Event on Sheet1
Whenever the data in a cell on Sheet1 changes, the On change event is triggered.
You can capture this and test if the cell that changed was within your named range.
If it was within the named range, then you can copy cells on the same row as the changed cell to specified cells on Sheet2.

The following code does this, although you will have to rework the copy cell address depending on what you actually want - (I assumed it was cells on the same row as the changed cell in columns E, I and O to S),
and change the destination cell addresses to what you need.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'disable events, so that changes made by this code
'do not re-trigger it
Application.EnableEvents = False

On Error GoTo ErrHnd

Dim rngIsect As Range
'test if change occurred in a cell in the named range
Set rngIsect = Intersect(Target, Range("Codes"))
If Not rngIsect Is Nothing Then
    'copy and paste
    With ActiveSheet
        .Cells(Target.Row, 5).Copy _
            Destination:=Worksheets("Sheet2").Range("A1")
        .Cells(Target.Row, 9).Copy _
            Destination:=Worksheets("Sheet2").Range("A2")
        .Range(.Cells(Target.Row, 15), .Cells(Target.Row, 19)).Copy _
            Destination:=Worksheets("Sheet2").Range("A3")
    End With
End If
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

As changes made by macros cannot be undone with the Undo function, you should test this on a backup copy of your data.
Always make a backup before running your finished macro.

Hope this helps.

Regards


Report •

#2
July 1, 2010 at 10:21:05
Hi Humar

Thx for the feedback. Let me try and answer your points first.

Sheet2 incorporates 2 iterative calculations. I thought that the user would have to trigger a re-cal once chages were made??

Re (2): i (not 1) refers to the row number which represents the intersect (rownum) of ColA and the cell which has been changed within it.

The macro needs to identify this rownum (i) and then use this value to extract the respective column values within row i.

Re (3); Yes ur right I did not specify but I thought this bit would be easy!

I'm sure the code you have provided will help. At first glance it looks to hit the mark. I will test shortly and revert


Report •

#3
July 1, 2010 at 12:08:05
Hi,

The change event returns a variable called Target.
This is the cell that has changed.
You can use Target (as the macro does) to get the row of the changed cells: Target.Row

Because the row is available, you won't need to assign it to another variable i

If the changed cell was A2 then this code:
.Cells(Target.Row, 5).Copy
will copy cell E2
'Cells' takes numerical row and column values as its arguments, so it is using: Cells(2, 5).Copy

Regards


Report •

Related Solutions

#4
July 1, 2010 at 19:34:39
Thx. Yes understood.

Unfortunatley I can't get the macro to work. I've got this posted to Sheet1. I have other Worksheet_Change routines here already. the first one works and I know why the second one doesn't (because the change results from a formula)

Perhpas this macro doesn't work because the change to the cell in ColA is triggerred by a UserForm rather than manually by the user itself?

Maybe a more convenient (but not ideal) structure would be to find the last non-empty cell in ColA and determine the relevant row value that way?

In regards to the latter i had this:

Sub CopyVals()
Dim lastCode_Row As Integer
Dim strtRange, TargetRow As Range

With Worksheets("Position Log")
Set strtRange = Range("A14")
Set TargetRow = strtRange.End(xlDown)
lastCode_Row = TargetRow.Row


'Copy Position Log cell values to MM Calc
.Range("C" & lastCode_Row).Copy _
Destination:=Worksheets("MM Calc").Range("G5")
.Range("G" & lastCode_Row).Copy _
Destination:=Worksheets("MM Calc").Range("B6")
.Range("I" & lastCode_Row).Copy _
Destination:=Worksheets("MM Calc").Range("G6")
.Range("R" & lastCode_Row).Copy _
Destination:=Worksheets("MM Calc").Range("B20")
.Range("S" & lastCode_Row).Copy _
Destination:=Worksheets("MM Calc").Range("B21")
.Range("T" & lastCode_Row).Copy _
Destination:=Worksheets("MM Calc").Range("G20")
End With

End Sub

But it doesn't work either and seems to call on Functions etc resulting in a stack of "Values!" and "Names?" cropping up in Sheet1.

b.rgds


Report •

#5
July 2, 2010 at 04:30:48
Hi,

Any method to find the changed row will not work if the code is not being triggered by the relevant change event.
If the code is being triggered by the change event, then the Target.row method will work.

I suggest you put a break point in the code I gave you and see if the code is being triggered.
Enter a breakpoint on the line that starts

Set rngIsect

Then use f8 to single step and see if the rest of the code works.

The change event will be triggered by a change to a cell caused by a User Form

I created a User Form that changes the value of cell A1 and the Change Event is triggered and it identifies that cell A1 was changed (Target.Address = $A$1)

You say I have other Worksheet_Change routines here already
I presume you only have one line:

Private Sub Worksheet_Change(ByVal Target As Range)

Have you tried single stepping from the start of this, so that you know that the various pieces of code are actually running.

I also suggest that you try the change event macro I supplied in a new workbook, and just test it without any other code present. Just define the named range "Codes" in the new workbook.
Instead of the Copy and Paste have a message box report the value of Target.Row, like this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'disable events, so that changes made by this code
'do not re-trigger it
Application.EnableEvents = False

On Error GoTo ErrHnd

Dim rngIsect As Range
'test if change occurred in a cell in the named range
Set rngIsect = Intersect(Target, Range("Codes"))
If Not rngIsect Is Nothing Then
    MsgBox "The changed cell was on row " & Target.Row
End If
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

Can you post the part of the Worksheet Change macro that branches to the various routines.

I looked at the code you posted. There is nothing in the code itself that causes anything to change on any Sheet, except MM Calc.
The code copies a series of cells based on the last row in column A below A14, that contains data.

If you get errors on "Sheet1", are these cells linked to the cells that the code changes on "MM Calc"

The code does not call on Functions etc - I tested it and it does what you would expect - it copies a few cells from one worksheet to another.

Are you sure that this is the code that is running - again put a break point in it and see if the code stops there.

Regards


Report •

#6
July 3, 2010 at 19:25:11
Your advice is great

I tested in a new workbook and yes it worked

I dis-engaged the other change value sub-routines i had on this worksheet and, again, it works....However, it copies the actual cell contents (ie it copies the formula in the cell when I actually just need the value derived by that formula). I tried attaching something like PasteSpecial Paste:=xlPasteValues to the program line but it doesn't accept.


I'm sure this will be easy to rectify. However how do i then re-engage the other two change value sub-routines. One is shown below whihc was working fine:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("AD10")) Is Nothing Then
Exit Sub
Else
Call BidPrice_IconCondFormat
End If
End Sub

Is their a specific structure to facilitate two or more change value sub-routines on the same worksheet?

Incidentally, can you call on sub-routines as part of a UserForm process. What I would like to do is call on 3 IconCondFormat sub-routines once the data input from the UserForm into the worksheet "Postion Log" has occurred. The alternative is to call on these as part of the change rountine you have provided as this needs to be applied each time there is a change in the target range ("Codes")?

Many thx


Report •

#7
July 4, 2010 at 00:34:46
re: "Is their a specific structure to facilitate two or more change value sub-routines on the same worksheet?"

This can be done via a series of IF's to determine what the specifics of the change are.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
'Condition 1: 
'
 If Not Intersect(Target, Range("Codes")) Is Nothing Then
  'Code to run when the Target is within Range("Codes")
  Exit Sub
 End If
'
'Condition 2:
'
 If Target.Address = "$AD$10" Then
  'Code to run when Range("AD10") is Changed
  Exit Sub
 End If
'
'Condition 3:
'
 If Target.Column = 3 And Cells(Target.Row, 4) = "Yes" Then
  'Code to run when Change was made to Column C and the
  'cell in Column D of the same Row contains "Yes"
  Exit Sub
 End If
'
'Etc.
'
End Sub

If 2 (or more) conditions might be met by a single change, simply add some more If's or some And's to narrow down the specifics of the change, similar to what I did for Condition 3.


Report •

#8
July 4, 2010 at 05:00:32
Hi,

First the copy and paste issue.

You didn't post the code that you tried.

In the original:

        .Cells(Target.Row, 5).Copy _
            Destination:=Worksheets("Sheet2").Range("A1")

The copy and paste is all one function - the _ is a line continuation symbol
This 'all in one' function does not accept alternatives such as PasteSpecial. You have to use separate Copy and Paste commands:
        Range("J4").Copy
        Range("B15").PasteSpecial _
            Paste:=xlPasteValuesAndNumberFormats

There are other Paste Special options available such as xlPasteValues

The issue of testing the changed cell and having different responses:
Here is a short Change event macro. As before create a named range "Codes" say C3 to C6

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
    MsgBox "This is the first message" & vbCrLf _
        & "The changed cell was on row: " & Target.Row
ElseIf Not Intersect(Target, Range("B2")) Is Nothing Then
    MsgBox "This is the second message" & vbCrLf _
        & "The changed cell was on row: " & Target.Row
ElseIf Not Intersect(Target, Range("Codes")) Is Nothing Then
    MsgBox "This is the third message" & vbCrLf _
        & "The changed cell was on row: " & Target.Row
End If
End Sub

Now change cell A1 and you will see the first message, change B2 and you will see the second message and changing any cell in the Codes range will display the third message.

As to User Forms, yes User Forms can have subroutines.
Let's say you have a User Form with one Text Box, this code calls one of three subroutines contained within the user form based on the text entered in TextBox1:

Private Sub TextBox1_Change()
Select Case TextBox1.Text
    Case "1"
        Call mysub1
    Case "2"
        Call mysub2
    Case "3"
        Call mysub3
End Select
End Sub

Private Sub mysub1()
        ActiveSheet.Range("D1") = 100
End Sub
Private Sub mysub2()
        ActiveSheet.Range("D1") = 200
End Sub
Private Sub mysub3()
        ActiveSheet.Range("D1") = 300
End Sub

Hope this helps.

Regards


Report •

#9
July 4, 2010 at 20:41:18
OK...I seemed to have got this working at one point...but then it went away!

I now have the situation where none of the changes are triggering the sheet1 change value routines. (Please note I had to change the target range in the Position Log worksheet from colA to a range in the last column as the routine would not pick up all the values established by the UserFrom procedure otherwise.

Here is the code I currently have:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'disable events, so that changes made by this code do not re-trigger it
Application.EnableEvents = False

On Error GoTo ErrHnd

'Condition 1:
If Not Intersect(Target, Range("Growth_Target")) Is Nothing Then
'Code to run when the Target is within Range("Growth_Target")
'test if change occurred in a cell in the named range
MsgBox "The changed cell was on row: " & Target.Row


Dim rngIsect As Range
Set rngIsect = Intersect(Target, Range("Growth_Target"))
If Not rngIsect Is Nothing Then
With Worksheets("Position Log")
.Range("C" & Target.Row).Copy
Worksheets("MM Calc").Range("G5").PasteSpecial Paste:=xlPasteValues
.Range("G" & Target.Row).Copy
Worksheets("MM Calc").Range("B6").PasteSpecial Paste:=xlPasteValues
.Range("I" & Target.Row).Copy
Worksheets("MM Calc").Range("G6").PasteSpecial Paste:=xlPasteValues
.Range("R" & Target.Row).Copy
Worksheets("MM Calc").Range("B20").PasteSpecial Paste:=xlPasteValues
.Range("S" & Target.Row).Copy
Worksheets("MM Calc").Range("B21").PasteSpecial Paste:=xlPasteValues
.Range("W" & Target.Row).Copy
Worksheets("MM Calc").Range("G20").PasteSpecial Paste:=xlPasteValues

'.Cells(Target.Row, 7).Copy _
Destination:=Worksheets("MM Calc").Range("B6")
'.Range(.Cells(Target.Row, 18), .Cells(Target.Row, 20)).Copy _
Destination:=Worksheets("MM Calc").Range("A3")
End With
Call Initial_Stop_IconCondFormat


Exit Sub
End If

'Condition 2:
'Code to run when change is made to thresholds for Bid Price breaches/alerts
If Target.Address = "$AD$10" Then
Call BidPrice_IconCondFormat
Exit Sub
End If

'Condition 3:
'Code to run when change is made to Highest Price after entry and a Trailing ATR has been entered
If Target.Column = 24 And Cells(Target.Row, 25) > 0 Then
Call Trailing_Stop_IconCondFormat
Exit Sub
End If

'Condition 4:
'Code to run when change is made to Latest Bid Price
If Not Intersect(Target, Range("Latest_Bid_Price")) Is Nothing Then
Call BidPrice_IconCondFormat
Exit Sub
End If

're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub


Report •

#10
July 5, 2010 at 06:27:22
Hi,

Just a quick note - if you break and reset code in a macro that stops events, they do not restart automatically.

In the VBA window, open the Immediate window (View - Immediate Window).
Scroll to the bottom of the window and enter this:
Application.EnableEvents = TRUE
and Enter

Then test your events again.

Regards


Report •

#11
July 5, 2010 at 07:15:00
Just a quick tip...

Since I do so much "playing" with Event macros while helping out in this forum, I put a custom button on my toolbar that does nothing but set EnableEvents = True.

I keep forgetting to reset EnableEvents while in the VBA editor and I'm always wondering why my code doesn't do anything.

Having the button on my toolbar makes it easy to eliminate
EnableEvents = False as the issue.


Report •

#12
July 5, 2010 at 08:32:28
Thx Humar

No idead what Application.EnableEvents = TRUE
is doing exactly or strictly how to use it but it seems to work! However...does this need to be "entered" every time there is a break when you're testing? Sometimes the macro ran on other conditions when subsequent changes were made...but sometimes not!

For example it did not enable Conditon 3 to operate under any testing. For instance, it does not pick up changes to ColX and look at this togehter with the value of the cell in (Target Row, ColY) to call on another routine.

All advice has been much appreciated by the way


DerbyDad03,
Thx, however given my statement in para 1 above, I think you will forgive me for saying I don't really comprehend and therefore don't fully appreciate how to apply your advice just yet!


Report •

#13
July 5, 2010 at 10:13:34
re: No idea what Application.EnableEvents = TRUE
is doing...

In order for any Event driven code to work e.g.

Worksheet_Change
Worksheet_SelectionChange
Workbook_BeforePrint
Workbook_Open
etc.

The application (Excel) needs to have Events Enabled.

Events are enabled (TRUE) by default when the app opens, but VBA provides the ability to disable Events (FALSE) so that the Event code doesn't force itself into a loop.

Let's say that you have this simple piece of code in a Worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target > 1 Then Target = Target + 1
End Sub

If the changed cell was greater than 1, the Worksheet_Change Event code would add 1 to the value in the cell and change it.

That change would cause the Worksheet_Change Event code to recognize the change and fire again. The code would add 1 to the value in the cell and change it.

That change would cause the Worksheet_Change Event code to recognize the change and fire again. The code would add 1 to the value in the cell and change it.

You see what I mean?

So, you should add 2 lines to the code:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
   If Target > 1 Then Target = Target + 1
 Application.EnableEvents = True
End Sub

With those additional lines, the change that the code makes would not recognized by the Event macro and therefore the code would not keep firing.

However, the problem lies in the fact that Application.EnableEvents does not get set back to TRUE if the code doesn't execute the Application.EnableEvents = True line. If the code crashes or you stop it before it executes that line, any and all Event driven macros will be disabled - until you either enable them via code or restart the application.

Humar's suggestion of entering Application.EnableEvents = True in the Immediate window re-enables events so that the application will react to them.

My suggestion was to write a very simple macro and assign it to a button so all you have to do is click it to re-enable Events. e.g.

Sub EventsOn()
 Application.EnableEvents = True
End Sub

I hope that that explanation helps you understand Events a little better.


Report •

#14
July 5, 2010 at 11:05:34
I'm jumping in late here, but I think I have found at least one of your problems.

Note: (I changed all of your "Call" lines to simply display a MsgBox with the name of the called routines just for testing purposes. If you find that the code works for all of your conditions, just change those lines back to Call.)

Your apparent problem:

In each of your conditions, you have the line:

Exit Sub

This means that as soon as any condition is met, the code for that condition runs, but the the Events never get re-enabled because you are exiting the Sub before re-enabling them.

You have a few choices:

1 - Add the line:

Application.EnableEvents = True within each condition before Exit Sub

2 - Instead of Exit Sub use GoTo Done and add the label Done: just below the 're-enable events comment.

3 - If all are your changes are mutually exclusive, meaning that only a single condition can be met with any given change, you can simply eliminate the Exit Sub line from each condition and just let the code check every condition. All but one condition will fail and the code will just flow through to the bottom where it will encounter the Application.EnableEvents = True line.

That is the least efficient way to do it, but it would work.

However, I think you were also missing an End If for Condition 1. As I said, I'm jumping in late here, but I saw 2 IF's within the Condition 1 section but only 1 End if.

The code below seem to work for every condition.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'disable events, so that changes made by this code do not re-trigger it
Application.EnableEvents = False

'On Error GoTo ErrHnd

'Condition 1:
  If Not Intersect(Target, Range("Growth_Target")) Is Nothing Then
'Code to run when the Target is within Range("Growth_Target")
'test if change occurred in a cell in the named range
   MsgBox "The changed cell was on row: " & Target.Row


    Dim rngIsect As Range
     Set rngIsect = Intersect(Target, Range("Growth_Target"))
      If Not rngIsect Is Nothing Then
       With Worksheets("Position Log")
        .Range("C" & Target.Row).Copy
          Worksheets("MM Calc").Range("G5").PasteSpecial _
            Paste:=xlPasteValues
        .Range("G" & Target.Row).Copy
          Worksheets("MM Calc").Range("B6").PasteSpecial _
            Paste:=xlPasteValues
        .Range("I" & Target.Row).Copy
          Worksheets("MM Calc").Range("G6").PasteSpecial _
            Paste:=xlPasteValues
        .Range("R" & Target.Row).Copy
          Worksheets("MM Calc").Range("B20").PasteSpecial _
             Paste:=xlPasteValues
        .Range("S" & Target.Row).Copy
          Worksheets("MM Calc").Range("B21").PasteSpecial _
            Paste:=xlPasteValues
        .Range("W" & Target.Row).Copy
          Worksheets("MM Calc").Range("G20").PasteSpecial _
            Paste:=xlPasteValues

'.Cells(Target.Row, 7).Copy _
Destination:=Worksheets("MM Calc").Range("B6")
'.Range(.Cells(Target.Row, 18), .Cells(Target.Row, 20)).Copy _
Destination:=Worksheets("MM Calc").Range("A3")
       End With
        MsgBox "Initial_Stop_IconCondFormat"
      End If
   GoTo Done
  End If

'Condition 2:
'Code to run when change is made to thresholds for Bid Price breaches/alerts
  If Target.Address = "$AD$10" Then
   MsgBox " BidPrice_IconCondFormat"
   GoTo Done
  End If

'Condition 3:
'Code to run when change is made to Highest Price 
'after entry and a Trailing ATR has been entered
  If Target.Column = 24 And Cells(Target.Row, 25) > 0 Then
   MsgBox "Trailing_Stop_IconCondFormat"
   GoTo Done
  End If

'Condition 4:
'Code to run when change is made to Latest Bid Price
  If Not Intersect(Target, Range("Latest_Bid_Price")) Is Nothing Then
   MsgBox "BidPrice_IconCondFormat"
   GoTo Done
  End If

're-enable events
Done:
 Application.EnableEvents = True
 Exit Sub

'error handler
ErrHnd:
 Err.Clear
're-enable events
 Application.EnableEvents = True
End Sub


Report •

#15
July 5, 2010 at 12:21:07
Hi,

The first issue is that you use Exit sub without re-enabling Events.

DerbyDad03 has provided background on the Enable Events function.

If you follow the path taken through the code you start with:
Application.EnableEvents = False

This means that (amongst other things), changing a cell does not trigger the change event anymore.
This is necessary to stop firing the change macro by changes made by the macro (as discussed by DerbyDad03).

So, Application.EnableEvents = False is important for your change macro, but you must ensure that every route out of the macro has this instruction:
Application.EnableEvents = True.

You will see that the error handler includes this statement, so that if there is a program error in the code, the Application.EnableEvents = True statement is always executed.

If you look at Condition 1, it ends like this:

        Call Initial_Stop_IconCondFormat
    Exit Sub

What it needs if you are going to exit this way is:

    Call Initial_Stop_IconCondFormat
    Application.EnableEvents = True
    Exit Sub

Secondly there is unneeded code in Condition 1:

'Condition 1:
If Not Intersect(Target, Range("Growth_Target")) Is Nothing Then
'Code to run when the Target is within Range("Growth_Target")
'test if change occurred in a cell in the named range
MsgBox "The changed cell was on row: " & Target.Row


Dim rngIsect As Range
Set rngIsect = Intersect(Target, Range("Growth_Target"))
If Not rngIsect Is Nothing Then
With Worksheets("Position Log")

Dim rngIsect As Range
Set rngIsect = Intersect(Target, Range("Growth_Target"))
If Not rngIsect Is Nothing Then
code is not needed as you have already tested for the change. Also it includes an IF statement that is not balanced by an End IF - which causes an error and the 'action' part of the code does not run.

During testing you can 'comment out' the line On Error Goto ...
This will result in errors stopping the code and highlighting the offending line.
remove the ' from the line when all is working

I used an If ... ElseIf ...ElseIf ... End IF structure, then there is only one main exit from the change event and Application.EnableEvents=True is only needed once.

Without your data I couldn't really test the code, but this is what I came up with:

Private Sub Worksheet_Change(ByVal Target As Range)
'disable events, so that changes made by this code do not re-trigger it
Application.EnableEvents = False

On Error GoTo ErrHnd

'Condition 1:
If Not Intersect(Target, Range("Growth_Target")) Is Nothing Then
    'Code to run when the Target is within Range("Growth_Target")
    With Worksheets("Position Log")
        .Range("C" & Target.Row).Copy
        Worksheets("MM Calc").Range("G5").PasteSpecial Paste:=xlPasteValues
        .Range("G" & Target.Row).Copy
        Worksheets("MM Calc").Range("B6").PasteSpecial Paste:=xlPasteValues
        .Range("I" & Target.Row).Copy
        Worksheets("MM Calc").Range("G6").PasteSpecial Paste:=xlPasteValues
        .Range("R" & Target.Row).Copy
        Worksheets("MM Calc").Range("B20").PasteSpecial Paste:=xlPasteValues
        .Range("S" & Target.Row).Copy
        Worksheets("MM Calc").Range("B21").PasteSpecial Paste:=xlPasteValues
        .Range("W" & Target.Row).Copy
        Worksheets("MM Calc").Range("G20").PasteSpecial Paste:=xlPasteValues
    End With
    Call Initial_Stop_IconCondFormat
    
    'Condition 2:
    ElseIf Target.Address = "$AD$10" Then
    'Code to run when change is made to thresholds for Bid Price breaches/alerts
    Call BidPrice_IconCondFormat

    'Condition 3:
    ElseIf Target.Column = 24 And Cells(Target.Row, 25) > 0 Then
    'Code to run when change is made to Highest Price after entry and
    'a Trailing ATR has been entered
    Call Trailing_Stop_IconCondFormat

    'Condition 4:
    ElseIf Not Intersect(Target, Range("Latest_Bid_Price")) Is Nothing Then
    'Code to run when change is made to Latest Bid Price
    Call BidPrice_IconCondFormat
End If

're-enable events before exiting the sub
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

Regards
Edit:
I see that DerbyDad03 has already identified the issues - although I used the If...ElseIf ... structure, rather than Goto Done. I don't think that it adds significant overhead, although doing a series of If ... End If, would add more overhead.


Report •

#16
July 6, 2010 at 01:16:12
All working fine & dandy now and I've learnt a fair bit of decent stuff so many thx to you both !

Report •

#17
July 6, 2010 at 04:16:46
Hi,

Great news - and thanks for the feedback.

Regards

Humar


Report •

#18
July 6, 2010 at 07:50:22
No worries

One more quick request. I have this small If statement embedded in one of the conditions. It just creates an input box if there is no value within a cell relative to the Target. is there an alternative to using .Select and ActiveCell.Value when ascribing the input value to the cell?

If Cells(Target.Row, 25) = "" Then
Dim Trail_ATR As String
Dim myPrompt As String
Dim myTitle As String

myPrompt = "Enter Trailing ATR as #.## " & Chr(13) & Chr(13) & " (for example: for $1.05 enter 1.05)"
myTitle = "Enter Trailing ATR"
Trail_ATR = InputBox(myPrompt, myTitle)
With Worksheets("Position Log")
.Range("Y" & Target.Row).Select
ActiveCell.Value = Trail_ATR
End With
End If


Report •

#19
July 6, 2010 at 08:22:11
Hi,

In VBA you typically don't need to select a cell or another object to be able to use it (change it, read it etc.)
So
.Range("Y" & Target.Row) = Trail_ATR
will work.
In this case you are using the default for a range (cell) object which is its 'Value'
So this is more technically correct:
.Range("Y" & Target.Row).Value = Trail_ATR

(I like to use the full property as I sometimes convert VBA to VisualBasic.NET which will not let me use defaults, so the conversion is easier).

If you want to read a value from a cell you have several options, because Excel stores different versions of the data.

If cell A1 has a value of 1000.1
and it's formatted as a number with two decimal places,
then strTest=Range("A1").Text will return the formatted text 1000.10,
but if you wanted a number, use its Value.

Cells with dates have very different Value, Value2 and Text properties.
e.g. "01/01/2010" as Text and 40179 as Value2
and Value is in a default date format "01/Jan/10" on my PC

but
you can only enter data, even text through the Value property. The others are read only.

In your code you might want to consider adding some validation to the string from the message box - in case the user has not entered data, or entered unusable data.

Regards


Report •

#20
July 6, 2010 at 15:45:04
thx again

Report •


Ask Question