Results 1 to 21 of 21

Need help with VBA code. Tons of code seperated in two, second part of code doesn't work.

Threaded View

  1. #8
    Registered User
    Join Date
    12-30-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Google Sheets
    Posts
    43

    Re: Need help with VBA code. Tons of code seperated in two, second part of code doesn't wo

    Not sure if I'm doing something wrong, but I did the second part of the code to hide columns as a test. If I delete a cell from I4, it hides all columns in that sheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheet_Change_B Target
    Worksheet_Change_V Target
    End Sub
    Private Sub Worksheet_Change_B(ByVal Target As Range)
    Dim ws As Worksheet, blHide As Boolean, Rw As Long
    Const sPW As String = "pass1234"
    Application.ScreenUpdating = False
        '==================== Beauty Expert ====================
    If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Then
        blHide = (Target.Value = ""): Rw = Target.Row
        For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
            With ws
                .Unprotect (sPW)
                .Range(Rw & ":" & Rw & "," & Rw + 41 & ":" & Rw + 41 & "," & _
                  Rw + 82 & ":" & Rw + 82 & "," & Rw + 123 & ":" & Rw + 123).EntireRow.Hidden = blHide
                .Protect (sPW)
                .EnableSelection = xlUnlockedCells
            End With
        Next
        With Sheets("Extra Week")
            .Unprotect (sPW)
            .Range(Rw & ":" & Rw).EntireRow.Hidden = blHide
            .Protect (sPW)
            .EnableSelection = xlUnlockedCells
        End With
        With Sheets("Cosmetician Sales")
            .Unprotect (sPW)
            .Range(Rw & ":" & Rw & "," & Rw + 31 & ":" & Rw + 31).EntireRow.Hidden = blHide
            .Protect (sPW)
            .EnableSelection = xlUnlockedCells
        End With
        With Sheets("CAST")
            .Unprotect (sPW)
            .Range((Rw - 4) * 19 + 1 & ":" & (Rw - 3) * 19).EntireRow.Hidden = blHide
            .Protect (sPW)
            .EnableSelection = xlUnlockedCells
        End With
    End If
    Application.ScreenUpdating = True
    End Sub
    Private Sub Worksheet_Change_V(ByVal Target As Range)
    Dim ws As Worksheet, blHide As Boolean, Cl As Long
    Const sPW As String = "pass1234"
    Application.ScreenUpdating = False
        '==================== Beauty Expert ====================
    If Not Intersect(Target, ActiveSheet.Range("I4:I33")) Is Nothing Then
        blHide = (Target.Value = ""): Cl = Target.Column
        For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
            With ws
                .Unprotect (sPW)
                .Range(Cl & ":" & Cl).EntireColumn.Hidden = blHide
                .Protect (sPW)
                .EnableSelection = xlUnlockedCells
            End With
        Next
        With Sheets("Extra Week")
            .Unprotect (sPW)
            .Range(Cl & ":" & Cl).EntireColumn.Hidden = blHide
            .Protect (sPW)
            .EnableSelection = xlUnlockedCells
        End With
    End If
    Application.ScreenUpdating = True
    End Sub
    Last edited by FragaGeddon; 11-23-2015 at 12:51 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Tool or code to copy VBA code to OneNote or Word while preserving VBA formatting
    By gregersdk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2015, 04:07 PM
  2. [SOLVED] Pattern Building VBA Code - Working code, would like to use cleaner code
    By Benisato in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-10-2015, 03:05 PM
  3. [SOLVED] Excel VB code. Message pops up while code running asking question. Code must not wait.
    By Heinrich Venter in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-28-2014, 06:10 AM
  4. [SOLVED] VBA code for assigning a numeric code to text; then numeric code populates table
    By cteaster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2014, 08:01 PM
  5. Replies: 2
    Last Post: 03-09-2013, 04:30 AM
  6. Replies: 2
    Last Post: 03-17-2011, 08:55 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1