Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel VBA (Excel 2007)

    In VBA I can change the name of a tabbed worksheet by "Sheets("ABC").Name = "ANewName"
    However if this is Sheet1(as seen in the listing in the VBE it would show as "Sheet1 (ABC)"
    How do I change the Sheet1 to ABC? Then it would show in the VBE as "ABC (ABC)"

    John

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel VBA (Excel 2007)

    Use the .Codename Property. See <post#=52,864>post 52,864</post#>; other discussion later in the thread may be useful.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel VBA (Excel 2007)

    Chip Pearson's site should be bookmarked by any Excel inmate.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel 2007)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Thanks John,

    I also found this about 10 minutes after posting.

    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent


    Then,
    ' I change the sheet name ...
    Sheets("DeliverySlip (2)").Name = "DeliverySlip" & strDest(X)

    ' Then I change the component name.
    Set VBComp = ActiveWorkbook.VBProject.VBComponents("DeliverySli p1")
    VBComp.Name = "DeliverySlip" & strDest(X)

    I found this link very helpful

    http://www.cpearson.com/excel/vbe.aspx

    Thanks again John,
    John

Posting Permissions

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