Using InputBox to change file links

[fillip]

I am trying to use the InputBox function to automatically edit a number of cells with a similar file link. The file path remains constant but the file name changes in each case, by a 10 digit project number. I figure the best way would have the user input the project number into an input box and the response used with the replace function to amend the link. I'm also a bit of a Visual Basic novice.

[debasisdas]

Which cells are you editing? Is it in Excel or any other grid control?

[fillip]

It is in Excel. The formula is similar to the one below which relates to about 9 different groups of cells.

=IF(OR('J:\SHAR ED\General_Serv ices_Shared\DES IGN & CONSTRUCTION\Ca pital Projects Admin\REPORTS\L atest Reports\[Latest report 1200006013.xls]Full Report'!$H$19=" r",'J:\SHARED\G eneral_Services _Shared\DESIGN & CONSTRUCTION\Ca pital Projects Admin\REPORTS\L atest Reports\[Latest report 1200006013.xls]Full Report'!$H$20=" r"),"R",IF(OR(' J:\SHARED\Gener al_Services_Sha red\DESIGN & CONSTRUCTION\Ca pital Projects Admin\REPORTS\L atest Reports\[Latest report 1200006013.xls]Full Report'!$H$19=" a",'J:\SHARED\G eneral_Services _Shared\DESIGN & CONSTRUCTION\Ca pital Projects Admin\REPORTS\L atest Reports\[Latest report 1200006013.xls]Full Report'!$H$20=" a"),"A","G") )

[Killer42]

Sorry to see you're not getting much of a response on this. I guess things get pretty quiet over Christmas.

I think probably the simplest thing would be to write a function to do what you want, then just use that function in the formula. For instance the cell could just say something like: =MyFunction()

The function, written in VBA, can prompt the user to enter the number, then open the appropriate worksheet, check the appropriate values, make decisions, and return the appropriate value.

When I have time, I'll try to help you with the code for the function if you like.

[fillip]

Thanks Killer42. That would be brilliant.