1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

How to force data entry with VBA

Discussion in 'Business Applications' started by DJ_Rutts, Dec 8, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    Hi people,

    I currently use the following code to track job status. Due to the size of the tracker, I keep columns B and C hidden so it's legible when I print. Unfortunately this can mean that columns B and C get overlooked when I new job is set up. Would it be possible to force data entry into cells B and C if there is an entry into A?

    I had a lot of help from this forum with the VBA below. I still consider myself a novice when it comes to this..!

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Cell As Range
    Dim CI As Long
    Dim N As Long
    Dim R As Long
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Status As String
    Dim X As Long

    On Error GoTo ResetEvents
    Application.EnableEvents = False ' This stops the on_change trigger until it's ready
    ' has to be rest at the end of the code (see below)
    If Target.Cells.Count > 1 Then GoTo netd

    Application.ScreenUpdating = False

    If Target.Column = 10 Then
    If Target.Value <> "" Then
    If Target.Value = "9. Completed" Then
    Completed oRow:=Target.Row
    Else
    Cells(Target.Row, "L").Value = Date
    End If
    Else
    Cells(Target.Row, "L").ClearContents
    End If
    End If

    If Target.Column = 10 Then
    If Target.Value <> "" Then
    If Target.Value = "10. Rejected" Then
    Rejected oRow:=Target.Row
    Else
    Cells(Target.Row, "L").Value = Date
    End If
    Else
    Cells(Target.Row, "N").ClearContents
    End If
    End If

    For Each Rng In Range("A2:A" & WorksheetFunction.Max(2, Range("A" & Rows.Count).End(xlUp).Row))
    Status = Cells(Rng.Row, "J")
    GoSub ColorRow
    Range(Cells(Rng.Row, "A"), Cells(Rng.Row, "S")).Interior.ColorIndex = CI

    If Cells(Rng.Row, "J").Value <> vbNullString Then
    Cells(Rng.Row, "O").Value = WorksheetFunction.Days360(Cells(Rng.Row, "L").Value, Date)
    Else
    Cells(Rng.Row, "O").ClearContents
    End If

    Next Rng

    Set Rng = Range("A2:S" & WorksheetFunction.Max(2, Range("A" & Rows.Count).End(xlUp).Row))

    Rng.Sort Key1:=Rng.Cells(1, "J"), Order1:=xlAscending, _
    Key2:=Rng.Cells(1, "F"), Order2:=xlAscending, _
    Key3:=Rng.Cells(1, "L"), Order3:=xlAscending, _
    Header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    netd: ' nothing else to do
    ResetEvents:
    On Error GoTo 0
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub

    ColorRow:
    Select Case Status
    Case "1. CR Review"
    CI = 38
    Case "2. Awt Est"
    CI = 37
    Case "3. Auth Req"
    CI = 38
    Case "4. Pre-Dev"
    CI = 37
    Case "5. Development"
    CI = 37
    Case "6. System Test"
    CI = 6
    Case "7. User Test"
    CI = 43
    Case "8. Awt Release"
    CI = 38
    Case Else
    CI = xlNone
    End Select
    Return

    End Sub
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi DJ, is it possible to post a sample with the vba code (macros) in it and some ficticious data?
    I think I understand the question but a sheet with data tells me more.
    Validation is no issue with VBA and quiet simple to setup even if you use hodden columns or even hidden sheets to track changes and data
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I've downloaded the file and will take a look at it sometime this weekend.
    I don't remember this one, at least not under your ID, but i'll probably recognize the code.
    I'll get back to you if I've got questions
     
  4. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    I just deleted that one as realised it I had deleted the tab with the list names so it won't function correctly. This is the one you will need...

    Thanks for your help!

    Dan
     

    Attached Files:

  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I added two lines of code that will either unhide columns B and C when you enter a new job.
    See and test it to see if it works, another options is to crete a user form where yoiu enter the new job name and the contents that have to be stored in B and C and then just continue with the rest.
     

    Attached Files:

  7. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    Thanks, it seems to work in unhiding the columns, that is very helpful. Is there any way to force data entry as well though?

    If people can take short cuts they often will! So I need to do what I can to make sure we get all the data possible.

    Cheers again,

    Dan
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Well you could do that yourself, that in one of the two cells is still empty the faile will not close.
    Just the same as the notification telling you there is data missing you could include that if they don't fill the required data, that same row will be deleted and the file saved of the files closes without saving and all cganes are lost. Bad luck for the indiscipline user :)
     
  9. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    Hi Hans,

    I am having some issues with this one, as since I enter the new VBA code, the unhide function works, but the other function of sorting/reordering the rows no longer works...?
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    What code did you enter? Did you amplify the sort range? remeber you alwasy need to re evaluate which is the last row to keep the sorting working
     
  11. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    I just copied and pasted the code from the attachment you posted....
     
  12. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    Sorry, What do you mean by 'evalute the last row..?'
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Just that, if you add a row of data the last row moves down one, if the macro does not inlcude that row in the sorting, the sorting no longer checks
    Simple, no?
     
  14. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    The code is set up to sort the data into groups - not necessarily the last row. It groups together jobs with the same status and then sorts them in order of priority and finally date.

    You can see by the attachment, how they have been grouped previously by the code, but it will no longer work and I can't see why?
     

    Attached Files:

  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I don't knwo what you changed but this was wrong:

    Code:
        Set rng = Range("A2:S" & WorksheetFunction.Max(2, Range("A" & Rows.Count).End(xlUp).Row))
        
        rng.Sort Key1:=rng.Cells([COLOR="Red"]2[/COLOR], "J"), Order1:=xlAscending, _
            Key2:=rng.Cells([COLOR="red"]2[/COLOR], "F"), Order2:=xlAscending, _
            Key3:=rng.Cells([COLOR="red"]2[/COLOR], "L"), Order3:=xlAscending, _
            Header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    
    
    The numbers now in RED where 1 instead of 2 and row 1 is not included in your sort, so nothing happens.

    Change it and see, It lookes good to me now.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1030318

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice