Hi,

can anybody tell me whether and if yes how it is possible to re-name a style
in VBA? I want the style-definition to stay unchanged and just change the
name.

Thanks in advance,
Marcel

Hi Marcel,

AFAIK you cannot rename a style but you can add a new style "based on" some
cell, eg

With ActiveCell
.ClearFormats
.Style = "OldStyle"
End With
ActiveWorkbook.Styles.Add Name:="NewSyle", Basedon:=ActiveCell

If the idea is to replace existing cells formatted with OldStyle with
NewStyle you'll need to loop all cells in the usedrange of each sheet. Could
be time consuming if you don't know how to limit a search, though there's a
lot you can do to speed up the process. But that's another subject!

Regards,
Peter T


"Marcel Marien" wrote in message
...
Hi,

can anybody tell me whether and if yes how it is possible to re-name a
style
in VBA? I want the style-definition to stay unchanged and just change the
name.

Thanks in advance,
Marcel

Dear Peter,

thanks a lot for your input. It seesm I have to look for a different
solution. Defining a new style and then replacing the old style is too
cumbersome and too much fraught with hazard. (I thought since in WinWord one
can simply re-name a style and Excel comes out of the same stable, so to
say, it might have been programmed according to a similar philosophy).

Greetings,
Marcel


AFAIK you cannot rename a style but you can add a new style "based on"
some
cell, eg

With ActiveCell
.ClearFormats
.Style = "OldStyle"
End With
ActiveWorkbook.Styles.Add Name:="NewSyle", Basedon:=ActiveCell

If the idea is to replace existing cells formatted with OldStyle with
NewStyle you'll need to loop all cells in the usedrange of each sheet.
Could
be time consuming if you don't know how to limit a search, though there's
a
lot you can do to speed up the process. But that's another subject!

Regards,
Peter T


"Marcel Marien" wrote in message
...
Hi,

can anybody tell me whether and if yes how it is possible to re-name a
style
in VBA? I want the style-definition to stay unchanged and just change the
name.

Thanks in advance,
Marcel

I don't understand why it would be that difficult to use Peter's suggestion.

Option Explicit
Sub testme02()

Dim myCell As Range
Dim myOldStyleName As String
Dim myNewStyleName As String
Dim TestStyle As Style

myOldStyleName = "asdf"
myNewStyleName = "qwer"

Set TestStyle = Nothing
On Error Resume Next
Set TestStyle = ActiveWorkbook.Styles(myOldStyleName)
On Error GoTo 0

If TestStyle Is Nothing Then
MsgBox myOldStyleName & " isn't used in this workbook"
Else
With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
myCell.Style = TestStyle.Name
.Parent.Styles.Add Name:=myNewStyleName, BasedOn:=myCell
TestStyle.Delete
myCell.Clear
End With
End If

End Sub

Marcel Marien wrote:

Dear Peter,

thanks a lot for your input. It seesm I have to look for a different
solution. Defining a new style and then replacing the old style is too
cumbersome and too much fraught with hazard. (I thought since in WinWord one
can simply re-name a style and Excel comes out of the same stable, so to
say, it might have been programmed according to a similar philosophy).

Greetings,
Marcel

AFAIK you cannot rename a style but you can add a new style "based on"
some
cell, eg

With ActiveCell
.ClearFormats
.Style = "OldStyle"
End With
ActiveWorkbook.Styles.Add Name:="NewSyle", Basedon:=ActiveCell

If the idea is to replace existing cells formatted with OldStyle with
NewStyle you'll need to loop all cells in the usedrange of each sheet.
Could
be time consuming if you don't know how to limit a search, though there's
a
lot you can do to speed up the process. But that's another subject!

Regards,
Peter T


"Marcel Marien" wrote in message
...
Hi,

can anybody tell me whether and if yes how it is possible to re-name a
style
in VBA? I want the style-definition to stay unchanged and just change the
name.

Thanks in advance,
Marcel





--

Dave Peterson

Dear Dave,

Thank you VERY much for the program (I wouldn't have come up myself with
it).

What is so "difficult" about replacing the styles is, that
1) there are about 100 different styles
2) I want them to have meaningful names
3) I want different people to define own categories and therefore use
self-defined names

My original idea was to have all those categories described in one list and
then run a macro that utilizes the list to re-name the styles accordingly.
Well I'll have to re-think the concept once again.

Marcel

"Dave Peterson" schrieb im Newsbeitrag
...
I don't understand why it would be that difficult to use Peter's
suggestion.

Option Explicit
Sub testme02()

Dim myCell As Range
Dim myOldStyleName As String
Dim myNewStyleName As String
Dim TestStyle As Style

myOldStyleName = "asdf"
myNewStyleName = "qwer"

Set TestStyle = Nothing
On Error Resume Next
Set TestStyle = ActiveWorkbook.Styles(myOldStyleName)
On Error GoTo 0

If TestStyle Is Nothing Then
MsgBox myOldStyleName & " isn't used in this workbook"
Else
With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1,
1)
myCell.Style = TestStyle.Name
.Parent.Styles.Add Name:=myNewStyleName, BasedOn:=myCell
TestStyle.Delete
myCell.Clear
End With
End If

End Sub

Marcel Marien wrote:

Dear Peter,

thanks a lot for your input. It seesm I have to look for a different
solution. Defining a new style and then replacing the old style is too
cumbersome and too much fraught with hazard. (I thought since in WinWord
one
can simply re-name a style and Excel comes out of the same stable, so to
say, it might have been programmed according to a similar philosophy).

Greetings,
Marcel

AFAIK you cannot rename a style but you can add a new style "based on"
some
cell, eg

With ActiveCell
.ClearFormats
.Style = "OldStyle"
End With
ActiveWorkbook.Styles.Add Name:="NewSyle", Basedon:=ActiveCell

If the idea is to replace existing cells formatted with OldStyle with
NewStyle you'll need to loop all cells in the usedrange of each sheet.
Could
be time consuming if you don't know how to limit a search, though
there's
a
lot you can do to speed up the process. But that's another subject!

Regards,
Peter T


"Marcel Marien" wrote in message
...
Hi,

can anybody tell me whether and if yes how it is possible to re-name a
style
in VBA? I want the style-definition to stay unchanged and just change
the
name.

Thanks in advance,
Marcel





--

Dave Peterson

I'm not sure how you'd handle #3, but you could create a list of the old style
names (A2:Axx) and put the new style names in B2:Bxx and run a macro that loops
through those cells:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim WksList As Worksheet

Set WksList = ThisWorkbook.Worksheets("myList")

With WksList
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
.Offset(0, 2).Value _
= RenameStyles(.Value, .Offset(0, 1).Value, ActiveWorkbook)
End With
Next myCell

End Sub

Function RenameStyles(myOldStyleName As String, myNewStyleName As String, _
Optional myWorkbook As Workbook) As String

Dim myCell As Range
Dim TestStyle As Style

If myWorkbook Is Nothing Then
Set myWorkbook = ActiveWorkbook
End If

Set TestStyle = Nothing
On Error Resume Next
Set TestStyle = myWorkbook.Styles(myOldStyleName)
On Error GoTo 0

If TestStyle Is Nothing Then
RenameStyles = "Failed"
Else
With myWorkbook.Worksheets(1)
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
myCell.Style = TestStyle.Name
.Parent.Styles.Add Name:=myNewStyleName, BasedOn:=myCell
TestStyle.Delete
myCell.Clear
End With
RenameStyles = "Renamed"
End If

End Function


Marcel Marien wrote:

Dear Dave,

Thank you VERY much for the program (I wouldn't have come up myself with
it).

What is so "difficult" about replacing the styles is, that
1) there are about 100 different styles
2) I want them to have meaningful names
3) I want different people to define own categories and therefore use
self-defined names

My original idea was to have all those categories described in one list and
then run a macro that utilizes the list to re-name the styles accordingly.
Well I'll have to re-think the concept once again.

Marcel

"Dave Peterson" schrieb im Newsbeitrag
...
I don't understand why it would be that difficult to use Peter's
suggestion.

Option Explicit
Sub testme02()

Dim myCell As Range
Dim myOldStyleName As String
Dim myNewStyleName As String
Dim TestStyle As Style

myOldStyleName = "asdf"
myNewStyleName = "qwer"

Set TestStyle = Nothing
On Error Resume Next
Set TestStyle = ActiveWorkbook.Styles(myOldStyleName)
On Error GoTo 0

If TestStyle Is Nothing Then
MsgBox myOldStyleName & " isn't used in this workbook"
Else
With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1,
1)
myCell.Style = TestStyle.Name
.Parent.Styles.Add Name:=myNewStyleName, BasedOn:=myCell
TestStyle.Delete
myCell.Clear
End With
End If

End Sub

Marcel Marien wrote:

Dear Peter,

thanks a lot for your input. It seesm I have to look for a different
solution. Defining a new style and then replacing the old style is too
cumbersome and too much fraught with hazard. (I thought since in WinWord
one
can simply re-name a style and Excel comes out of the same stable, so to
say, it might have been programmed according to a similar philosophy).

Greetings,
Marcel

AFAIK you cannot rename a style but you can add a new style "based on"
some
cell, eg

With ActiveCell
.ClearFormats
.Style = "OldStyle"
End With
ActiveWorkbook.Styles.Add Name:="NewSyle", Basedon:=ActiveCell

If the idea is to replace existing cells formatted with OldStyle with
NewStyle you'll need to loop all cells in the usedrange of each sheet.
Could
be time consuming if you don't know how to limit a search, though
there's
a
lot you can do to speed up the process. But that's another subject!

Regards,
Peter T


"Marcel Marien" wrote in message
...
Hi,

can anybody tell me whether and if yes how it is possible to re-name a
style
in VBA? I want the style-definition to stay unchanged and just change
the
name.

Thanks in advance,
Marcel





--

Dave Peterson

--

Dave Peterson

Hello again,

I just came up with one more related question: Does anybody know how and
where the style-definitions are stored? Maybe it is nevertheless possible to
edit it there and change the style-name...

Marcel



"Peter T" <peter_t@discussions schrieb im Newsbeitrag
...
Hi Marcel,

AFAIK you cannot rename a style but you can add a new style "based on"
some
cell, eg

With ActiveCell
.ClearFormats
.Style = "OldStyle"
End With
ActiveWorkbook.Styles.Add Name:="NewSyle", Basedon:=ActiveCell

If the idea is to replace existing cells formatted with OldStyle with
NewStyle you'll need to loop all cells in the usedrange of each sheet.
Could
be time consuming if you don't know how to limit a search, though there's
a
lot you can do to speed up the process. But that's another subject!

Regards,
Peter T


"Marcel Marien" wrote in message
...
Hi,

can anybody tell me whether and if yes how it is possible to re-name a
style
in VBA? I want the style-definition to stay unchanged and just change the
name.

Thanks in advance,
Marcel