I am creating an Excel file with several sheets on it. To make this as brief
as possible, I am (sadly) the most technologically savvy person in my
department. I need to find a way to create a macro that others can easily
change, so that when they change the file name, the macro code doesn't have
to be changed- it just has to be run.

For example:

I make "Assessment Template", and create a macro that works with it. That
macro has to open another Excel file, so that path is written in the macro
code. Just in this example, when I open this on another computer, I will have
to change the code- something other professors won't do.

What I was wondering is, is there a way to create a form in Excel that the
professor fills out before they run the macro. It would have the following:

the name of the data file
the name of the sheet the data is being transferred to
the number of rows information will be taken from from
the number of columns the information will be special pasted into

And so on...

Is it possible? If so, where would I find out how to do this?

Thanks!

This is all possible. You could do this one of several ways -

One way would be to have some cells on a worksheet in the workbook where the
user could simply type in the information needed and then click a button that
would run a macro that would read that information from the worksheet and use
it in your macro to find the data in the other workbook and manipulate it as
you need.

Just a couple of snippets of how the could would work here. Assuming they
enter the full path to the other file on a sheet named [SysInfoSheet] into
cell A1, then the code might look something like
....
Dim dataWBName As String
dataWBName = ThisWorkbook.Worksheets("SysInfoSheet").Range("A1" )
Workbooks.Open dataWBName
....

Another way to just get a file name is to let the user browse for it in the
File Open Dialog, and in code it looks something like this:

Dim dataWBName As Variant
dataWBName = Application.GetOpenFilename
If dataWBName = False Then
'probably get out, they used [Cancel] or [x] to close the
'dialog without choosing a file
End If
Workbooks.Open dataWBName
.....

If you feel I can help you more with this, feel free to contact me at
(remove spaces)
Help From @ JLatham Site.com


"SeventFloorProfessor" wrote:

I am creating an Excel file with several sheets on it. To make this as brief
as possible, I am (sadly) the most technologically savvy person in my
department. I need to find a way to create a macro that others can easily
change, so that when they change the file name, the macro code doesn't have
to be changed- it just has to be run.

For example:

I make "Assessment Template", and create a macro that works with it. That
macro has to open another Excel file, so that path is written in the macro
code. Just in this example, when I open this on another computer, I will have
to change the code- something other professors won't do.

What I was wondering is, is there a way to create a form in Excel that the
professor fills out before they run the macro. It would have the following:

the name of the data file
the name of the sheet the data is being transferred to
the number of rows information will be taken from from
the number of columns the information will be special pasted into

And so on...

Is it possible? If so, where would I find out how to do this?

Thanks!