Trying to understand GetCustomListContents

[Nick Vittum]

As practice exercise (and one that will be practical if it can work), I'm playing with 12 worksheets named for the months and a few others named otherwise. There are things that happen only on the 12 month sheets, and other things that do not happen on those sheets. Until now, for those operations, I've listed all the 12 month sheets, or else the other sheets, depending on what seemed most practical in the individual case. But what if I could have one line of code that could apply in each situation?

As a starting point, I wrote

Code: Select all

Private Sub Workbook_SheetActivate(ByVal Sh As Object)    Dim Lm As Long
    Lm = Application.GetCustomListContents(3)
    
    If ActiveSheet.Name <> Lm Then MsgBox "hi"
    (other code)
End sub

But of course it can't work as is, because what I really need to be saying is "If the ActiveSheet.Name is a subset of Lm," and I don't know if it's even possible to say such a thing, and I certainly don't know how to say it.

Backing away from the specific example to focus on the general concept: Is it possible to use CustomLists in this way? Or can they only be used where you want to use the entire contents of the list?

I'm signing off now. I just wanted to post this now to be seen in the morning. Thanks

[snb]

1. in VBA you don't have to use Worksheet_Activate, since you can refer to every sheet directly, using its indexnumber, name or codename: sheet(7), sheets("July") or sheet7
2. in VBA checking in the worksheet_Activate event whether the name of the sheet is part of a customlist seems to be redundant.
3. if you are checking the name of the sheet in the worksheet_Activate event, you should us the argument sh ot het event, not the activesheet.name. In your example code you should have used sh.name
4. if you aks this to understand the technique:

4a. a customlist is a 1-dimensional array
4b. a sheetname is a string
4c. there are several/many techniques to check whether a string is part of a 1-dimensional array.
to mention only a few:

5.1

Code: Select all

msgbox ubound(filter(application.getcustomlistcontents(3),activesheet.name))>-1

5.2

Code: Select all

msgbox instr(activesheet.name,join(application.getcustomlistcontents(3),"")

5.3

Code: Select all

msgbox not iserror(application.match(actviesheet.name,application.getcustomlistcontents(3),0))

5.4

Code: Select all

for j=1 to 12
        if activesheet.name=application.getcustomlistcontent(3)(j) then exit for
     next
     msgbox j<13

6. If this code is part of the Worksheet_activate event the code shoud read as follows:

6.1

Code: Select all

msgbox ubound(filter(application.getcustomlistcontents(3),sh.name))>-1

6.2

Code: Select all

msgbox instr(sh.name,join(application.getcustomlistcontents(3),"")

6.3

Code: Select all

msgbox not iserror(application.match(sh.name,application.getcustomlistcontents(3),0))

6.4

Code: Select all

for j=1 to 12
        if sh.name=application.getcustomlistcontent(3)(j) then exit for
     next
     msgbox j<13

[HansV]

GetCustomListContents returns an array, not a Long.

Code: Select all

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim Lm As Variant
    Dim i As Long
    Dim f As Boolean
    Lm = Application.GetCustomListContents(3)
    For i = LBound(Lm) To UBound(Lm)
        If Sh.Name = Lm(i) Then
            f = True
            Exit For
        End If
    Next i
    If f Then
        MsgBox "Active sheet name is in Jan - Dec"
    Else
        MsgBox "Active sheet name is not in Jan - Dec"
    End If
End Sub

It might fail if the user's Excel is in another language.

[Doc.AElstein]

Hi Nick, ( hope all is OK with the daughter.. )
Just adding a quick link for completeness: http://www.eileenslounge.com/viewtopic.php?f=27&t=34206" onclick="window.open(this.href);return false; , http://www.eileenslounge.com/viewtopic. ... 06#p265299" onclick="window.open(this.href);return false;
_.. some of this we touched on before, at least the bit about the GetCustomListContents(3) being a 1 dimensional array of the 12 months, and how to use it in a loop and similar to do things of the sort you are talking about in the first post here.. like "If the ActiveSheet.Name is a subset of… etc…

Same again, in very simple form ( It looks simpler/ prettier in the code module since my long 'comments are hidden to the right )

Code: Select all

Sub OneDimensionalArrayGetCustomListContents_3()
Dim arr() As Variant       '              A Variant is needed or the next line will give a type mismatch error, as the .GetCustomListContents( ) method returns its stuff in  Elements  of Variant type,  even though they are String values -  They are text strings "housed" in Varinat Type Elements    We have to use a dynamic array , that is to say we have not given it a size, because it gets sized by the next line.  It would not work either if you sized the array to ( 1 to 12 ) because it is just the way the next line has to work -   .GetCustomListContents( ) method  chucks back a field of values expecting to see a dynamic array to put it in and at the same time size as required.  
 Let arr() = Application.GetCustomListContents(3)
 
Dim Cnt
    For Cnt = 1 To 12
     MsgBox prompt:=arr(Cnt)
    Next Cnt
End Sub

Ref:
https://docs.microsoft.com/en-us/office ... stcontents" onclick="window.open(this.href);return false;
https://www.snb-vba.eu/VBA_Excel_customlist_en.html" onclick="window.open(this.href);return false;


To see the picture below , you step through your coding in the VB editor, then when you are past the bit where the array is filled , you highlight/ select arr , and hit keys Shift+F9 , then select the button to add the "watch" to that variable in the Watch Window : https://imgur.com/PlUxVTM" onclick="window.open(this.href);return false;
( To step through the macro, you click anywhere in the macro , then hit the F8 key - each hit steps through a line )

[Nick Vittum]

Wow, thanks, everyone. A lot of interesting stuff here I didn't get a chance to look at before, because Eileen had gone into hiding before I got on-line the next morning. I look forward to studying these responses soon. Thanks again.

[Nick Vittum]

Thanks again to all three of you. I just got back to this this evening. I ended up using this line—

Code: Select all

If not iserror(application.match(activesheet.name,application.getcustomlistcontents(3),0)) Then

—just because it was simple and I understood it the best. But I'll be spending more time looking at all of them. And hopefully understanding them...

[snb]

Please stick to VBA exclusively:

Code: Select all

msgbox ubound(filter(application.getcustomlistcontents(3),activesheet.name))>-1

[Nick Vittum]

Wait, what?? The code I used came from you. It looked like VBA to me. . . (And I understood it. I don't understand "UBound." Not at all. And for that matter, what is that ">-1" doing there? What does that mean, in this context?)