Reference Worksheet Codename using a string variable?

    Right so in a nutshell I've got a load of active X command buttons which are used to:


    1. Copy a VBA Generated SQL from a Cell (each with a named range with a common prefix followed by a number)
    2. Activate the sheet which the user should paste the results of the Copied SQL to


    The process they all go through is fairly common so the only thing I have each of the actual button controls doing is setting a suffix number from 1 to 10 which then passes this to a common bit of code:



    So the problem is at the end, part where I'm trying to activate the relevant SQL Output Sheet. The codenames of these sheets are all SQL1-10.


    I appreciate the above doesn't work because the codename isn't a string so I can't use this to reference the sheet in the same way you could use a string to reference a named range ( i.e. .range(StringVariable) can be used to replace .range("Value of StringVariable") ).


    But is there anyway I can get it to recognise a variable as a worksheet codename.


    Obviously one solution could be to use the worksheet name rather than the codename and then the above, in theory (if sSheetCodeName was replaced with Sheets(sSheetCodeName) ), would work fine if sSheetCodeName had the same value as the worksheet name. But I can't really do it that way as the worksheets are not generically named for the purposes of the user.


    Any ideas folks?

    Re: Reference Worksheet Codename using a string variable?


    You can create a function that loops through the sheets checking their code name against a specified parameter and then returns the sheet as an object. If you have trusted access to the VBProject (and your users do too) then you can access it more directly using the VBProject object.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Reference Worksheet Codename using a string variable?


    Given a sheet's code name, as a string, a function like this will return the worksheet object.

    Code
    Function SheetFromCodeName(aName As String, Optional wb As Workbook) As Worksheet
    If wb = Nothing Then Set wb = ThisWorkbook
    With wb
        Set SheetFromCodeName = .Sheets(.VBProject.VBComponents(aName).Properties("Index"))
    End With
End Function

    Re: Reference Worksheet Codename using a string variable?


    Mike:

    Code
    If wb = Nothing


    should be:

    Code
    If wb Is Nothing

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Reference Worksheet Codename using a string variable?


    Quote from mikerickson;715284

    Given a sheet's code name, as a string, a function like this will return the worksheet object.

    Code
    Function SheetFromCodeName(aName As String, Optional wb As Workbook) As Worksheet
    If wb = Nothing Then Set wb = ThisWorkbook
    With wb
        Set SheetFromCodeName = .Sheets(.VBProject.VBComponents(aName).Properties("Index"))
    End With
End Function


    Hi, this is the first time I use this forum and I'm not sure I'm highjacking the thread (and it's pretty old), but this was EXACTLY what I was looking for and it's just not clear to me why use the Index property to get the sheet, since then you lose the advantages of the CodeName property ? I tried changing "Index" to "_CodeName" and it obviously didn't work, but I don't see why.


    Please let me know if this is still highjacking... I'll create another post even though it's the same subject :)


    Many Thanks!!

    Re: Reference Worksheet Codename using a string variable?


    Woah! This must have been one of my first posts here, lol.


    Can't even remember why I was doing this let alone why we used the Index Property.


    I think I was developing an add-in though so it had something to do with not knowing the real codenames in the sheets that were referencing the add-in.


    I could be talking utter bollocks though, so don't quote me.

    Re: Reference Worksheet Codename using a string variable?


    You pass the code name to the function, so you still have the advantage of the code name. The Index is simply used to get a reference to the right sheet from the VBComponent, which is identified using the code name. I don't really understand what your issue is?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Reference Worksheet Codename using a string variable?


    Quote from rory;800124

    You pass the code name to the function, so you still have the advantage of the code name. The Index is simply used to get a reference to the right sheet from the VBComponent, which is identified using the code name. I don't really understand what your issue is?


    Thanks to both of you for the quick answers !
    Well I guess talking about my own issue would officially be highjacking the post, but personally I have a spreadsheet with two different types of worksheets, which I have renamed (codenames) to S0, S1... And C0, C1,.. . It will be used by completely unexperienced Excel users so I need to make sure it doesn't break with them doing crazy inexplicable sheet name changing, swiping their positions for no reason and so on..


    So when calling my macros, I want to make sure it will look for the right sheet (I want to get rid of anything of the likes of ActiveSheet).


    Calling this function would therefore be more of a way of creating something stable to call the activesheet and make sure this sheet corresponds to the "type" that it should be.


    .. keeping in mind that the document is pretty much a database and analysis tool for the data inside and there's a lot of information that goes around through sheets, sheets are constantly created, modified etc.


    Perhaps a simple use of a variable as in

    Code
    Set sh = activesheet


    Would be enough to ensure this, but yeah, I was hoping to be able to make this an unbreakable document and therefore was wishing to really only use codenames..


    Sorry if this was too much or not enough info, this thing has become a monster to me, I'm completely drowning in it 😂😂😂


    Thanks !

    Re: Reference Worksheet Codename using a string variable?


    If the code is in the same workbook as the sheets, you don't need this function at all. Just use the code names. If it isn't, then you pass the codename as a string to the function and it returns a worksheet object reference, so I don't understand what your issue with it is.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Reference Worksheet Codename using a string variable?


    I just think I got what you meant by keeping the advantage of the codename, you're saying that on the case of someone messing up with the index, you would still find the correct sheet ?

    Re: Reference Worksheet Codename using a string variable?


    Yes. The code finds the index of the sheet with the desired codename, so it must be correct.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Reference Worksheet Codename using a string variable?


    Quote from rory;800154

    If the code is in the same workbook as the sheets, you don't need this function at all. Just use the code names. If it isn't, then you pass the codename as a string to the function and it returns a worksheet object reference, so I don't understand what your issue with it is.


    Well, the macro doesn't know the codename of the sheet, I have to get it when the macro is called, to then apply the changes to the sheet of that codename.
    (The macro can be called by a series of different sheets of the same "type").
    Should I just use a variable through ActiveSheet and be done with it ?

    Re: Reference Worksheet Codename using a string variable?


    I don't know what you mean. What does "I have to get it when the macro is called" mean? If you aren't dealing with fixed specific sheets, the code name is probably not your best option.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Reference Worksheet Codename using a string variable?


    Quote from rory;800199

    I don't know what you mean. What does "I have to get it when the macro is called" mean? If you aren't dealing with fixed specific sheets, the code name is probably not your best option.


    I see. I meant that when the macro is called, it could be called from various sheets that look the same, but have different data inside. Meaning that I have to identify which precise sheet it is at the beginning of the macro, so I can apply changes onto the workbook, referring to the data inside this sheet.


    My best option here is then to set a variable to be the ActiveSheet and that's it.


    I don't know very well how excel reacts to code, so I don't know what to expect as trouble from my macros. I think I'll be making a thread on that !


    Thanks

    Re: Reference Worksheet Codename using a string variable?


    Quote from bettvett;800155

    I just think I got what you meant by keeping the advantage of the codename, you're saying that on the case of someone messing up with the index, you would still find the correct sheet ?


    Let's break down the function

    Code
    With wb 
        Set SheetFromCodeName = .Sheets(.VBProject.VBComponents(aName).Properties("Index")) 
    End With


    VBProject.VBComponents(aName) is the VB component that has the codename that was passed to the function.


    .VBProject.VBComponents(aName).Properties("Index") is the current index of that component (sheet)


    .Sheets(.VBProject.VBComponents(aName).Properties("Index")) is the worksheet object that currently has that index.


    So, when the function is run, it returns a worksheet object that has the given code name.


    If the user re-orders the sheets between you running the function and using the results, it doesn't matter. The Worksheet object is still the same. the user changing the index hasn't changed the object, its only changed its location. (Your car is your car, no matter where you park it)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login