Prev: Highlight Rows
Next: Select list box entries in multiple rows?
From: terilad on 6 Apr 2010 18:17
Hi,

I have over 100 worksheets in a workbook, through time they have been
reordered about and are now in order alphabetically but the sheet numbers are
all over the place, can I resequence the sheet numbers to sheet1, sheet2 and
so on by the order the sheets are in my workbook, keeping the sheets
alphabetically but change the default sheet numbers to 1, 2, 3, 4 and so on.

Any help would be great.

thanks

Mark
From: FreeBird on 7 Apr 2010 04:16

Hello

If i did catch well, you want to rename the "VBA" name of sheets.
I wrote this below for personal use.
You can simplify it, the important codeline is the last one.


Sub vbp_SheetCodeName_DEMO()
Call vbp_SheetCodeName(ActiveSheet, "test") ', Workbooks("Classeur1"))
End Sub


Sub vbp_SheetCodeName(varSheet As Variant, varSheetCompName As String,
Optional varWorkbook As Variant)
' renascence(a)free.fr
If TypeName(varSheet) = "string" Then
Set varSheet = ActiveWorkbook.Sheets(varSheet)
End If

If Not IsMissing(varWorkbook) And TypeName(varWorkbook) = "string"
Then
Set varWorkbook = Workbooks(varWorkbook)
ElseIf IsMissing(varWorkbook) = True Then
Set varWorkbook = ActiveWorkbook
End If

' the important line :
varWorkbook.VBProject.VBComponents(varSheet.CodeName).name =
varSheetCompName
End Sub

HTH


--
FreeBird
------------------------------------------------------------------------
FreeBird's Profile: 1701
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=193483

http://www.thecodecage.com/forumz

 | 
Pages: 1
Prev: Highlight Rows
Next: Select list box entries in multiple rows?