Private Sub Worksheet_Change(ByVal Target As Range)

[Suzannah Robertson]

Hello,

I'm trying to use a Private Sub Worksheet_Change(ByVal Target As Range) macro on one of my sheets and it's not working.
I've put in the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Partner
If Target.Row = 8 Then
thiscolumn = Target.Column

Range("e71").select...

and it's not triggered when I change information on row 8 of the worksheet... Can anyone see what I'm doing wrong?

Any help much appreciated.

thanks,
Suzannah

 

[stnkynts]

This works for me.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim trgtcolumn As Integer
 
If Target.Row = 8 Then
trgtcolumn = Target.Column
MsgBox trgtcolumn
End If
 
End Sub

 

[Suzannah Robertson]

Thanks stnkynts, butI need the macro to continue to perform calculations if that row is selected.. that's why I nominated the "thiscolumn" so that the if function continues..
am I using the thiscolumn incorrectly?

 

[lenze]

Perhaps you should post the full code so we can see how you are using your variable. Do you want itto be the column number? The value? What?

lenze

 

[Suzannah Robertson]

Hi Lenze,

I'm trying to work in a goal seek function:

Private Sub Worksheet_Change(ByVal Target As Range)
'Partner
If Target.Row = 8 Then
thiscolumn = Target.Column
Sheets("utilisation").Select
Range("e71").Select
Do Until ActiveCell.Value = "E"
If ActiveCell.Value = 0 Then
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.GoalSeek Goal:=0, ChangingCell:=ActiveCell.Offset(-19, 52)
ActiveCell.Offset(0, 1).Select
End If
Loop
End If

excuse the dreadful coding. I'm trying to change a value via overwriting a % using goal seek..

does this make sense?
S

 

[ZVI]

Thanks stnkynts, butI need the macro to continue to perform calculations if that row is selected.. that's why I nominated the "thiscolumn" so that the if function continues..
am I using the thiscolumn incorrectly?

Suzannah, may be you need in Worksheet_SelectionChange rather than Worksheet_Change?

 

[Suzannah Robertson]

hello ZVI,
I tried that one, but it activates the macro when i select the cell rather than waiting until I change it, so I can't manipulate the data..
S

 

[ZVI]

If your macro doesn't trigger on changing of the cellvalue at row 8, then run this code to restore events raising:

Sub EventsOn()
  Application.EnableEvents = True
End Sub

Without your code optimization, it seems that there is incorrect referencing:
Sheets("utilisation").Select
Range("e71").Select

It's my guessing that Sheets("utilisation") is not the sheet with your code.
But Range("e71") is always referenced to the E71 cell of the sheet with your code, not to the E71 of Sheets("utilisation").
Try to replace Range("e71").Select by ActiveSheet.Range("e71").Select

 

[Suzannah Robertson]

thanks for your reply ZVI.. but I still can't seem to get it working.

 

[Suzannah Robertson]

I think I've found the answer.. I think it was because I was doing conditional formatting on those cells using a user defined function.. when I took that off it seems to work.
thanks for your help! now just got to figure out how to still have the UDF and worksheet change macro