469,166 Members | 2,169 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,166 developers. It's quick & easy.

sheet list to return exact name without any trim

232 100+
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
1 1255
MikeTheBike
637 Expert 512MB
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.

Similar topics

4 posts views Thread by Colin Steademan | last post: by
12 posts views Thread by Mike Brashars | last post: by
4 posts views Thread by Bill Borg | last post: by
8 posts views Thread by Tom | last post: by
6 posts views Thread by Andrus | last post: by
8 posts views Thread by Force88 | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.