+ Reply to Thread
Results 1 to 10 of 10

Code to exit and not call module if certain ranges are blank

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    647

    Code to exit and not call module if certain ranges are blank

    I have ranges on a sheet
    B10:b19
    B21:B30
    B32:B39
    B41:B48
    B50:B57
    B59:B66

    If those Ranges are totally blank then I want it exit and not call module

    then if one of the ranges has a cell populated then it calls the module.

    Code i Have that calls the module now is

    Private Sub Worksheet_Activate()
    
    Call PopulatesJobTicket.NewDateRange
    End Sub
    what would I add to make it do what I would like.

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Code to exit and not call module if certain ranges are blank

    Private Sub Worksheet_Activate()
    
         Dim TestRange as string
    
         TestRange = "B10:B19, B21:B30, B32:B39, B41:B48, B50:B57, B59:B66, B10:B19" 'Defines the range
              If WorksheetFunction.CountA(TestRange) < 0 Then 'Determines if the range has any cells that are NOT blank
                   Call PopulatesJobTicket.NewDateRange
              End If
    
    End Sub
    Last edited by mikeTRON; 10-07-2014 at 04:46 PM.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    647

    Re: Code to exit and not call module if certain ranges are blank

    Sorry, one more addition could it clear contents out of the range on a tab "Job Ticket" the range is A10:N67 first then exit


    I am making changes as it works so I can see what need to be done next.

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Code to exit and not call module if certain ranges are blank

    Private Sub Worksheet_Activate()
    
         Dim TestRange as string
    	 Dim ClearRange as string
    
         TestRange = "B10:B19, B21:B30, B32:B39, B41:B48, B50:B57, B59:B66, B10:B19" 'Defines the range
    	ClearRange = "A10:N67"
    	
              If WorksheetFunction.CountA(TestRange) < 0 Then 'Determines if the range has any cells that are NOT blank
                   Call PopulatesJobTicket.NewDateRange
              End If
    		  
    	Sheets("Job Ticket").Range(ClearRange).Clear
    	
    End Sub

  5. #5
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    647

    Re: Code to exit and not call module if certain ranges are blank

    I just noticed that I was not clear on my explanation.

    The Range that it is checking is on another sheet Named (Daily Chgs) that is where the range is located that it is checking for blanks and the range to clear contents are on the sheet Named "Job Ticket" Range A10:N67

  6. #6
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Code to exit and not call module if certain ranges are blank

    You are correct. You are unclear. LOL

    You have lost me.

  7. #7
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    647

    Re: Code to exit and not call module if certain ranges are blank

    Let me start over... I tried to convert your code ..Not Successful

    Private Sub Worksheet_Activate()
    
         Dim TestRange As String
         Dim ClearRange As String
    
         TestRange = Sheets ("Daily Chgs").range."B10:B19, B21:B30, B32:B39, B41:B48, B50:B57, B59:B66, B10:B19" 'Defines the range
         ClearRange = "A10:N67"
        
              If WorksheetFunction.CountA(TestRange) < 0 Then 'Determines if the range has any cells that are NOT blank
    
            ActiveSheet.Unprotect Password:="Financial3"
                Sheets("Job Ticket").Range(ClearRange).Clear
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Financial3"
    
              End If     
    
       Call PopulatesJobTicket.NewDateRange
        
    End Sub
    The range to check for the blanks is on the sheet "Daily Chgs"
    The sheet that I want the Contents cleared is "Job Ticket" Range A10:N67
    Sheet PW is Financial3



    when they activate the Sheet "Job Ticket" I want it to check and see if the ranges on the sheet "Daily Chgs" are Blank
    "B10:B19, B21:B30, B32:B39, B41:B48, B50:B57, B59:B66, B10:B19"

    If they are blank then I want it to clear the contents on the "Job Ticket" Range (A10:N67) and then exit sub

    If the checked range on the "Daily Chgs" tab is found not be blank then I want it to call Module "PopulatesJobTicket.NewDateRange"

    I hope this is better explanation

  8. #8
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Code to exit and not call module if certain ranges are blank

    I would not use ActiveSheet, I would call it out explicitly as noted in the code below.

    I also added a ton of comments so you can understand what is going on.

    Option Explicit
    
    Private Sub Worksheet_Activate()
    
        '======================================================================
        'Declare variables
        '======================================================================
            Dim TestRange As String 'Used to define the range to check if blank
            Dim ClearRange As String 'Used to define the range that will be cleared
            Dim ClearTab As String 'used to define the name of the tab that needs the range to be cleared
            Dim TestValue As Long 'used to check if testrange is empty - if it is zero, then it is empty
            Dim PW As String 'used to define the password
            Dim WrkSht As String 'used to define the sheet to unprotect
        
        '======================================================================
        'Define Variables
        '======================================================================
            WrkSht = ActiveSheet.Name 'this defines the active sheet, but I would call it out specifically like the code commented out below
            'WrkSht = "WhateverSheet" <- example
            TestRange = "B10:B19, B21:B30, B32:B39, B41:B48, B50:B57, B59:B66, B10:B19" 'Defines the range
            ClearRange = "A10:N67"
            ClearTab = "Job Ticket"
            TestValue = WorksheetFunction.CountA(Worksheets("Daily Chgs").Range(TestRange)) 'COUNTA checks for any NONblank cells
            PW = "Financial3"
        '======================================================================
        'Test Logic
        '======================================================================
            If TestValue < 0 Then 'If any cells are NOT blank then
                Worksheets(WrkSht).Unprotect Password:=PW
                    Sheets(ClearTab).Range(ClearRange).Clear
                        Worksheets(WrkSht).Protect DrawingObjects:=True, _
                            Contents:=True, Scenarios:=True, Password:=PW
                            'Exit Sub 'Do you want the sub to exit after this or go on to call the next procedure??
            End If
        '======================================================================
        ' Call procedure
        '======================================================================
            Call PopulatesJobTicket.NewDateRange
            
        '======================================================================
        'End Macro procedures
        '======================================================================
    End Sub

    You can make this code MUCH smaller, or less text, but when new I prefer to have more visibility into what exactly is going on.

    If you have any questions, let me know.

    Also check out this website so you can learn how to properly step through code and determine how to correct things.
    http://www.cpearson.com/excel/DebuggingVBA.aspx
    Last edited by mikeTRON; 10-07-2014 at 06:18 PM.

  9. #9
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    647

    Re: Code to exit and not call module if certain ranges are blank

    I will try it out when home thanks for the help

  10. #10
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    647

    Re: Code to exit and not call module if certain ranges are blank

    I seems to be working good thank you...I will put it to the test tomorrow when i put the entire sheet through a trial run.

    Again thanks

+ 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. [SOLVED] How can I call a Module or a sub procedure and run it automatically in current module?
    By qzqzjcjp in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2014, 11:48 AM
  2. [SOLVED] VBA code: A Sub() module to CALL another Sub ()
    By hydz1213 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-07-2013, 08:48 AM
  3. Call combobox_change function on exit.
    By hubert_ii in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-23-2009, 09:05 AM
  4. how to access Sheet module, normal module, Worbook module to type code
    By alibaba in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2009, 07:51 AM
  5. Call Code in One Module Into Another
    By SEOT in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-20-2008, 11:09 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