Please help with code
 

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

Please help with code
 

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

Please help with code
 

On Monday, August 27, 2012 6:14:34 PM UTC+2, Claus Busch wrote:
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

Please help with code
 

On Tuesday, August 28, 2012 9:18:39 AM UTC+2, Norbert wrote:
On Monday, August 27, 2012 6:14:34 PM UTC+2, Claus Busch wrote:

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