How to force data entry with VBA

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

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
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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.
 

Attachments

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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 :)
 

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...?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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?
 

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?
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top