VBA Opening worksheet, name changes

J

Jeff

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
 
B

Bob Phillips

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")
 
J

Jeff

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.
 
J

Jeff

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.
 
D

Dave Peterson

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.
 
Top