Rename Worksheets From German to English Sheet1

W

wayne

I have a spreadsheet with VBA code that does not work reliably. Upon
close examination, I find the worksheets are identified as tablle1,
tablle2, etc while the VBA is written in English. Is there a way to
rename the worksheets at the program level? Note: I am referring to the
name you see in the vba editor in front of the name you type into the
worksheet when changing the name.

TIA
--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
 
D

Dave Peterson

Each sheet in a workbook has the name property that you see on the tab and a
codename property that you can use in VBA macros. The codename is much more
difficult for the average user to change, but not impossible to be changed.

If you're in the VBE looking at the project explorer, you'll see the codename
followed by the name (in parens).

Like:
Sheet1 (NameYouSeeInExcel)

You maybe able to make sure your code refers to this codename property instead
of the name property.

Instead of:
With thisworkbook.worksheets("NameYouSeeInExcel")
use
With Sheet1

You can change the .codename property (Show the project explorer, select the
sheet object, hit F4 to show the properties, and then type over the (Name) (with
the ()'s) property.

==========
So....

If the codenames are that tablle#stuff, maybe just renaming the codename will
work for you.

If those tablle# are variables that the developer created, you'll have to change
the code (Edit|replace in all the current project (and step through the
changes--that string could be used for lots of things???).

I have a spreadsheet with VBA code that does not work reliably. Upon
close examination, I find the worksheets are identified as tablle1,
tablle2, etc while the VBA is written in English. Is there a way to
rename the worksheets at the program level? Note: I am referring to the
name you see in the vba editor in front of the name you type into the
worksheet when changing the name.

TIA
--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
Click to expand...
 
W

wayne

Each sheet in a workbook has the name property that you see on the tab and a
codename property that you can use in VBA macros. The codename is much more
difficult for the average user to change, but not impossible to be changed.

If you're in the VBE looking at the project explorer, you'll see the codename
followed by the name (in parens).

Like:
Sheet1 (NameYouSeeInExcel)

You maybe able to make sure your code refers to this codename property instead
of the name property.

Instead of:
With thisworkbook.worksheets("NameYouSeeInExcel")
use
With Sheet1

You can change the .codename property (Show the project explorer, select the
sheet object, hit F4 to show the properties, and then type over the (Name) (with
the ()'s) property.

==========
So....

If the codenames are that tablle#stuff, maybe just renaming the codename will
work for you.

If those tablle# are variables that the developer created, you'll have to change
the code (Edit|replace in all the current project (and step through the
changes--that string could be used for lots of things???).

I have a spreadsheet with VBA code that does not work reliably. Upon
close examination, I find the worksheets are identified as tablle1,
tablle2, etc while the VBA is written in English. Is there a way to
rename the worksheets at the program level? Note: I am referring to the
name you see in the vba editor in front of the name you type into the
worksheet when changing the name.

TIA
--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
Click to expand...
Click to expand...
Thank you David. The code I am trying to use is stuff lik:
Sub Filter_notfinished()
'
' Filter_notfinished Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
With ActiveSheet
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
.Range("J8").Select
End With

End Sub

OR

Sub Filter_notfinished()
'
' Filter_notfinished Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet

AutoFilterMode = False
Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
Range("J8").Select

End Sub

Both of the above work when the worksheet codename is sheet(1), but
neither work when the code sheetname is tablle(1). One fails with
errors in the vba, the other simply doesn't do anything.

I fixed one sheet by creating a new worksheet and copying the contents
of the first into it, but I have several workbooks with the same code
and multiple sheets and was hoping there was a better way.

It seems a bit strange to me that the VBA is in English and seems to
work on the German computers, but English computers have a problem!

Thanks again for your help.

--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
 
D

Dave Peterson

I don't see anything in your code that refers to the name or codename.

If that code is in a General module, then both refer to the activesheet.

So you could use something like:

Sub Filter_notfinished()
'
' Filter_notfinished Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
With worksheets("nameyouseeinthetabinexcel")
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
End With

'or using the codename

With TheCodeNameYouSeeInTheVBE
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
End With

End Sub

=========
But I'm confused with your comment about the codename being "sheet(1)" or
"tablle(1)". Those parens aren't allowed in the codename property.

I'm betting that those are the names you see in the tabs in excel.

Then that With statement would look like:
with worksheets("Sheet1(1)")
or
with worksheets("tablle(1)")

Be careful with your spelling--it has to match exactly. Even space characters
are important!

wayne wrote:
Sub Filter_notfinished()
'
' Filter_notfinished Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
With ActiveSheet
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
.Range("J8").Select
End With

End Sub

OR

Sub Filter_notfinished()
'
' Filter_notfinished Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet

AutoFilterMode = False
Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
Range("J8").Select

End Sub

Both of the above work when the worksheet codename is sheet(1), but
neither work when the code sheetname is tablle(1). One fails with
errors in the vba, the other simply doesn't do anything.

I fixed one sheet by creating a new worksheet and copying the contents
of the first into it, but I have several workbooks with the same code
and multiple sheets and was hoping there was a better way.

It seems a bit strange to me that the VBA is in English and seems to
work on the German computers, but English computers have a problem!

Thanks again for your help.

--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
Click to expand...
 
D

Dave Peterson

You don't have to save the workbook for the code to work ok when you run it.

But there could be differences between worksheets--like you found with the
autofilter (on or off) or worksheet protection or ranges not the same (maybe
..Range("A7:Z7") doesn't have data???).

I don't have a guess without knowing what lines of code are broken and what the
data for that offending worksheet looks like.
I don't see anything in your code that refers to the name or codename.

If that code is in a General module, then both refer to the activesheet.

So you could use something like:

Sub Filter_notfinished()
'
' Filter_notfinished Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
With worksheets("nameyouseeinthetabinexcel")
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
End With

'or using the codename

With TheCodeNameYouSeeInTheVBE
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
End With

End Sub

=========
But I'm confused with your comment about the codename being "sheet(1)" or
"tablle(1)". Those parens aren't allowed in the codename property.

I'm betting that those are the names you see in the tabs in excel.

Then that With statement would look like:
with worksheets("Sheet1(1)")
or
with worksheets("tablle(1)")

Be careful with your spelling--it has to match exactly. Even space characters
are important!
Click to expand...

My bad with the paren's. The sheets are named sheet1 etc. and tablle1 etc.

I spent a couple of days trying to get the code to work in one
spreadsheet, and then copied the modul1 from one workbook to another,
only to have failures. I am now mostly trying to learn why toing the
above does not seem to be possible. Everything looks the same in the
different workbooks excpt for the amount of data. Even the sheetnames
are the same, but when I get the code working in ine workbook, I ended
up with errors in the others, or no action by the script at all.
Stepping through the code looks like the steps are being read, but no
activity in the sheet.

Could I have to save the workbook after changing the VBA before running
it? It doesn't seem to matter while developing the code (I think).

Here is the complete module. Some of the VBA is not used, perhaps from
another project.

' Makro Auto Open runs every time the worksheet is opened
'

Sub Auto_Open()

'Log_Activity ("opened")

Filter_refresh
Rows("8:9999").Select
Selection.EntireRow.Hidden = False
Range("A8").Select

End Sub

<--not used>
Sub Log_Activity(Action)

Application.ScreenUpdating = False
Sheets("Logsheet").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Now()
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Action
Sheets("To-Do's").Select
Application.ScreenUpdating = True

End Sub
<--end not used-->

Sub sort_deadlinedate()
'
' sort_deadline date Macro
' Macro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
Rows("7:9999").Select
Selection.Sort Key1:=Range("H7"), Order1:=xlAscending,
Key2:=Range("F7") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom
Range("H8").Select
End Sub

Sub Filter_activate()

'
' Filter_activate Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
'
With ActiveSheet
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter
.Range("A8").Select
End With

End Sub

Sub Filter_critical()
'
' Filter_critical Makro
' Macro modified by Wayne D Hammond 7/13/2009

With ActiveSheet
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter
.Range("A7:Z7").AutoFilter Field:=3, Criteria1:="x"
.Range("J8").Select
End With

End Sub
Sub Filter_refresh()
'
' Filter_refresh Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
With ActiveSheet
.AutoFilterMode = False
End With

FreezePanes = False
Rows("7:7").Select
FreezePanes = True
Range("A8").Select

' Application.Run "Filter_notfinished"
' On Error Resume Next
' ActiveSheet.ShowAllData
' On Error GoTo 0
' ActiveWindow.FreezePanes = False
' Range("A8").Select
' ActiveWindow.FreezePanes = True
End Sub

Sub Filter_notfinished()
'
' Filter_notfinished Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
With ActiveSheet
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
.Range("J8").Select
End With

End Sub

Sub sort_number()
'
With ActiveSheet
.AutoFilterMode = False
End With
Rows("7:3000").Select
Selection.Sort Key1:=Range("A7"), Order1:=xlAscending,
Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, Key2:=Range("D7") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom

Range("A8").Select
End Sub
Sub sort_inputdate()
'
' sort_inputdate Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
'
Rows("7:3000").Select
Selection.Sort Key1:=Range("B7"), Order1:=xlAscending,
Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, Key2:=Range("D7") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom

Range("A8").Select
End Sub

Sub sort_completiondate()
'
' sort_completion date Makro
' Macro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
'
With ActiveSheet
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:="<>"
.Range("J8").Select
End With
End Sub

The original vba did not check to see if the autofilter was active
before invoking it, just invoked it which seemed to toggle autofilter
instead of turning it on or off according to the module (filter_refresh
is supposed to turn off and filter_activate is supposed to turn on).

Regards,

--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
Click to expand...
 
W

wayne

You don't have to save the workbook for the code to work ok when you run it.

But there could be differences between worksheets--like you found with the
autofilter (on or off) or worksheet protection or ranges not the same (maybe
.Range("A7:Z7") doesn't have data???).

I don't have a guess without knowing what lines of code are broken and what the
data for that offending worksheet looks like.
Click to expand...

Hi Dave,

There are several sheets in the workbook and all are identical to a copy
I made to track my own To Do items. I had all of the vba working fine
on my copy and copied/pasted the code into the main copy on our server
where all of the actions for the plant are tracked. My spreadsheet is
close in size to the main copy (550 items vs 700). When I try and run
the subs in the main copy, they are all broken. The sub names are the
same as before and they work fine on my copy so I am at a loss. One
puzzling thing is that sometimes they work at first and then stop.

The first 7 rows of the spreadsheet being manipulated contain headers
and some conditionally formated cells to act as alarms when actions are
overdue or missing data fields. The Filter_Refresh is supposed to make
sure the "Freezepanes" is set so data can scroll beneath this area,

If you are interested, I can delete the proprietary data from one of the
sheets and send it to you.

Regards,

--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
 
D

Dave Peterson

No thanks to the file.

I don't understand why you have the same code in all the worksheet modules,
though.

I'd move one copy from a worksheet module into a General module. And I'd delete
all the rest.

Since all the macros are the same (exactly the same, right?), I would have to
make sure that all the code is written to work on the activesheet.

And then comes the debugging time.

You'll have to make sure you have as many validity checks as you think are
necessary.

If you think a worksheet could be protected, check for it. If you think that a
range may be smaller/larger or missing completely, check for it.

And on and on and on...
You don't have to save the workbook for the code to work ok when you run it.

But there could be differences between worksheets--like you found with the
autofilter (on or off) or worksheet protection or ranges not the same (maybe
.Range("A7:Z7") doesn't have data???).

I don't have a guess without knowing what lines of code are broken and what the
data for that offending worksheet looks like.
Click to expand...

Hi Dave,

There are several sheets in the workbook and all are identical to a copy
I made to track my own To Do items. I had all of the vba working fine
on my copy and copied/pasted the code into the main copy on our server
where all of the actions for the plant are tracked. My spreadsheet is
close in size to the main copy (550 items vs 700). When I try and run
the subs in the main copy, they are all broken. The sub names are the
same as before and they work fine on my copy so I am at a loss. One
puzzling thing is that sometimes they work at first and then stop.

The first 7 rows of the spreadsheet being manipulated contain headers
and some conditionally formated cells to act as alarms when actions are
overdue or missing data fields. The Filter_Refresh is supposed to make
sure the "Freezepanes" is set so data can scroll beneath this area,

If you are interested, I can delete the proprietary data from one of the
sheets and send it to you.

Regards,

--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
Click to expand...
 
W

wayne

No thanks to the file.

I don't understand why you have the same code in all the worksheet modules,
though.

I'd move one copy from a worksheet module into a General module. And I'd delete
all the rest.

Since all the macros are the same (exactly the same, right?), I would have to
make sure that all the code is written to work on the activesheet.

And then comes the debugging time.

You'll have to make sure you have as many validity checks as you think are
necessary.

If you think a worksheet could be protected, check for it. If you think that a
range may be smaller/larger or missing completely, check for it.

And on and on and on...
Click to expand...

Sorry if I misled you, copied to several workbooks, not sheets. The
workbooks have the exact same format but different data.



--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
 
D

Dave Peterson

If I had the same layout in several worksheets (even in several workbooks), I
wouldn't duplicate the code in each of the workbooks.

I'd create a single workbook (or addin) that contained all the code. Then each
time I needed any of the macros, I'd run the code from that workbook with the
macros.

But the suggestions for doing validity checks still stands.
No thanks to the file.

I don't understand why you have the same code in all the worksheet modules,
though.

I'd move one copy from a worksheet module into a General module. And I'd delete
all the rest.

Since all the macros are the same (exactly the same, right?), I would have to
make sure that all the code is written to work on the activesheet.

And then comes the debugging time.

You'll have to make sure you have as many validity checks as you think are
necessary.

If you think a worksheet could be protected, check for it. If you think that a
range may be smaller/larger or missing completely, check for it.

And on and on and on...
Click to expand...

Sorry if I misled you, copied to several workbooks, not sheets. The
workbooks have the exact same format but different data.

--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
Click to expand...
 
W

wayne

If I had the same layout in several worksheets (even in several workbooks), I
wouldn't duplicate the code in each of the workbooks.

I'd create a single workbook (or addin) that contained all the code. Then each
time I needed any of the macros, I'd run the code from that workbook with the
macros.

But the suggestions for doing validity checks still stands.
Click to expand...

Thank you for all of your help. The workbooks are not in the same
location though so using the vba from one workbook is not an option.

Regards,

--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
 
D

Dave Peterson

If you're running the macro against the activesheet, then it doesn't matter
where that file is located--as long as the file is open and the correct
worksheet is active.
If I had the same layout in several worksheets (even in several workbooks), I
wouldn't duplicate the code in each of the workbooks.

I'd create a single workbook (or addin) that contained all the code. Then each
time I needed any of the macros, I'd run the code from that workbook with the
macros.

But the suggestions for doing validity checks still stands.
Click to expand...

Thank you for all of your help. The workbooks are not in the same
location though so using the vba from one workbook is not an option.

Regards,

--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

if then help please 23

Top