Solved

limit array to specific sheets only

Posted on 2013-12-28
13
207 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Forgive you ???? I am pleased when you jump in its an honor !!! keep up the good work your the best !! :)
gowflow
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:rutgermons
Comment Utility
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
Comment Utility
do you have more than these values ? Europe asia Americas ?
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 250 total points
Comment Utility
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 80

Assisted Solution

by:byundt
byundt earned 250 total points
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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
Comment Utility
thanks! to be fair I must award the credits to both!both solutions are doable and work for me

thanks guys!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

743 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

13 Experts available now in Live!

Get 1:1 Help Now