+ Reply to Thread
Results 1 to 2 of 2

Can I use a named cell in a CASE statement

  1. #1
    Registered User
    Join Date
    11-24-2008
    Location
    ohio
    Posts
    19

    Question Can I use a named cell in a CASE statement

    I have a Worksheet Change VBA code added to my Worksheet in excel. Basically I want to change the scaling in a chart within the worksheet when I change a cell value. This code works
    Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet.ChartObjects("Chart 1").Chart
    Select Case Target.Address
    Case "$B$5"
    .Axes(xlCategory).MaximumScale = ActiveSheet.Range("XMax").Value
    Case "$B$6"
    .Axes(xlCategory).MinimumScale = ActiveSheet.Range("XMin").Value
    Case "$C$5"
    .Axes(xlValue).MaximumScale = ActiveSheet.Range("YMax").Value
    Case "$C$6"
    .Axes(xlValue).MinimumScale = ActiveSheet.Range("YMin").Value
    End Select
    End With
    End Sub

    But I would like to use the cell names in the CASE statement rather than the $B$5 so if someone adds rows or columns to the worksheet the VBA will still run properly. As you can see in the code $B$5 is named XMax in the worksheet and $B$6 is named XMin and so on. But if I use Case "XMax" or Case Range("XMax") the VBA doesn't work.

    I'm hoping someone can tell me what I'm doing wrong. Any help would be greatly appreciated.

  2. #2
    Forum Contributor
    Join Date
    06-11-2014
    MS-Off Ver
    Office 2003, 2007 & 2010
    Posts
    119

    Re: Can I use a named cell in a CASE statement

    Use

    Please Login or Register  to view this content.

+ 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. Case statement to set variable cell range
    By mwgiehm in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-19-2013, 01:09 PM
  2. [SOLVED] Excel 2003 - Creating Named ranges for a validation list box using a case statement
    By DebbieEdwards in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-17-2013, 08:43 AM
  3. Case Statement on Changed Cell's Value
    By meissen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2013, 12:07 PM
  4. Color code cell with case statement and datestamp cell(s) to the left
    By garricko in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-26-2010, 08:37 PM
  5. [SOLVED] Comparing Cell Contents using a Case statement
    By todd in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2005, 04:35 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