Private Sub Worksheet_Change(ByVal Target As Range)

Suzannah Robertson

New Member
Joined
Jan 13, 2010
Messages
23
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

stnkynts

Board Regular
Joined
Oct 7, 2010
Messages
198
This works for me.
Code:
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

New Member
Joined
Jan 13, 2010
Messages
23
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Jan 13, 2010
Messages
23

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,875
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
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

New Member
Joined
Jan 13, 2010
Messages
23

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,875
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
If your macro doesn't trigger on changing of the cellvalue at row 8, then run this code to restore events raising:
Rich (BB code):

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

New Member
Joined
Jan 13, 2010
Messages
23
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,577
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top