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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,590
Messages
6,179,763
Members
452,940
Latest member
rootytrip

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
Back
Top