Solved

limit array to specific sheets only

Posted on 2013-12-28
13
208 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
  • 7
  • 2
  • 2
  • +1
13 Comments
 
LVL 29

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 29

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

Expert Comment

by:teylyn
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
 
LVL 29

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

Expert Comment

by:teylyn
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 29

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 29

Expert Comment

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

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 29

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

948 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now