VBA Opening worksheet, name changes
 

I have a VBA code - that opens a worksheet, then gets data. The problem is
that the worksheet name can change, then it won't open. Is there anyway to
save the worksheet name as some variable, that can reference the worksheet
name/location + will automatically update?

Thanks

Use the codename not the Excel name.

This is usually the same, Sheet1, Sheet2, etc, but it doesn't change if the
user changes the Excel name.

You use it like

Sheet1.Range("A1")

instead of

Worksheets("Sheet1").Range("A1")


--
HTH

Bob Phillips

"Jeff" wrote in message
...
I have a VBA code - that opens a worksheet, then gets data. The problem
is
that the worksheet name can change, then it won't open. Is there anyway
to
save the worksheet name as some variable, that can reference the worksheet
name/location + will automatically update?

Thanks

If I set a variable equal to the sheet, the sheet will not change but I need
to open the worksheet name. So I need the worksheet name, not the sheet.

My Code

ChDir "C:\Illustrator"

Workbooks.Open FileName:= _
"C:\Illustrator\COI.xls"
Sheets("MNS").Select

So the file "COI" is what could change.


"Bob Phillips" wrote:

Use the codename not the Excel name.

This is usually the same, Sheet1, Sheet2, etc, but it doesn't change if the
user changes the Excel name.

You use it like

Sheet1.Range("A1")

instead of

Worksheets("Sheet1").Range("A1")


--
HTH

Bob Phillips

"Jeff" wrote in message
...
I have a VBA code - that opens a worksheet, then gets data. The problem
is
that the worksheet name can change, then it won't open. Is there anyway
to
save the worksheet name as some variable, that can reference the worksheet
name/location + will automatically update?

Thanks

What would cause it to change, and how do you know?

--
HTH

Bob Phillips

"Jeff" wrote in message
...
If I set a variable equal to the sheet, the sheet will not change but I
need
to open the worksheet name. So I need the worksheet name, not the sheet.

My Code

ChDir "C:\Illustrator"

Workbooks.Open FileName:= _
"C:\Illustrator\COI.xls"
Sheets("MNS").Select

So the file "COI" is what could change.


"Bob Phillips" wrote:

Use the codename not the Excel name.

This is usually the same, Sheet1, Sheet2, etc, but it doesn't change if
the
user changes the Excel name.

You use it like

Sheet1.Range("A1")

instead of

Worksheets("Sheet1").Range("A1")


--
HTH

Bob Phillips

"Jeff" wrote in message
...
I have a VBA code - that opens a worksheet, then gets data. The
problem
is
that the worksheet name can change, then it won't open. Is there
anyway
to
save the worksheet name as some variable, that can reference the
worksheet
name/location + will automatically update?

Thanks

Someone could change the filename.

Since "COI" is the filename, that portion of the code would have to be
updated to open the correct worksheet.

"Bob Phillips" wrote:

What would cause it to change, and how do you know?

--
HTH

Bob Phillips

"Jeff" wrote in message
...
If I set a variable equal to the sheet, the sheet will not change but I
need
to open the worksheet name. So I need the worksheet name, not the sheet.

My Code

ChDir "C:\Illustrator"

Workbooks.Open FileName:= _
"C:\Illustrator\COI.xls"
Sheets("MNS").Select

So the file "COI" is what could change.


"Bob Phillips" wrote:

Use the codename not the Excel name.

This is usually the same, Sheet1, Sheet2, etc, but it doesn't change if
the
user changes the Excel name.

You use it like

Sheet1.Range("A1")

instead of

Worksheets("Sheet1").Range("A1")


--
HTH

Bob Phillips

"Jeff" wrote in message
...
I have a VBA code - that opens a worksheet, then gets data. The
problem
is
that the worksheet name can change, then it won't open. Is there
anyway
to
save the worksheet name as some variable, that can reference the
worksheet
name/location + will automatically update?

Thanks

I don't think you'll ever be able to stop users from changing the filename or
even moving it to a different location.

But you could just prompt the user for the filename and open it after they give
it to you.

dim wkbk as workbook
dim myFilename as variant

myfilename= application.getopenfilename(filefilter:="Excel Files, *.xls")
if myfilename = false then
'user hit cancel
'what should happen here
exit sub '??
end if

set wkbk = workbooks.open(filename:=myfilename)

application.goto wkbk.worksheets("MNS").range("a1")

====
You can prevent them from changing the worksheet name (not the workbook name)
via:
tools|Protection|protect workbook
Check Structure
(leave windows unchecked)

But the user can't change the order of the worksheets, rename worksheets, or
insert/delete worksheets.




Jeff wrote:

Someone could change the filename.

Since "COI" is the filename, that portion of the code would have to be
updated to open the correct worksheet.

"Bob Phillips" wrote:

What would cause it to change, and how do you know?

--
HTH

Bob Phillips

"Jeff" wrote in message
...
If I set a variable equal to the sheet, the sheet will not change but I
need
to open the worksheet name. So I need the worksheet name, not the sheet.

My Code

ChDir "C:\Illustrator"

Workbooks.Open FileName:= _
"C:\Illustrator\COI.xls"
Sheets("MNS").Select

So the file "COI" is what could change.


"Bob Phillips" wrote:

Use the codename not the Excel name.

This is usually the same, Sheet1, Sheet2, etc, but it doesn't change if
the
user changes the Excel name.

You use it like

Sheet1.Range("A1")

instead of

Worksheets("Sheet1").Range("A1")


--
HTH

Bob Phillips

"Jeff" wrote in message
...
I have a VBA code - that opens a worksheet, then gets data. The
problem
is
that the worksheet name can change, then it won't open. Is there
anyway
to
save the worksheet name as some variable, that can reference the
worksheet
name/location + will automatically update?

Thanks







--

Dave Peterson