Using a Defined Name (Dynamic List) in Code

[vikrampnz]

Hello

I have a piece of code which works well for its intended purpose and has part where certain worksheet names are specified which are to be excluded for the list that is made by using that code. (See the sample below)

Dim xSheet As Worksheet
Dim xRow As Integer
Dim calcState As Long
Dim scrUpdateState As Long

For Each xSheet In Application.Worksheets
If IsError(Application.Match(xSheet.Name, _
Array(Me.Name, "Reports", "Trial List", "Original Template"), 0)) Then
xRow = xRow + 1

In the above code, "Reports", "Trial List", "Original Template" are the three worksheets that will be excluded from the displayed list. I have created a table ("Table2") and am adding the names of worksheets to be excluded in that table in the column named "Ignore_L", as its much easier for me to add 3-4 more names in the table than to add in those sheetnames in the code directly every time. Purpose of inserting table was that the range automatically extends everytime a new name is added to the list. I have defined a name for this column in the table as "Ignore_List".

I have tried referencing the Defined Name "Ignore_List" in my code with no luck. It either gives an error or doesn't work.

Would appreciate any help from the group !

Cheers

 

[Peter_SSs]

If you have made a formal table (which it sounds like you have) then you don't also need a named range. You should be able to use something like this (checking the table name of course)

If IsError(Application.Match(xSheet.Name, Range("Table1[Ignore_List]"), 0)) Then
  xRow = xRow + 1
End If

BTW, if you want to format part of your posted code yourself, you need to use the 'rich' code tags, not 'vba' code tags.

 

[vikrampnz]

Hi Peter_SSs
Thanks for your response.

Unfortunately, I tried to reference it the way you suggested but its clearing the entire list and not just the worksheets listed in the Ignore_List.

Not sure whats going wrong !

 

[Peter_SSs]

its clearing the entire list and not just the worksheets listed in the Ignore_List

There is nothing in the code you posted about clearing anything so there is no way for me to see if I can replicate what you are describing.

Perhaps you could post your actual code?

.. and a small sample of two worksheets (one on the ignore list and one not) using XL2BB so that we can easily copy to test with?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[vikrampnz]

Hello...I am using Excel 2007....have updated the same in my profile ......I have a sample sheet....but I am struggling to see where I can upload it from....please guide on how to upload the sample sheet...thanks

 

[Peter_SSs]

please guide on how to upload the sample sheet...thanks

See the XL2BB link in my previous post.
If you want to share an actual workbook, you need to upload to DropBox/OneDrive/GoogleDrive etc and provide a shared link here.

 

[vikrampnz]

Test - Index.xlsm

Heres the link....thanks

 

[Peter_SSs]

Heres the link

Thanks, but as far as I can see that has your original method ..

If IsError(Application.Match(xSheet.Name, _
Array(Me.Name, "Reports", "Trial List", "Original Template"), 0)) Then

You said you had tried my method, but I cannot see that attempt. What was your actual replacement code for the above line that you tried?


Thanks for updating your profile.

 

[vikrampnz]

Test - Index - New Code.xlsm

Heres the link for the workbook with the change in code you suggested. Its giving me the error. Really appreciate your help in this. Thanks

 

[Peter_SSs]

Heres the link for the workbook with the change in code you suggested.

Thanks.
What you have (just combined onto a single code line) is

If IsError(Application.Match(xSheet.Name, Array(Me.Name, Range("Ignore_List[IGNORE]")), 0)) Then

There are a couple of issues.

1. There is no Array() in what I suggested.
   
   
2. You hadn't told us that this was part of a worksheet 'event' code (Worksheet_Activate) in the 'Summary' worksheet module and that the table you had created was on a different worksheet.
   The problem here is that in your code Range("Ignore_List[IGNORE]") will be looking on the Summary sheet for that table/range and naturally cannot find it.

If you do not want that part of your code to act on the Summary sheet, then add that name to the Ignore_List table.
Then modify my code only to point to the sheet where the Ignore_List table is

If IsError(Application.Match(xSheet.Name, Sheets("DataMaster").Range("Ignore_List[IGNORE]"), 0)) Then