+ Reply to Thread
Results 1 to 3 of 3

Adding named range to Select Case

  1. #1
    Registered User
    Join Date
    11-17-2015
    Location
    Greater Toronto area, Ontario
    MS-Off Ver
    2013
    Posts
    5

    Adding named range to Select Case

    I created a multi-worksheet workbook that has multiple instances of bi-directional (and in some cases tri-) cell updating. For instance, If I change B1 on worksheet 1, B2 on worksheet 2 changes, and if I change B2 on worksheet 2, then B1 on worksheet 1 changes. The issue is I have created this massive spreadsheet and have no flexibility. I need to add/delete rows without affecting the integrity of the code. It was suggested I use named ranges. So, great, sounds good. I then changed the relevant cell references to named ranges ("cost", "revenue", etc.) and inserted in place of B1, B2, etc. Doesn't work. The following is some of the code from one of the sheets (before I changed to named ranges, and it still worked).

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Select Case Target.Address(0, 0)
    Case "I1" 'Payment Processing Fee
    Sheets("Overview").Range("T4") = Target
    Case "I2" 'Cost bandwidth
    Sheets("Overview").Range("T5") = Target


    End Select

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub

    When I change the T4 and T5 to a named range, but leave the Case "I1" and "I2" alone, it works. If I change the Case names, it doesn't work. Has me stumped and very frustrated! Thanks in advance!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Adding named range to Select Case

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED, scroll down and click Manage Attachments.

    PS Can you add code tags when posting code?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    11-17-2015
    Location
    Greater Toronto area, Ontario
    MS-Off Ver
    2013
    Posts
    5

    Re: Adding named range to Select Case

    Thanks for the reply. Simplified version of worksheet is attached to see code. Thanks again!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA using a named range in a 'case' arguement?
    By franklinjim in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-25-2014, 08:58 PM
  2. Adding row to a named range-updating the named range address
    By kjsconv in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-12-2013, 11:22 PM
  3. Select case on range name
    By mcinnes01 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-10-2011, 11:26 AM
  4. Select Case in a Range
    By prairied0gg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-12-2010, 03:30 PM
  5. Select case using a range
    By sigfreid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-31-2009, 09:17 AM
  6. Select Case on a range - problem
    By cdb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2005, 10:06 AM
  7. case select and a range
    By JT in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1