Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet CodeName Property (Excel 97)

    Is it possible to change the CodeName property of a worksheet during runtime? I can change it in the Properties window in the VB editor, but are unable to change it during runtime.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    It is not possible to change the CodeName of a worksheet (or Char, Workbook etc) at runtime by use of VBA. It can only be changed through VBIDE at design time, and probably for a very good reason. It is very dangerous to start changing the codename of objects unless you know exactly what you are doing.

    HOWEVER, at you own risk you can have your VBA code piggyback the design time environment by setting a reference to Visual Basic for Applications Extensibility (Tools, References etc.). Once you have that in place the following code example will change the code name of Sheet1 to

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    Thanks Andrew
    I hear what you say, but let me explain why I want to change the codename.

    If the CodeName of a worksheet is say Sheet1 and you copy that sheet, the new CodeName is Sheet11. If you copy that sheet again the CodeName becomes Sheet111 and so on. Eventualy the CodeName becomes to long and it cause Excel to crash.

    In some of my files I always make a copy of the sheet with the latest information using VBA code. It works well until excel crashes because of the CodeName that is to long.

    I hope you can help.

    Frits

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    Hi Frits,

    One possible solution might be to insert a new sheet, and then copy the data to that sheet, depending on the amount of data you need to copy.

    Andrew C

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    Frits,

    Why not make a "roll-up" sheet with links to the other sheets that update with the latest information? Unless you are trying to save the contents of a worksheet as a representation of data at a static point for future reference, I cannot figure why you would want to keep creating copies of the worksheet. If you could explain more about why you are wanting to do this, it could help with the methodology of building an alternative solution to changing the codename.

  6. #6
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    Mike

    I will try to explain it better.

    I use the spreadsheet to fill in information on a monthly basis. The information that I add to the sheet and the formatting of the the sheet can change monthly. At the begining of a month I make a copy of last month's sheet and just clear some of the fields and I am ready with the current formatting and information. To prevent the file from becomming to big I yearly move all the sheets for that year to another file and give it a different name.

    From what I have learned now, I will rather add a new sheet and then copy all the info including formats to the new sheet. But then I loose all the page setup information.

    Any other ideas?

    Frits

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    Frits,

    If you copy using "Move or Copy Sheet" (make sure you tick the "Create a copy" box) rather than selecting, copying and pasting, the whole page is copied, including formats.

    If any cells have more than 255 characters, you will get a message when you do this, warning of truncation.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    Geoff

    Copying the entire sheet as you explained is my problem. If you keep on copying the newest sheet you end up with a sheet CodeName that is to long and that causes XL to crash. Please see #Post51372

    Frits

  9. #9
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    Frits,

    Sorry, I wasn't quite clear that that was the method you used to copy. I should have implied that.

    However, If I create the new worksheet at the end, I don't get that problem- the codename is just the next number in the series. I only have your problem when I create a new sheet, and insert it between 2 existing sheets.

    If I create it at the end, and then move it between 2 existing sheets. it keeps the codename.

    Just to make it totally clerar. If I have Sheet1, Sheet2 and Sheet3, and I copy Sheet2 to the end, it creates Sheet4. If I move Sheet4 to before Sheet2, it still reaains as Sheet4. However, If I copy a sheet before Sheet2, it creates Sheet11.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    <P ID="edit"><FONT SIZE=-1>Edited by gwhitfield on 06-Jul-01 06:52.</FONT></P>Long line in "pre" tags split

    Frits,

    Why not save the workbook under a new name every month, similar to what you are doing with the yearly version?

    I have a similar workbook which imports 6 text files into 6 different sheets the sums and analyzes various data in 3 other sheets.
    Each sheet contains sales and inventory data for one restaurant. At the end of the month, I save the workbook under the month name and the year, then start over fresh.

    I am using this code to prompt the user to do this when the Workbook_BeforeClose event fires:

    <pre>Public Sub Workbook_BeforeClose(Cancel As Boolean)
    If ThisWorkbook.Name <> "Sales.xls" Then End
    tiddly = Month(ThisWorkbook.Sheets("Totals").Range("H1").Va lue)
    If Month(Now) > tiddly Then Loc.Show
    End Sub</pre>


    Loc is a userform. If the user clicks OK therefore wanting to save the workbook under the new name then the following code runs:

    <pre>Public Sub LocDown()
    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim fish As Date
    Dim catch As String
    fish = ThisWorkbook.Sheets("Totals").Range("H1").Value
    catch = MonthName(Month(fish)) & " " & Year(fish)
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
    "C:Accounting 4.01ServerIncomingSalesSales.xls", FileFormat:=xlNormal, _
    Password:=""
    For Each ws In ThisWorkbook.Sheets
    For Each qt In ws.QueryTables
    With qt
    .RefreshOnFileOpen = False
    End With
    Next
    Next
    Sheets("Totals").Select
    ActiveWorkbook.SaveAs Filename:= _
    "C:Accounting 4.01ServerPrevious Months" & catch & ".xls", FileFormat:= _
    xlNormal, Password:=""
    Unload Loc
    hook = MsgBox(" Information for " & catch & " has been saved under the filename _
    'C:Accounting 4.01ServerPrevious Months" & catch & ".xls", vbOKOnly, _
    "Previous Month Saved")
    End Sub</pre>


    The first code checks to see if the current month is different than the one in the current workbook. The second code disables the automatic refresh in all the queries then saves the workbook under the month name and year.

    Don't know if this will help, but you might could modify it to work with your data.

  11. #11
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    Mike

    Thank You. My problem is solved. From the replies I got from yourself and Geoff Whitfield I now have 3 different solutions to choose from.

    Frits

  12. #12
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    Frits,

    With respect, would be good to know which solution worked.

    It's not important to me to know if it was mine or anyone else's.

    One of the great uses of this forum is that it provides a bank of solutions for a lot of people who come in later.

    You have 3 solutions to choose from. It would be really good, if you could give some feedback, when you find your solution, to give some feedback as to which solution suited, and why.

    It doesn't matter to me if my post solved your problem. But it may well matter to a lot of people who come in behind me looking for a sloution.

    So your feedback would be welcome to many!
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  13. #13
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    Yes, you CAN change it during runtime -- we do it here quite often! What makes you think it cannot be done?

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet CodeName Property (Excel 97)

    The following from the help file might make someone think that it could not be changed.

    [pre]
    CodeName Property

    Returns the code name for the object. <font color=red>Read-only String.</font color=red>

    Remarks

    The code name for an object can be used in place of an expression that returns the object. For example, if the code name for worksheet one is "Sheet1", the following expressions are identical:

    Worksheets(1).Range("a1")
    Sheet1.Range("a1")

    It's possible for the sheet name to be different from the code name. When you create a sheet, the sheet name and code name are the same, but changing the sheet name doesn'tchange the code name, and changing the code name (using the Properties window in the Visual Basic Editor) doesn't change the sheet name
    Legare Coleman

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles, California, USA
    Posts
    1,734
    Thanks
    0
    Thanked 3 Times in 1 Post

    Re: Worksheet CodeName Property (Excel 97)

    Please don't 'tease'! If you know the answer why not share it?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Eileen_sig.gif>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •