Solved

Combine two Worksheet_SelectionChange(ByVal Target As Excel.Range)

Posted on 2015-02-12
3
98 Views
Last Modified: 2016-02-11
Hi Experts

I have two Excel Private Sub Worksheet_SelectionChange that I'm trying to combine with no success.  Each work separately but I need to combine them.

The first is this:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Set Target = Range("name")
    If Target = "" Then Exit Sub
    ActiveSheet.Name = Left(Target, 31)
    Exit Sub
End Sub

The second is this:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("C6:C14")
   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        overwrite
       
    End If
End Sub

The macro called overwrite is this:
Sub overwrite()
ActiveCell.Offset(-1, 1).Value = ActiveCell.Offset(-1, 1).Value
End Sub

So in trying to combine them, I tried this which doesn't work:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Set Target = Range("name")
    If Target = "" Then Exit Sub
    ActiveSheet.Name = Left(Target, 31)
    Exit Sub

    Dim KeyCells As Range
    Set KeyCells = Range("C6:C14")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        overwrite
    End If
End Sub

What am I doing wrong?

Cheers,

Will
0
Comment
Question by:willnjen
  • 2
3 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 40607113
Try this...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
     If Range("name") <> "" Then
        ActiveSheet.Name = Left(Range("name").Value, 31)
     End If
     If Not Application.Intersect(Target, Range("C6:C14")) Is Nothing Then
         overwrite
     End If
End Sub

Open in new window

0
 

Author Comment

by:willnjen
ID: 40607115
Thanks Wayne

I'll try that when I get back to my computer.

Cheers,

Will
0
 

Author Comment

by:willnjen
ID: 40611175
Hi Wayne

Thanks for your answer.  It works perfectly and the points are yours.

Cheers,

Will
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

685 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