Results 1 to 17 of 17

Thread: [RESOLVED] returning the worksheet code name for a sheet & a simple RANGE question

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Resolved [RESOLVED] returning the worksheet code name for a sheet & a simple RANGE question

    Hello forum members,

    I have a listbox that contains a list of worksheets held on a workbook. The user will select a single value from the listbox and I will then copy the contents of that worksheet to another worksheet.

    I know that I should not refer to worksheets using the name as that can change and then the program will fail. I should instead use the worksheet code name. How do I return the code name for the selected worksheet? I did think about adding it into the listbox and then just picking it up from the chosen value. Is there a better way to achieve the same?

    One other things that bothers me is that when I know a range that I want to access, I have to specify the sheet as well (sheet1.range("BeMoreDog"). Is there a way to just refer to the range as it should be unique within the workbook.

    Making great progress in learning VBA now thanks to help from this forum over the past year. Many thanks.

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    What do you mean by worksheet "code name?"

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    I mean the sheet number that Excel automatically assigned when a sheet is created. That wont change if a user changes the order or the name of the sheet within Excel.

    I can see the sheet number when I work in VBA editor (top left of the editor)

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    gotcha. what do you mean by "how do i return the code name?" also, for you "range" question, how are you defining the range?

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    If I have 3 worksheets in a workbook (Bob, Jon, Harry) and in VBA Editor I can the following;

    Sheet1(Bob)
    Sheet2(Jon)
    Sheet3(Harry)

    A user selects 'Jon' from the listbox and then I would like to reference that sheet using some other method than the sheet name. So I would like to use something like 'workbook.sheet2' but I won't know the sheet code name. Does it make sense?

    For the range question, I just have a number of ranges on a worksheet. For example,
    Sheet1 Range = "Car"
    Sheet1 Range = "Boat"
    Sheet2 Range = "Birds"

    WHen I want to reference the range "Boat" then ideally I'd like to just write range("Boat").select (or something similar) and not have to write sheet1.range("Boat).select.

    Hope it helps to explain.

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    If you populate your listbox with Bob, Jon and Harry, then I'm assuming you need to change the contents of the listbox if the names of any of the sheets change, right? Why not just read the names of the sheets and populate your listbox? How are you populating it?

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    yep, I'd need to rename the content of the listbox. And I would refresh the listbox content on OPEN and whenever the worksheet changed. But it's the part after that I'm struggling with. How do I refer to the cells on the selected worksheet without using the worksheet name (Bob, Jon, Harry)?

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    depends on what you need to do with those ranges. are you going to copy data from them to somewhere else? use the values in the cells for calcs?

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    sometimes I will copy from them to another sheet.

    Other times I might just want to read the range content.

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    Once you've defined a range object, you can do those things without qualifying it each time, ie:

    Code:
    Sub macRanges()
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim rng1 As Range
        Dim rng2 As Range
        
        Set ws1 = ActiveWorkbook.Worksheets(1)
        Set ws2 = ActiveWorkbook.Worksheets(2)
        Set rng1 = ws1.Range("a1")
        Set rng2 = ws2.Range("b2:b5")
        
        MsgBox rng1.Value
        
        rng2.Copy
        
    End Sub

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    Thanks.

    So I'm better off storing the sheet name and corresponding 'code name' in the listbox because then I know how to reference the sheet which the value selects from the listbox?

  12. #12
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    I'm using the worksheet name in this example:

    Code:
    Private Sub setObject_Click()   'button named "setObject"
        Set wb = ActiveWorkbook
        Dim wsDynamic As Worksheet
    
        If Sheet1.lb1.Value <> -1 Then
            Set wsDynamic = Worksheets(Sheet1.lb1.Value)    'name selected in listbox
        End If
        
        wsDynamic.Activate  'shows wsDynamic successfully set
    End Sub
    I update the listbox when the workbook opens, then again whenever a worksheet name changes.

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    you can not use a string as a way to return the sheet object by codename, best you can do is use a select case or similar, but it is much better to work with sheet names
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    hi there. sorry to replied delay but no chance to get online.

    Thanks for the code sample. As I read and understand the code, the issue I still have is shown in your code on the following code;
    Set wsDynamic = Worksheets(Sheet1.lb1.Value)

    How can I avoid specify 'sheet1' everytime. The listbox will be populated with different worksheets and it will that not every worksheet is on 'sheet1'. And it's the 'sheetx' that I need to find out.

    So for example, if I have 4 worksheets (bob, dick, harry, tom) and these appear in the listbox. When I select 'bob' from the list then this is sheet3. And 'dick' is sheet4. Harry is sheet13 and Tom is sheet25. How do I update your code sample so that the correct sheet replaces your 'sheet1')?

    Thanks

  15. #15
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    Can you zip and attach a workbook that has the conditions you're describing (both name and "codename")?

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    How can I avoid specify 'sheet1' everytime.
    according to the code the listbox is on sheet1, when you are working on a different sheet, you must fully address the listbox, this can not be avoided unless you work with the shapes /ole objects on a named worksheet
    OR
    you have a global sheet variable, that is set in the click event of the listbox
    Code:
    public wsDynamic as worksheet  ' in standard module
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: returning the worksheet code name for a sheet & a simple RANGE question

    Thanks for the feedback. I now understand what the code is doing so many thanks for that, vbfbryce. But it still leaves me working with the worksheet name and that can always be changed by a user. So I will still use this approach of loading the worksheet name into the listbox but I will use worksheet.codename to return the internal codename of the selected sheet. Then I am fine.

    Thanks again to you both for your time on this. I learned something new that will be very useful as I continue to develop Excel based solutions.

Posting Permissions

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



Click Here to Expand Forum to Full Width