+ Reply to Thread
Results 1 to 9 of 9

Multiple "ByVal Target As Range"

Hybrid View

  1. 11-08-2013, 12:36 PM #1
    batchy
    batchy is offline
    Forum Contributor
    Join Date
    09-09-2008
    Location
    Middlesbrough, England
    MS-Off Ver
    2007
    Posts
    174

    Multiple "ByVal Target As Range"

    Hi,

    I have a worksheet which has 2 fields (cells). When these values change i would like my VB code to hide a range of cells.

    The 1st code works fine:

    Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Address = "$E$15" Then
        If Target.Value = "All Sites" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(26), Rows(40)).EntireRow.Hidden = False
        End If
        If Target.Value = "Inhouse" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(26), Rows(31)).EntireRow.Hidden = False
        End If
        If Target.Value = "Outsource" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(32), Rows(40)).EntireRow.Hidden = False
        End If
    End If
         
End Sub
    However when i add in the 2nd range it fails and not sure why?

    Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Address = "$E$15" Then
        If Target.Value = "All Sites" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(26), Rows(40)).EntireRow.Hidden = False
        End If
        If Target.Value = "Inhouse" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(26), Rows(31)).EntireRow.Hidden = False
        End If
        If Target.Value = "Outsource" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(32), Rows(40)).EntireRow.Hidden = False
        End If
    End If
         
End Sub
Private Sub WorksheetChange2(ByVal Target As Range)
    
    If Target.Address = "$E$13" Then
        If Target.Value = "Consumer" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(45), Rows(58)).EntireRow.Hidden = False
        End If
        If Target.Value = "Campaigns" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(62), Rows(73)).EntireRow.Hidden = False
        End If
        If Target.Value = "B2B" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(76), Rows(99)).EntireRow.Hidden = False
        End If
    End If

     
End Sub
    Reply With QuoteRegister To Reply
  2. 11-08-2013, 01:43 PM #2
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Multiple "ByVal Target As Range"

    From what I know WorksheetChange2 won't work....the system work recongize the "2"....but you can put the code from "2" into first one and it should work because you are looking at different cells....I think that should work....HTH
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....
    Reply With QuoteRegister To Reply
  3. 11-08-2013, 02:06 PM #3
    batchy
    batchy is offline
    Forum Contributor
    Join Date
    09-09-2008
    Location
    Middlesbrough, England
    MS-Off Ver
    2007
    Posts
    174

    Re: Multiple "ByVal Target As Range" 

    Private Sub WorksheetChange(ByVal Target As Range)
     
    If Target.Address = "$E$15" Then
        If Target.Value = "All Sites" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(26), Rows(40)).EntireRow.Hidden = False
        End If
        If Target.Value = "Inhouse" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(26), Rows(31)).EntireRow.Hidden = False
        End If
        If Target.Value = "Outsource" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(32), Rows(40)).EntireRow.Hidden = False
        End If
            If Target.Address = "$E$13" Then
        If Target.Value = "Consumer" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(45), Rows(58)).EntireRow.Hidden = False
        End If
        If Target.Value = "Campaigns" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(62), Rows(73)).EntireRow.Hidden = False
        End If
        If Target.Value = "B2B" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(76), Rows(99)).EntireRow.Hidden = False
    End If
         
End Sub
    i have tried putting the code in the same SUB but still not working
    Reply With QuoteRegister To Reply
  4. 11-08-2013, 02:04 PM #4
    batchy
    batchy is offline
    Forum Contributor
    Join Date
    09-09-2008
    Location
    Middlesbrough, England
    MS-Off Ver
    2007
    Posts
    174

    Re: Multiple "ByVal Target As Range"

    I have tried changing the name but still not working :s
    Reply With QuoteRegister To Reply
  5. 11-08-2013, 02:09 PM #5
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Multiple "ByVal Target As Range"

    try this

    Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Address = "$E$15" Then
        If Target.Value = "All Sites" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(26), Rows(40)).EntireRow.Hidden = False
        End If
        If Target.Value = "Inhouse" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(26), Rows(31)).EntireRow.Hidden = False
        End If
        If Target.Value = "Outsource" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(32), Rows(40)).EntireRow.Hidden = False
        End If
    End If
    
    If Target.Address = "$E$13" Then
        If Target.Value = "Consumer" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(45), Rows(58)).EntireRow.Hidden = False
        End If
        If Target.Value = "Campaigns" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(62), Rows(73)).EntireRow.Hidden = False
        End If
        If Target.Value = "B2B" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(76), Rows(99)).EntireRow.Hidden = False
        End If
         
End Sub
    Reply With QuoteRegister To Reply
  6. 11-08-2013, 02:38 PM #6
    batchy
    batchy is offline
    Forum Contributor
    Join Date
    09-09-2008
    Location
    Middlesbrough, England
    MS-Off Ver
    2007
    Posts
    174

    Re: Multiple "ByVal Target As Range"

    I received an error

    Compile error:

    Block If without End If
    Reply With QuoteRegister To Reply
  7. 11-08-2013, 02:39 PM #7
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Multiple "ByVal Target As Range" 

    Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Address = "$E$15" Then
        If Target.Value = "All Sites" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(26), Rows(40)).EntireRow.Hidden = False
        End If
        If Target.Value = "Inhouse" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(26), Rows(31)).EntireRow.Hidden = False
        End If
        If Target.Value = "Outsource" Then
            Range(Rows(26), Rows(40)).EntireRow.Hidden = True
            Range(Rows(32), Rows(40)).EntireRow.Hidden = False
        End If
    End If
    
    If Target.Address = "$E$13" Then
        If Target.Value = "Consumer" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(45), Rows(58)).EntireRow.Hidden = False
        End If
        If Target.Value = "Campaigns" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(62), Rows(73)).EntireRow.Hidden = False
        End If
        If Target.Value = "B2B" Then
            Range(Rows(45), Rows(99)).EntireRow.Hidden = True
            Range(Rows(76), Rows(99)).EntireRow.Hidden = False
        End If
    End If

     
End Sub
    Reply With QuoteRegister To Reply
  8. 11-08-2013, 02:48 PM #8
    batchy
    batchy is offline
    Forum Contributor
    Join Date
    09-09-2008
    Location
    Middlesbrough, England
    MS-Off Ver
    2007
    Posts
    174

    Re: Multiple "ByVal Target As Range"

    Excellent many thanks what was the difference?
    Reply With QuoteRegister To Reply
  9. 11-08-2013, 02:50 PM #9
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Multiple "ByVal Target As Range"

    if the first attempt when you combined the two subs you put one IF statement inside the other....when I copied and seperated the two, I left out the last End IF...glad I could help....and thanks for the +rep
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Newbie How to delete macro "Private Sub Worksheet_SelectionChange(ByVal Target As Range)<"
    By semiplenus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2013, 01:38 AM
  2. Need help creating VBA looping code in "Worksheet_Change(ByVal Target As Range)"
    By jimredfield in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-11-2012, 03:09 PM
  3. [SOLVED] Worksheet_Change(ByVal Target As Range) - set to only update when 3 target cells changed?
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 06:40 PM
  4. Worksheet_CHange (ByVal Target as Range) when target is formula cell
    By coasterman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2012, 07:00 PM
  5. 2 target cells for "Change(ByVal..." worksheet macro
    By timmtamm in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-12-2009, 05:12 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1