Create list of tab names

[bobhc]

Good day all

I have two pieces of code to create a list of tab names in a new work sheet, the code is linked to an active x button on an empty sheet but it creates the list in a fresh sheet I was hoping to keep the list and button on the same sheet.

The first piece of code creates the list but if there is a = in the beginning of the tab name the code will hang at that tab.

The second piece of code takes care of the problem = in tab names......but I do not have the faintest idea what the @ does or how the code works.

Could someone a) explain the code and b) point out if there is a way to keep the button on the same sheet as the tab list.

[pre]

Code for tabs without the = sign
Set NewSheet = Sheets.Add(Type:=xlWorksheet)		

For i = 1 To Sheets.Count		

NewSheet.Cells(i, 1).Value = Sheets(i).Name		

Next i
Code for tabs with the = sign
Private Sub CommandButton1_Click()				

Set NewSheet = Sheets.Add(Type:=xlWorksheet)				

For i = 1 To Sheets.Count				

With NewSheet.Cells(i, 1)
.NumberFormat = "@" 'text
.Value = CStr(Sheets(i).Name)
End With				

Next i				

End Sub

[/pre]

 

[Luke M]

Hi bobhc!


a)

The "@" symbol is the notation XL uses to indicate a cell format of "text". This can be seen when you format a cell in a workbook to Text, and then go to the custom format. Examples of formats are mm/dd/yyyy, @, $0.00, etc.


So, the code if formatting the cell to "text" before putting in the sheet name. That way, if the sheet name as a "=" symbol, it won't try to turn it into a formula.


b) To keep the data on same sheet, we can change the first line to

Set NewSheet = ActiveSheet

Complete code w/ comments

[pre]Private Sub CommandButton1_Click()
Dim NewSheet As Worksheet
'Defines which sheet we are going to add info to
'An assumption is made that the active sheet is a worksheet
'since this is to be run from an ActiveX control, this should be a
'safe assumption to make
Set NewSheet = ActiveSheet

'Defines upper limit of i as being total count of sheets
'Note that these are "sheets", not "worksheets". The difference is sometimes
'you have charts which are on a sheet of their own
For i = 1 To ThisWorkbook.Sheets.Count
'All of the following apply to a specific cell within NewSheet
With NewSheet.Cells(i, 1)
'Set the number format of the cell to "text"
.NumberFormat = "@" 'text
'Set the value of cell. CStr converts the name of the sheet
'into a true string
.Value = CStr(Sheets(i).Name)
End With
Next i
End Sub

[/pre]

 

[bobhc]

Good evening Luke M


Many thanks for the clear explanation and code update, works a treat, you have my thanks

 

[Luke M]

No problem bob, glad to help.