Yep. That's a problem that I didn't think about!
I think the only way around it is to look at every cell in the usedrange of the
worksheet and keep track of the style that was used. Then apply the new style
to those cells.
The good news is that it makes the code much easier. Just change the name of
the style while the loop is running. Then after all the cells on all the sheets
are done, delete all the old names.
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim WksList As Worksheet
Dim wks As Worksheet
Dim res As Variant
Set WksList = ThisWorkbook.Worksheets("myList")
With WksList
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each wks In ActiveWorkbook.Worksheets
For Each myCell In wks.UsedRange.Cells
res = Application.Match(myCell.Style.Name, myRng, 0)
If IsError(res) Then
'not on the list, do nothing
Else
myCell.Style = myRng(res).Offset(0, 1).Value
End If
Next myCell
Next wks
'now clean up those old style names
On Error Resume Next
For Each myCell In myRng.Cells
ActiveWorkbook.Styles(myCell.Value).Delete
Next myCell
On Error GoTo 0
End Sub
I think that my other response was interesting at best, but really dumb!
Marcel Marien wrote:
Hello Dave,
I finally managed, to try out the program you suggested. Unfortunately it
operates beyond my present "Excel horizon" and I do not understand all of
it, therefore I can not give you a very detailled feedback.
I filled the cells A2:A6 with existing style names and the cells B2:B6 with
intended new names. The program properly defined the new styles based on the
old styles and then deleted the old styles. However, it did not assign the
new style to those cells that were originally formated with the old style
before deleting the style - and since I can't fully comprehend it, I am not
sure whether it was at all designed to do so. Of course, assigning the new
stlye to the respective cells would be part of what needs to be accomplished
if I want to rename the existing styles.
In any case, thank you very much for your input. I am going to study your
program some more time and maybe I manage to figure more of it out.
best regards,
Marcel
"Dave Peterson" schrieb im Newsbeitrag
...
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
--
Dave Peterson