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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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,722
Office Version
  1. 2016
  2. 2010
  3. 2007
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,722
Office Version
  1. 2016
  2. 2010
  3. 2007
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,445
Messages
5,528,800
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top