Selecting Data

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
 

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
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.
 

Forum statistics

Threads
1,082,575
Messages
5,366,405
Members
400,887
Latest member
tporeda

Some videos you may like

This Week's Hot Topics

Top