linking to a cell...suggestion required

[monika]

I am slightly confused on this part and maybe someone who
has done a similar thing can help guide me.
I have to take values for months (in each cell) and
material from another workbook. SO I need to link a cell
with another workbook. I want to give user the facilty to
open the workbook and select a value from it. How can I
automate this ??? There are 12 values in 12 cells that he
needs to put here. I cannot hardcode hte process because
the input workbook for this case will be volatile. Hence I
am giving an option to the user to select the value, bu
opening the workbook himself and going to the particular
cell.

How can I achieve this...I am slightly confused to find a
sophisticated method for this thing.

Mar-04 Apr-04 May-04 Jun-04 Q4
MOnth 0.45 0.445 0.44 0.435 0.425
Material % 33% 33% 33% 33% 33%

Thanks a lot ...

 

[Patrick Molloy]

use the application.getopenfilename method to get the
file.
Use the application Inputbox to get the range, then save
the data...


Sub test()
Dim fn As String
Dim wb As Workbook
Dim Target As Range
Dim sTargetValue As String
fn = Application.GetOpenFilename()

Set wb = Workbooks.Open(fn)

Set Target =
Application.InputBox("Select A cell...", , , , , , , 8)

sTargetValue = Target.Resize(1, 1).Value

wb.Close False


End Sub


The inputbox allows th especifaction of what TYPE of
input is required. Type '8' is a cell reference.
I use the Resize method on the target range to ensure
that only one cell's value is passed - in case more than
one cell is selected. I use Workbook object to give
better control of the workbook that we open

HTH

Patrick Molloy
Microsoft Excel MVP

 

[monika]

Thanks for the response...

I couldnt understadn why so many commas are put :
Application.InputBox("Select A cell...", , , , , , , 8)
if i remove the comma's it gives error...!

I am still not clear how can i incorporate this in my
macro. THe situation is i am running my macro and creating
a some sheets. at one particular point I have to ask these
12 values from the user.

So does it mean that I need to set this inputbox 12 times?
thanks tremendously.