DaveRadford

Board Regular
Joined
Feb 18, 2010
Messages
63
Hello,

I have a Spreadsheet where:

Column B = Project Number
Column C = Project Title
Column D = Workstage
Column E = Workstage Description

I have code that will sort what can appear in workstage(D) depending on whats selected in Column A. this is done using the active cell at the time.

Sheets("WS").Range("L1").Value = ListBox1.Value

However if the users want to go back and edit the Workstage(D) without first selecting the Project Number again which i use as a filter trigger then it will display the result for the last trigger and not for the correct Job Number.

I can't hardcode the cell reference as there will be 52 worksheet....thats allot of code :/

The only saving grace is that if i have B11 then the Workstage will always be D11.

I have a trigger that i use to display the forms, i could the code here:

If Not Intersect(Target, Range("B11:B45")) Is Nothing Then
UserForm1.Show vbModeless
ElseIf Not Intersect(Target, Range("D11:D45")) Is Nothing Then
WSFilter
CreateWSList
UserForm2.Show vbModeless
End If
End Sub

Is there any code that could select the value in the cell two columns to the left, so that if D12 is selected then it will take the value of B12 and then copy it in cell L1 in a seperate worksheet.

Sheets("WS").Range("L1").Value

Hopefully thats understandable :D
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
To assign the value to L1 on sheet WS:

Sheets("WS").Range("L1").Value = Target.Offset(0, -2).Value
 

DaveRadford

Board Regular
Joined
Feb 18, 2010
Messages
63

ADVERTISEMENT

I keep getting Run-time error '424' Object required.

I attempted to modify like this however i still get the same error:

Sub WorkStageId()
With Sheets("WS")
.Range("L1").Value = Target.Offset(0, -2).Value
End With
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Target isn't available in that procedure. It's in an event procedure like the one containing the code in your first post.
 

DaveRadford

Board Regular
Joined
Feb 18, 2010
Messages
63
Ahhhh sorry i was adding it to a macro and then inserting the call function in. Works now tho.

Andrew.....I think i might be in love with you.

Once again, thanks for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,050
Messages
5,569,887
Members
412,298
Latest member
dietitiann
Top