Please help with code

[Norbert]

In workbook "planning ticket.xls" cell E8 there is following formula: ='C:\Documents and Settings\Tickets\[8250.xls]Prod.Ticket'!A16
(it looks up value of cell A16 in workbook 8250.xls)

In the event that cell F1 is changed to a new value e.g.: 7722, I'd like to have the formula in E8 changed to: ='C:\Documents and Settings\Tickets\[7722.xls]Prod.Ticket'!A16

Is this possible, somehow?
Norbert

 

[Claus Busch]

Hi Norbert,

Am Mon, 27 Aug 2012 07:37:59 -0700 (PDT) schrieb Norbert:

In workbook "planning ticket.xls" cell E8 there is following formula: ='C:\Documents and Settings\Tickets\[8250.xls]Prod.Ticket'!A16
(it looks up value of cell A16 in workbook 8250.xls)

In the event that cell F1 is changed to a new value e.g.: 7722, I'd like to have the formula in E8 changed to: ='C:\Documents and Settings\Tickets\[7722.xls]Prod.Ticket'!A16

try in code module of the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myPath As String
Dim wbName As String

If Target.Address <> "$F$1" Then Exit Sub
myPath = "'C:\Documents and Settings\Tickets\"
wbName = "[" & [F1] & ".xls]Prod.Ticket'!A16"
Range("E8").Formula = "=" & myPath & wbName

End Sub


Regards
Claus Busch

 

[Norbert]

Hi Norbert,



Am Mon, 27 Aug 2012 07:37:59 -0700 (PDT) schrieb Norbert:

In workbook "planning ticket.xls" cell E8 there is following formula: ='C:\Documents and Settings\Tickets\[8250.xls]Prod.Ticket'!A16

(it looks up value of cell A16 in workbook 8250.xls)
In the event that cell F1 is changed to a new value e.g.: 7722, I'd like to have the formula in E8 changed to: ='C:\Documents and Settings\Tickets\[7722.xls]Prod.Ticket'!A16

try in code module of the worksheet:



Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPath As String

Dim wbName As String



If Target.Address <> "$F$1" Then Exit Sub

myPath = "'C:\Documents and Settings\Tickets\"

wbName = "[" & [F1] & ".xls]Prod.Ticket'!A16"

Range("E8").Formula = "=" & myPath & wbName



End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,
your code works fine, thank you for that.
What I did not mention, there are plenty other cells like E8, with the same path in the formula but obviously linking to a different cell.

For example:
E8 ='C:\Documents and Settings\Tickets\[7722.xls]Prod.Ticket'!A16
AL7 ='C:\Documents and Settings\Tickets\[7722.xls]Prod.Ticket'!BT7
AP7 ='C:\Documents and Settings\Tickets\[7722.xls]Prod.Ticket'!A60
and more ...

How can I include those into the code?
As far as I can read your code, string wbName is different all the time, but
I have no knowledge of how to change it.

Regards,
Norbert

 

[Norbert]

Hi Norbert,
Am Mon, 27 Aug 2012 07:37:59 -0700 (PDT) schrieb Norbert:

In workbook "planning ticket.xls" cell E8 there is following formula: ='C:\Documents and Settings\Tickets\[8250.xls]Prod.Ticket'!A16

(it looks up value of cell A16 in workbook 8250.xls)

In the event that cell F1 is changed to a new value e.g.: 7722, I'd like to have the formula in E8 changed to: ='C:\Documents and Settings\Tickets\[7722.xls]Prod.Ticket'!A16

try in code module of the worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myPath As String
Dim wbName As String
If Target.Address <> "$F$1" Then Exit Sub
myPath = "'C:\Documents and Settings\Tickets\"
wbName = "[" & [F1] & ".xls]Prod.Ticket'!A16"
Range("E8").Formula = "=" & myPath & wbName
End Sub

Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

your code works fine, thank you for that.

What I did not mention, there are plenty other cells like E8, with the same path in the formula but obviously linking to a different cell.



For example:

E8 ='C:\Documents and Settings\Tickets\[7722.xls]Prod.Ticket'!A16

AL7 ='C:\Documents and Settings\Tickets\[7722.xls]Prod.Ticket'!BT7

AP7 ='C:\Documents and Settings\Tickets\[7722.xls]Prod.Ticket'!A60

and more ...



How can I include those into the code?

As far as I can read your code, string wbName is different all the time, but

I have no knowledge of how to change it.



Regards,

Norbert

Hi Claus,
I found a way, which is most likely the most awkward one but it works:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myPath As String
'Dim wbName As String

If Target.Address <> "$F$1" Then Exit Sub
myPath = "'C:\Documents and Settings\Muhammad\My Documents\PRODUCTION SPECS\"
wbName = "[" & [F1] & ".xls]"
Range("W7").Formula = "=" & myPath & wbName & "Prod.Ticket'!BT7"
Range("AL7").Formula = "=" & myPath & wbName & "Prod.Ticket'!A60"
Range("AP7").Formula = "=" & myPath & wbName & "Prod.Ticket'!A66"
Range("E8").Formula = "=" & myPath & wbName & "Prod.Ticket'!A16"
...
...
...
End sub