?
Solved

limit array to specific sheets only

Posted on 2013-12-28
13
Medium Priority
?
213 Views
Last Modified: 2013-12-29
Folks

How can I limit my array to contain and modify specific sheet names only based on the value specific in a field on sheet one


thed below works but does not limit to only the sheet names provided in the array:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
    On Error Resume Next
    Worksheets("chosen continent").Name = Array(, , "europe", "americas", "asia")(Worksheets("chosen continent").Index)
    Worksheets(Range("B1").Value).Name = "chosen continent"
    On Error GoTo 0
End If
End Sub

all help will do
0
Comment
Question by:rutgermons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +1
13 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39743368
How can I limit my array to contain and modify specific sheet names only based on the value specific in a field on sheet one

...
Can you be more specific ?
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39743373
By specific, I mean it is not clear what you need to achieve. Can you please explain it in plain English? like I have so and so worksheet names and I need to ....

something in these lines.
gowflow
0
 
LVL 50
ID: 39743377
Hello,

can you explain in simple words what your code needs to do? If the above code works, what functionality exactly would you want to add? If you can put it into words and describe the logic, the code can be developed.

cheers, teylyn
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:gowflow
ID: 39743379
Hi Teylyn
good to see you around and merry Christmas to you and your family. btw isn't it what I just requested from asker ?
gowflow
0
 
LVL 50
ID: 39743385
Hi gowflow, merry XXXs to you, too. And yes, it seems like I duplicated your comment. Reason is, I started to write code, then biffed it and just wrote a comment instead. In the meantime you had posted, but I was not aware of that. I should have refreshed my browser. Can you forgive me?

May 2014 be a good year for you!

cheers, teylyn
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39743388
Forgive you ???? I am pleased when you jump in its an honor !!! keep up the good work your the best !! :)
gowflow
0
 

Author Comment

by:rutgermons
ID: 39743547
i have a workbook with 10 sheets, on sheet 1 i have a field (b1)

if the field value is "europe"
then I would like sheet2 to be renamed as "chosen continent"

if the field value is "asia"
then I would like sheet3 to be renamed as "chosen continent"

if the field value is "americas"
then I would like sheet4 to be renamed as "chosen continent"

i dont want the other sheets i.e. sheets 5-10 to be affected , the array code I specified impacts all sheets which I dont want renamed

hope this clarifies
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39743770
do you have more than these values ? Europe asia Americas ?
gowflow
0
 
LVL 31

Accepted Solution

by:
gowflow earned 1000 total points
ID: 39743825
here it is try this sample workbook and place any value in sheet1 B1 and see the results.

for your easy convenience here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ray As Variant
Dim I As Long
Dim Foundit As Boolean

Foundit = False
Ray = Array("", "", "europe", "asia", "americas")
If Not Intersect(Target, Range("B1")) Is Nothing Then
    
    For I = 0 To UBound(Ray)
        If Ray(I) = Range("B1").Value Then
            Foundit = True
            Exit For
        End If
    Next I
    
    If Foundit Then
        On Error Resume Next
        Worksheets("chosen continent").Name = Ray(Worksheets("chosen continent").Index)
        Worksheets(Ray(I)).Name = "chosen continent"
        On Error GoTo 0
    End If
End If
End Sub

Open in new window


Regards
gowflow
Array-Continents.xls
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 1000 total points
ID: 39744001
The code posted in your question required that worksheets europe, americas, and asia be located in tab index positions 2, 3 & 4. If the user might scramble the order or insert other sheets, then a different approach is required.

The following code uses the Application.Undo method to get the previous value of cell B1. It uses that value to find the old name for worksheet "chosen continent".
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sOldName As String, sNewName As String
With Range("B1")
    If Not Intersect(Target, .Cells(1, 1)) Is Nothing Then
        sNewName = .Value
        Application.EnableEvents = False
        Application.Undo
        sOldName = .Value
        .Value = sNewName
        Application.EnableEvents = True
        
        If sOldName <> sNewName Then
            On Error Resume Next
            Worksheets("chosen continent").Name = sOldName
            Worksheets(sNewName).Name = "chosen continent"
            On Error GoTo 0
        End If
    End If
End With
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 39744005
The attached workbook has been used for several purposes, but it includes the suggested code in Sheet1 code pane, and will change the names of worksheets europe, americas and asia with "chosen continent" as requested--no matter what their order and no matter where they are located in the tab order.
MultipleLookupsQ28323513.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39744519
Well we always need to keep up with improvement ! Although this was not requested in the initial post but the comment of byundt is logical and raised a valid point where sheets could be messed up and not in the same order.

This version takes also care of sheets being totally messed up and even after other sheets like in this sample workbook.
gowflow
Array-Continents-V01.xls
0
 

Author Comment

by:rutgermons
ID: 39744821
thanks! to be fair I must award the credits to both!both solutions are doable and work for me

thanks guys!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question