Solved

limit array to specific sheets only

Posted on 2013-12-28
13
212 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 30

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 30

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 30

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 30

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 30

Expert Comment

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

Accepted Solution

by:
gowflow earned 250 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 250 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 30

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

690 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