By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

sheet list to return exact name without any trim

100+
P: 232
Expand|Select|Wrap|Line Numbers
  1. Sub SheetNames()
  2.     Columns(1).Insert
  3.     For i = 1 To Sheets.Count
  4.         Cells(i, 1) = Sheets(i).Name
  5.     Next i
  6. End Sub
This function returns sheet names very well but i want exact name without any trim for example for sheet name 0085 it returns 85 but i need same name to be returned i used cstr function but that still returns the same 85
kindly help
access 2007
Thanx in advance
Jun 13 '12 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 634
Hi

I thing this may solve the proble
Expand|Select|Wrap|Line Numbers
  1. Sub SheetNames() 
  2.     Columns(1).Insert
  3.     For i = 1 To Sheets.Count
  4.         Cells(i, 1).NumberFormat = "@"
  5.         Cells(i, 1) = Sheets(i).Name
  6.     Next iEnd Sub 
In short, the string is returned to the spreadsheet but the sheet then assumes it is a number, so you need to change the format to the Cell, in this example it formats it as text.

Alternatively you could format the cell with 4 leading zeros ie
Expand|Select|Wrap|Line Numbers
  1. Columns(1).Insert
  2.     For i = 1 To Sheets.Count
  3.         Cells(i, 1).NumberFormat = "0000"
  4.         Cells(i, 1) = Sheets(i).Name
  5.     Next i
HTH


MTB
Jun 13 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.