Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

limit array to specific sheets only

Posted on 2013-12-28
13
Medium Priority
?
215 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
Industry Leaders: 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

609 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