active cell triggering events... only after it's no longer active ?

[CatyH]

I started with hardcoding this task which worked really well. But then, trying to put parameters in here instead has really thrown me off.
The scenario: I have three columns: C (percent), D (hours), E (dollars)

When the user enters a value into column C for a particular row, I want that action to trigger calculating values to put in columns D and E for that row.
When the user enters a value into column D for a particular row, I want that action to trigger calculating values to put in columns C and E for that row.
When the user enters a value into column E for a particular row, I want that action to trigger calculating values to put in columns C and D for that row.

The problem SEEMS to be that when I run this code below, the events are not triggered until I get out of the current cell - which means that "THIS" cell changes to my new cell and the code being run is based off the NEW this cell instead of the one I wanted...

Also - this may or may not be related... it took me about 2 hours to figure out how to build a "range" based on the current cell. I wanted to build the (type range):
PercentCell to be the percent column and THIS row (where THIS is the active cell)
HoursCell to be the hours column and THIS row (where THIS is the active cell)
DollarsCell to be the dollars column and THIS row (where THIS is the active cell).
I suspect this may be one thing wrong with my code and possibly a place to start for why it's not working well...


Wish I could somehow deliver on a plate of homemade chocolate chip cookies to whomever can help me solve this conundrum! Alas, I can only offer my undying gratitude... Thank you in advance for your help!

cat

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = True
    
    
Dim thisCell As Range
Dim thisCol As Integer
Dim thisRow As Integer


Dim percentCellRef As String
Dim percentCell As Range
Dim percentCol As Integer


Dim hoursCellRef As String
Dim hoursCell As Range
Dim hoursCol As Integer


Dim dollarsCellRef As String
Dim dollarsCell As Range
Dim dollarsCol As Integer


Set thisCell = ActiveCell
thisCol = ActiveCell.Column
thisRow = ActiveCell.Row




percentCellRef = "C" & thisRow
Set percentCell = Range(percentCellRef)
percentCol = 3


hoursCellRef = "D" & thisRow
Set hoursCell = Range(hoursCellRef)
hoursCol = 4


dollarsCellRef = "E" & thisRow
Set dollarsCell = Range(dollarsCellRef)
dollarsCol = 5




            
            




  If thisCol = percentCol Then
    CalcHoursFromPct hoursCell
    CalcDollarsFromPct dollarsCell
      
   Else
      
      If thisCol = hoursCol Then
        CalcPctFromHours percentCell
        CalcDollarsFromHours dollarsCell
      
      Else
      
        If thisCol = dollarsCol Then
          CalcPctFromDollars percentCell
          CalcHoursFromDollars hoursCell
       
        Else
          MsgBox ("Else")
      
        End If
      End If
  End If




End Sub

 

[RoryA]

The Target argument is the cell that was changed, so you should use that instead of Activecell.

 

[CatyH]

ah - thanks... just tried changing all my ActiveCell to TargetCell and it's still buggy... :/ Run Time error 424 Object required... ?

 

[RoryA]

Not TargetCell, just Target - as in:

Private Sub Worksheet_Change(ByVal Target As Range)

 

[CatyH]

OHHHHHHHHHHHHHH! you mean IN the code put Target, not TargetCell...

GOT IT!!

AND IT WORKS!!!! YOU ARE AMAZING!!!!

THANK YOU THANK YOU THANK YOU!!!!!!

 

[RoryA]

Glad to help.