+ Reply to Thread
Results 1 to 13 of 13

changing cell on active sheet does not cause code to continue - No error codes either

Hybrid View

  1. #1
    Registered User
    Join Date
    09-19-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    26

    changing cell on active sheet does not cause code to continue - No error codes either

    Can anyone solve this riddle for me? Why when I change the cell's value in $E$31 on the active sheet does it not continue to work in the "Shop Order" sheet?

    I do not have much experience (yet) with VBA so please be gentle.

    I really appreciate the wealth of resources that are found on this forum.

    Thanks in advance.



    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    If Target.Address = "$E$31" Then
    
    
    With Sheets("Shop Order")
    
    '  The following code works with page 2 of the shop order worksheet specifically with System B.
    
    
          
    'In this first if statement, it is assumed that system B will not be in use at all.
    
         If Range("A73") = "" Then
        
         Range("B71:O75").Borders.LineStyle = 0              'this comment clears out all boxes for system B
        
       
         End If
         
    'the following code creates one thick outline box because this carriage will have only one section.
    
        
            If Range("A73") <> "" And Range("I46") >= 1 Then
            
        Range("E72:N74").Borders.LineStyle = 0              'this comment clears out all boxes for system B after the first carriage box
             
        Range("C72:D74").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        
       
        
            End If

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    attach a sample file for testing
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    09-19-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    Here you go:


    SampleWorkbook.xlsm

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    What's your goal ? you did not explain.

  5. #5
    Registered User
    Join Date
    09-19-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    Actually I did:

    Can anyone solve this riddle for me? Why when I change the cell's value in $E$31 on the active sheet does it not continue to work in the "Shop Order" sheet?
    I need help overcoming why this code is not running in the way I am expecting. I am expecting it to continue (as per code) to Shop Order Sheet in order to draw the appropriate amount of boxes, in this sample case - 1 box.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    Hello SoteriaLive,

    When using the With ... End With construct, all members that belong to the object following With must be prefixed with a period.
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim i As Integer
    
            If Target.Address <> "$E$5" Then Exit Sub
    
    
            With Sheets("Shop Order")
    
            '  The following code works with page 2 of the shop order worksheet specifically with System B.
          
            '  In this first if statement, it is assumed that system B will not be in use at all.
    
                If .Range("A73") = "" Then
        
                    .Range("C71:N74").Borders.LineStyle = 0              'this comment clears out all boxes for system B
        
                End If
         
            '  the following code creates one thick outline box because this carriage will have only one section.
    
        
                If .Range("A73") <> "" And Range("I46") >= 1 Then
            
                    .Range("E72:N74").Borders.LineStyle = 0           '  This line clears out all boxes for system B after the first carriage box
             
                    With .Range("C72:D74")                            '  This line draws in 1 thick border box spanning two columns and three rows.
                
                     '  Remove all diagonal borders
                        .Borders(5).LineStyle = xlNone
                        .Borders(6).LineStyle = xlNone
                        .Borders(11).LineStyle = xlNone
                        .Borders(12).LineStyle = xlNone
                    
                     '  Add borders around each cell
                        For i = 7 To 10
                            With .Borders(i)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlMedium
                            End With
                        Next i
                    
                    End With
            
                End If
            
            End With
            
    End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  7. #7
    Registered User
    Join Date
    09-19-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    ahhh..very good information. Let me try that out. I appreciate the illustration.

    I see the problem now. Since I have about 10 times more code than I provided, I realize it will become difficult to do this.

    How can I instead call the code from sheet 2 (Shop Order) from within active sheet macro?
    Last edited by SoteriaLive; 09-28-2012 at 04:01 PM.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    Hello SoteriaLive ,

    I am not sure what you want to do differently with the macro.

  9. #9
    Registered User
    Join Date
    09-19-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    What I mean, as an alternative, is simply on sheet1 write a basic procedure :

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    If Target.Address = "$E$31" Then
    
    call mymacro                          'and this macro will actually be the code that you see creating the boxes with borders behind sheet2 ("Shop Orders")
    
    
    
    End If
    
    End Sub

    I hope this helps and thank you again.

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    Hello ,

    Add a new VBA Module to your workbook. Copy and paste the macro code below into it.
    Sub AddBorders()
    
            With Sheets("Shop Order")
    
            '  The following code works with page 2 of the shop order worksheet specifically with System B.
          
            '  In this first if statement, it is assumed that system B will not be in use at all.
    
                If .Range("A73") = "" Then
        
                    .Range("C71:N74").Borders.LineStyle = 0              'this comment clears out all boxes for system B
        
                End If
         
            '  the following code creates one thick outline box because this carriage will have only one section.
    
        
                If .Range("A73") <> "" And Range("I46") >= 1 Then
            
                    .Range("E72:N74").Borders.LineStyle = 0           '  This line clears out all boxes for system B after the first carriage box
             
                    With .Range("C72:D74")                            '  This line draws in 1 thick border box spanning two columns and three rows.
                
                     '  Remove all diagonal borders
                        .Borders(5).LineStyle = xlNone
                        .Borders(6).LineStyle = xlNone
                        .Borders(11).LineStyle = xlNone
                        .Borders(12).LineStyle = xlNone
                    
                     '  Add borders around each cell
                        For i = 7 To 10
                            With .Borders(i)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlMedium
                            End With
                        Next i
                    
                    End With
            
                End If
    
    End Sub
    The Worksheet_Change event code will then be like this...
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    If Target.Address = "$E$31" Then
    
        call AddBorders                          'and this macro will actually be the code that you see creating the boxes with borders behind sheet2 ("Shop Orders")
    
    End If
    
    End Sub

  11. #11
    Registered User
    Join Date
    09-19-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    Unfortunately, this does not seem to be doing anything:

    Sub AddBorders()
    
            With Sheets("Shop Order")
    
            '  The following code works with page 2 of the shop order worksheet specifically with System B.
          
            '  In this first if statement, it is assumed that system B will not be in use at all.
    
                If .Range("A73") = "" Then
        
                    .Range("C72:N74").Borders.LineStyle = 0              'this comment clears out all boxes for system B
        
                End If
         
            '  the following code creates one thick outline box because this carriage will have only one section.
    
        
                If .Range("A73") <> "" And Range("I46") = 1 Then
            
                    .Range("E72:N74").Borders.LineStyle = 0           '  This line clears out all boxes for system B after the first carriage box
             
                    With .Range("C72:D74")                            '  This line draws in 1 thick border box spanning two columns and three rows.
                
                     '  Remove all diagonal borders
                        .Borders(5).LineStyle = xlNone
                        .Borders(6).LineStyle = xlNone
                        .Borders(11).LineStyle = xlNone
                        .Borders(12).LineStyle = xlNone
                    
                     '  Add borders around each cell
                        For i = 7 To 10
                            With .Borders(i)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlMedium
                            End With
                        Next i
                    
                    End With
            
                End If
                
            End With
    
    End Sub
    The above is in Module 3 and below is behind sheet1:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    If Target.Address = "$E$31" Then
    
        Call AddBorders                          'and this macro will actually be the code that you see creating the boxes with borders for sheet2 ("Shop Orders")
    
    End If
    
    End Sub

  12. #12
    Registered User
    Join Date
    09-19-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    You know , I think I just worked it out. It did not seem to like the reference to I46 as it is a calc reference from sheet 1 $E$31. when I pointed it to E31 on sheet 1, it started to work. Standby however, I need to extrapolate this code and make sure I can pull it off for up to 6 carriage boxes.


    Sub AddBorders()
    
            With Sheets("Shop Order")
    
            '  The following code works with page 2 of the shop order worksheet specifically with System B.
          
            '  In this first if statement, it is assumed that system B will not be in use at all.
    
                If .Range("A73") = "" Then
        
                    .Range("C72:N74").Borders.LineStyle = 0              'this comment clears out all boxes for system B
        
                End If
         
            '  the following code creates one thick outline box because this carriage will have only one section.
    
        
                If .Range("A73") <> "" And Sheets("Start - User Inputs").Range("$E$31") = 1 Then
            
                    .Range("E72:N74").Borders.LineStyle = 0           '  This line clears out all boxes for system B after the first carriage box
             
                    With .Range("C72:D74")                            '  This line draws in 1 thick border box spanning two columns and three rows.
                
                     '  Remove all diagonal borders
                        .Borders(5).LineStyle = xlNone
                        .Borders(6).LineStyle = xlNone
                        .Borders(11).LineStyle = xlNone
                        .Borders(12).LineStyle = xlNone
                    
                     '  Add borders around each cell
                        For i = 7 To 10
                            With .Borders(i)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlMedium
                            End With
                        Next i
                    
                    End With
            
                End If
                
            End With
    
    End Sub
    Thank you for teaching me VBA code.

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: changing cell on active sheet does not cause code to continue - No error codes either

    Hello SoteriaLive ,

    It is nice to see you solved the problem. If you have any other questions or issues with the code, let me know. I'll be happy to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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