Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rename sheet via code. (97 SR2)

    I've got a procedure which deletes a sheet, and pulls in another with the same name. The problem is, the copy procedure renames the name of my sheet from (sheet) to (sheet1). Is there an easy way to either keep the original name, or change the name of the sheet? I'm not talking about the caption of the sheet, but the actual name as VBA interprets it...

    <img src=/S/help.gif border=0 alt=help width=23 height=15>

    thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Rename sheet via code. (97 SR2)

    If oSheet is a variable DIMed as WorkSheet that currently contains the sheet, then:

    <pre> oSheet.CodeName = "Sheet1"
    </pre>


    should change the name to Sheet1.
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename sheet via code. (97 SR2)

    You rock Legare, Thanks!!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename sheet via code. (97 SR2)

    Err... now i'm getting a funky error...

    In theory, this should work, right?:

    if workbooks(wbname).sheets("main").CodeName = "main1" then sheets("Main").Codename = "Main"

    It's giving me the following error:

    Run-Time Erorr '450':
    Wrong number of arguments or invalid property assignment.

    <img src=/S/help.gif border=0 alt=help width=23 height=15>,... Again..

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rename sheet via code. (97 SR2)

    As you are discovering, changing the CodeName is very dangerous. You should probable think of a different scheme. <A target="_blank" HREF=http://www.cpearson.com/excel/codemods.htm>Here</A> is some more info on CodeName. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename sheet via code. (97 SR2)

    It's excel that's changing my sheet name, I want to change it back...

    ThisWorkbook.VBProject.VBComponents("Sheet2").Prop erties("_CodeName").Value = "NewCodeName" This convention in the link you posted worked like a charm.

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename sheet via code. (97 SR2)

    But, it only works if the code is stopped... Any ideas how I might get around this?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rename sheet via code. (97 SR2)

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15> I wanted to suggest that, but didn't want to test it! Still dangerous tho <img src=/S/alien.gif border=0 alt=alien width=14 height=15> --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Rename sheet via code. (97 SR2)

    Drk,

    << In theory, this should work, right?: >>

    Well actually wrong. In theory you cannot change the code name via VBA code. (You can of course change the sheet name, the name that appears on the sheet Tab). However in practice you can access th eVBProject properties and change them<pre> ThisWorkbook.VBProject.VBComponents("Sheet").Prope rties _
    ("_CodeName").Value = "Sheet1"
    </pre>

    However depending on what you are doing and how you are referencing your work sheets, perhaps the following is sufficient. It changes the sheetname as shown on the sheet tab, which is a different property than the codename.<pre> ThisWorkbook.Sheets("Sheet").Name = "Sheet1"</pre>

    Try the above in your code and see if it works.

    Andrew

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename sheet via code. (97 SR2)

    I suppose I can change all the references in my project to point to the sheetname instead, how might I do that?

    Why can't Excel just leave the codenames alone? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    thanks for your help!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Rename sheet via code. (97 SR2)

    << Why can't Excel just leave the codenames alone >>

    Well the codename is component property, and if you make a copy of a sheet, Excel has to change the codename to avoid conflicts.

    ThisworkBook.Sheets("SheetName") is a reference to a sheet name as shown on the sheet tab, regardless of the codename. What method are you using at the moment to reference a worksheet in your code ?

    Andrew

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename sheet via code. (97 SR2)

    Sheets("Name") is the convention i'm using currently, however when the codename of my sheet changes at copy, my code can't seem to find the object...
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Rename sheet via code. (97 SR2)

    Drk,

    Where are you copying the sheet from. Is it the same workbook ?

    When you copy a sheet to the same workbook, both the sheetname and code should change.

    Andrew

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename sheet via code. (97 SR2)

    No, It's being copied from a different workbook.

    Here's a bit of history on this process.

    I've built a CRM in Excel, it's got quite a lot of features, but given that it is an Excel database, it is very prone to failure, (the fact that we're using Win98 isn't helping much either.) Very early in the project, the fact that a system crash could result in complete data loss prompted me to come up with a solution. That solution was to take the main page between every call, and export it to it's own spreadsheet. (This also allows for after the call reporting...)

    So, i've worked out a great export process. The file is exported to the network, and retains the same name as the original, (as it's going to a new workbook). Now i'm running into all kinds of problems when trying to get it back in.

    Even if I delete the original "Main" sheet, the program insists on renaming the sheet to "Main1". this nullifies all the references in my code, rendering the database useless.

    Do you think that because this is coming from a different spreadsheet, I'm compromising things?

    Now, when I run the code:
    Workbooks(wbRecovery).Sheets("Main").Copy Before:=Workbooks(wbName).Sheets("System")

    Excel Completely Crashes, due to Invalid Page Fault.. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  15. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rename sheet via code. (97 SR2)

    Before you do the CopyBefore, find out the index of the sheet, then use this index to rename the copied sheet. Put this macro into a blank workbook and run it several times. Hopefully, you can go from there. --Sam
    <pre>Sub Macro1()
    Dim i As Integer
    i = Sheets("Sheet3").Index
    MsgBox i
    Sheets("Sheet1").Copy Before:=Sheets("Sheet3")
    MsgBox Sheets(i).Name
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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
  •