Selecting Data

[DaveRadford]

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

 

[Andrew Poulsom]

To return the cell 2 columns to the left of target:

Target.Offset(0, -2)

 

[DaveRadford]

and this will select the cells value?

 

[Andrew Poulsom]

To assign the value to L1 on sheet WS:

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

 

[DaveRadford]

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]

Target isn't available in that procedure. It's in an event procedure like the one containing the code in your first post.

 

[DaveRadford]

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.